ADOX and Access Linked Tables

ADOX and Access Linked Tables

Post by Scot Ro » Sun, 24 Nov 2002 00:41:10

Try this out and see if it helps... It actually is for linking a couple of Acces databases but the connection string is there for a SQL SErver DB as well..

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    cat.ActiveConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb; "

   tbl.ParentCatalog = cat
   tbl.Name = "MyTable"
   tbl.Properties("Temporary Table") = False

   'to link a SQL Server (PUBS) Database use this line
   'tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;driver=SQL Server;server=darkover;uid=sa;pwd=;database=pubs;"

   'To link to another access mdb use this line
   tbl.Properties("Jet OLEDB:Link DataSource") = "E:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
   tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Table Hidden In Access") = True
   tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
   cat.Tables.Append tbl

Want to know more? Check out the MSDN Library at  or the Microsoft Knowledge Base at

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

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


1. ADOX and Access linked tables


I have an Access 97 database that is either attached to another Access 97
database or a SQL Server 6.5 database via ODBC.

I have a VB6 procedure that attempts to refresh the linked tables after
connection to the database, as the database that the tables are linked to
may have moved.

If my database is linked to another Access 97 database, the following code

Public Sub RefreshLinks()

    Dim cat As New adox.Catalog
    Dim tbl As Table

    Set cat.ActiveConnection = mcnnConn

    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            tbl.Properties("Jet OLEDB:Link Datasource") =
        End If
    Next tbl

    Set tbl = Nothing
    Set cat = Nothing

End Sub

My problem is trying to refresh tables that are linked to a SQL Server

I have noticed that the "Jet OLEDB:Link Datasource" property, used above, is
not relevent for SQL Server databases as it is blank. However the "Jet
OLEDB:Link Provider String" property of the tables is set to the following:


I assumed that what I needed to do was set this property to the relevent

    For Each tbl In cat.Tables
        If tbl.Type = "PASS-THROUGH" Then
            tbl.Properties("Jet OLEDB:Link Provider String") = "DRIVER=SQL
        End If
    Next tbl

This causes the following error on the line:
ODBC--connection to 'SQL ServerBRITAIN' failed. [ref. FFFF8000:16389 in
Microsoft JET Database Engine].
(I have tested the ODBC connection and I know that it works).

I tried instead to delete the table and add a new one using the following

            cat.Tables.Delete "oldTable"
            Set tbl = New Table
            tbl.Name = "newTable"
            Set tbl.ParentCatalog = cat
            tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo.TableName"
            tbl.Properties("Jet OLEDB:Link Provider String") = "DRIVER=SQL
            cat.Tables.Append tbl

This appears to work but what it actually does is create a table of type
TABLE and not PASS-THROUGH and there are no columns. I know that you can add
columns by using .Columns.Append but I don't want to do this, I want it to
be linked to the SQL Server table thus inheriting the columns from that.


2. Visio Database Modeling - IDENTITY

3. ADOX and Access Linked Tables

4. Warehouse data file layout

5. Re-linking Access tables with ADOX

6. Execute DTS package from stored procedure

7. ADOX Indexes on Access Linked Tables...

8. using one dts file on multiple computers

9. ADOX : Linking Access and SQL Server leaves Access read only

10. ADOX link to Access.MDE problem!!

11. Using ADOX to create linked table

12. ADOX.Append Creates a Read-Only Linked Table to SQL Server 7.0

13. Linking a Foxpro table with Adox