ADOX and Access linked tables

ADOX and Access linked tables

Post by Debbie Briff » Thu, 15 Jun 2000 04:00:00



HELP!!

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
works...

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") =
"C:\DatabasePath\DatabaseName.mdb"
        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
database.

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:

DRIVER=SQL Server;SERVER=BRITAIN;APP=Microsoft?
Access;WSID=CAIRO;DATABASE=EXAMPLE

I assumed that what I needed to do was set this property to the relevent
SERVER or DATABASE, i.e.

    For Each tbl In cat.Tables
        If tbl.Type = "PASS-THROUGH" Then
            tbl.Properties("Jet OLEDB:Link Provider String") = "DRIVER=SQL
Server;SERVER=FRANCE;APP=Microsoft? Access;WSID=CAIRO;DATABASE=EXAMPLE2"
        End If
    Next tbl

This causes the following error on the tbl.properties.... 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
code:

            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
Server;SERVER=FRANCE;APP=Microsoft? Access;WSID=CAIRO;DATABASE=EXAMPLE2"
            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.

HELP PLEASE, IT'S DRIVING ME MAD!!