Create Link with text file Jet OLEDB:Create Link

Create Link with text file Jet OLEDB:Create Link

Post by T Szido » Fri, 29 Mar 2002 07:01:44



I have found sample code (see below) to refresh a link to
an access to table, but I can NOT find the correct syntax
to point to a text file.

Where can I find info on how to use Jet OLEDB:Create Link
with a text file?

Sub ADORefreshLinks()

   Dim cat As New ADOX.Catalog
   Dim tbl As ADOX.Table

   ' Open the catalog

cat.ActiveConnection  "Provider=Microsoft.Jet.OLEDB.4.0;"
& _
      "Data Source=.\NorthWind.mdb;"

   For Each tbl In cat.Tables
      ' Check to make sure table is a linked table.
      If tbl.Type = "LINK" Then
         tbl.Properties("Jet OLEDB:Create Link") = False
         tbl.Properties("Jet OLEDB:Link Provider String")
= _
            ";pwd=NewPassWord"
         tbl.Properties("Jet OLEDB:Link Datasource") = _
            ".\NewPubs.mdb"
         tbl.Properties("Jet OLEDB:Create Link") = True
      End If
   Next

End Sub

 
 
 

Create Link with text file Jet OLEDB:Create Link

Post by Steven Bras [M » Fri, 29 Mar 2002 08:28:38


Here's a sample of how to add a link to a text file in your Access
database, using ADOX (ADO Extensions for DDL and Security). You will need
to add a reference in your VB project to that library, which appears as
"ADO Ext for DDL and Security 2.x" in your references list):

'-- Begin sample code
  Dim adoCn As ADODB.Connection
  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table

  Set adoCn = New ADODB.Connection
  adoCn.Open "provider=microsoft.jet.oledb.4.0;user id=admin;data
source=c:\northwind.mdb"

  Set adoTbl = New ADOX.Table

  'Create Link...
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = adoCn

  Set adoTbl.ParentCatalog = adoCat
  adoTbl.Name = "LinkTable"

  adoTbl.Properties("Jet OLEDB:Link Datasource") = "c:\"
  adoTbl.Properties("Jet OLEDB:Link Provider String") = "Text;Extended
Properties=""text;HDR=Yes;FMT=delimited;"";"
  adoTbl.Properties("Jet OLEDB:Remote Table Name") = "Customers.txt"
  adoTbl.Properties("Jet OLEDB:Create Link") = True

  'Append the table to the tables collection
  adoCat.Tables.Append adoTbl

  MsgBox "Link Created"

  adoCn.Close
  Set adoCn = Nothing
'-- End sample code

This code assumes a comma-delimited text file in the root of the c: drive.
Be aware that using delimiters other than a comma in your text file will
require the presence of a schema.ini file, to explain the structure of the
file to ADOX. See the following Knowledge Base article for more details on
the schema.ini file:

Q149090 ACC: How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q149090

Hope this helps!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

1. OLEDB provider Jet error 80004005 when creating a linked server in SQL Server 7

We are running SQL Server 7 on a Win 2000 domain controller.  When I
create a linked server to an Access 97 database, I get error 80004005
the first time I try to access it.  It happens from Enterprise Manager
as well ... but the error message says "Error 7399: OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."

I am auditing the directory in which the Access DB resides, and nobody
is being denied access.

Ideas anybody?

Thanks!

--
Daniel Wilson, BSCS, MCP
Application Developer
http://www.compusoftsolutions.com/

2. retrieving documents stored in BLOB fields

3. for xml in a variable

4. Create a Linked table to external text file in Access

5. 3rd party tuning tools?

6. Could not create Linked Server to Informix with Informix OLEDB provider

7. Seek sometimes doesn't match record

8. Couldn't create a linked server to INFORMIX using Informix OleDb provider

9. No OLEDB providers when creating a new linked server

10. Linked Server Microsoft.Jet.OLEDB.4.0 to Access MDB with Workgroup/Security

11. Jet OLEDB:Link Provider String to secured db

12. Jet OLEDB:Link Provider String