Dynamically relinking tables in Access 2002 1

[Orion45]

One of the databases I support uses linked tables so, to keep links up to date code was added to automatically relinks the tables to the SQL Server back-end through an ODBC connection. This code was developed and working great in Access 2000 but since we have converted to 2002 I just get an invalid table reference error. I couldn't find any articles in Microsoft's Knowledge base that would indicate that syntax had changed. Has anyone else run into this problem when upgrading to Access '02? Any ideas on another way this can be done? The code for this is as follows.

 Dim CNN As ADODB.Connection
 Dim CAT As ADOX.Catalog
 Dim TBL As ADOX.Table
 CNN = CurrentProject.Connection
    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = CNN
    
    For Each TBL In CAT.Tables
        If TBL.Type = "PASS-THROUGH" Then
            TBL.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=ClientData;Database=ClientData;"
        End If
    Next TBL

Thanks in advance for your help!

 

[FancyPrairie]

Don't have time to investigate this, but...

Why not try just linking one of the tables via File|Get External Data. Then open the hidden system file MSysObjects to see what the connection string looks like. It might be a start.

 

[Orion45]

Thanks for the hint. This is definitely the root of my problem. Access 2000 stores the link as:
DSN=Bar Examiners;Database=Bar Examiners;

Access 2002 stores it as:
DSN=Bar Examiners;Description=PABLE Database;APP=Microsoft Office XP;WSID=PABLETEST;DATABASE=Bar Examiners;Network=DBMSSOCN;Trusted_Connection=Yes
I'll have to investigate this further. Thanks again.

 

[Orion45]

Took a week but I finally figured it out. Not only has the syntax of a linked table changed in Access XP but also the way linked tables are deleted. It was discovered that when a linked table is deleted the reference remains in the MSysObjects table but the name is changed to a system generated number with the prefix "~TMP". Although it is still unknown how to remove these "ghost" links I was able to modify my code to ignore them. My code is as follows;

Set CNN = CurrentProject.Connection
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CNN
For Each TBL In CAT.Tables
  If TBL.Name Like "~*" Then
     'skip
  Else
     If TBL.Type = "PASS-THROUGH" Then
     TBL.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=Database2;Description=PABLE Database;APP=Microsoft Office XP;WSID=PABLETEST;DATABASE=Database2;Network=DBMSSOCN;Trusted_Connection=Yes;"
      End If
  End If
Next TBL