Refresh Linked Tables - ADO

[Scott]

I'm trying to "refresh" or "change" the database path of all linked tables
in my access file that are linked to "myFileA.mdb" so the tables become
linked to "myFileB.mdb". This code came from the Office 2000 Developer
CD-ROM and can be viewed the MSDN page
http://msdn.microsoft.com/library/d...n-us/odeopg/html/deovrcreatinglinkedtable.asp.

When I try to run the sub in the Immediate Window as shown below in the
IMMEDIATE WINDOW listing, I get an error saying

Compile Error: User-defined type not defined

What am I doing wrong? Can someone test this code and explain the error?



*** IMMEDIATE WINDOW **********

call RefreshLinks ("C:\data\databases\myFileA.mdb",
"c:\temp\myFileB.mdb")



*** CODE ***************

Sub RefreshLinks(strDBLinkFrom As String, _
strDBLinkSource As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
' Open a Catalog object on the database in which to refresh links.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBLinkFrom

For Each tblLink In catDB.Tables
' Check to make sure table is a linked table.
If tblLink.Type = "LINK" Then
tblLink.Properties("Jet OLEDB:Link Datasource") =
strDBLinkSource
End If
Next

Set catDB = Nothing
End Sub

 

[Douglas J. Steele]

Did you add a reference to ADOX to your application?

If not, see whether changing

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog

to

Dim catDB As Object
Dim tblLink As Object

Set catDB = CreateObject("ADOX.Catalog")

fixes the problem.

 

[Scott]

Which ADOX version should I add? I tried several, but got the same error.

 

[Scott]

After making the code change you suggested and using "Microsoft ActiveX Data
Objects 2.6 Library", the errors go away, but the link tables don't change.

Any other ideas? Should I use a different ADOX version as a reference?

 

[Douglas J. Steele]

If you look at the code you cited, you'll see not only does it set the Jet
OLEDB: Link Datasource property, but it also sets the Jet OLEDB: Create Link
property.

BTW, DAO is far better than ADOX for stuff like this. Take a look at
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web". Also, my
code was intended to save you having to set the reference. If you've set the
reference, you may as well go back to your original code (with the
additional line "tblLink.Properties("Jet OLEDB:Create Link") = True" added
back in.)

 

[Scott]

I thought ADO was replacing DAO. Can you get my code to switch the linked
paths on your pc? It's almost impossible to troublshoot why my ADO isn't
working when it doesn't give an error.

I looked at the mdb from the Office 2000 Dev. cd that had this original
code in it and their references were:

ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Jet and Replication Objects 2.6 Library

I set mine to the same references to no avail.

 

[Douglas J. Steele]

Popular misconception. Not only didn't ADO replace DAO, but ADO has been
replaced by ADO.Net which, despite the name, has very little in common with
ADO. DAO was developed specifically to work with Jet databases. ADO is a
generic approach, which means that it has to go through additional layers of
abstraction. If all you're doing is working with MDB files, DAO is still the
way to go. In Access 2003, the DAO reference is back by default.

Have you single-stepped through your code? Is it actually making the changes
you expect it to?

 

[Scott]

If I run it, i check the linked table manager and the paths to the linked
files remain the same. access doesn't give an error.

Is it possible that the linked table manager just isn't refreshing?

Below is my current code and references.


MY REFERENCES *******

Visual Basic for Applications
Microsoft Access 11.0 Library
OLE Automation
ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Jet and Replication Objects 2.6 Library

CODE *********

Sub RefreshLinks(strDBLinkFrom As String, _
strDBLinkSource As String)

Dim catDB As Object
Dim tblLink As Object

Set catDB = CreateObject("ADOX.Catalog")

' Open a Catalog object on the database in which to refresh links.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBLinkFrom

For Each tblLink In catDB.Tables
' Check to make sure table is a linked table.
If tblLink.Type = "LINK" Then
tblLink.Properties("Jet OLEDB:Link Datasource") =
strDBLinkSource
End If
Next

Set catDB = Nothing
End Sub

 

[Douglas J. Steele]

You seem to have overlooked my earlier comment that

For Each tblLink In catDB.Tables
If tblLink.Type = "LINK" Then
tblLink.Properties("Jet OLEDB:Link Datasource") = _
strDBLinkSource
tblLink.Properties("Jet OLEDB:Create Link") = True
End If
Next

 

[Scott]

I tried stepping through code and found that the "If tblLink.Type = "LINK""
test isn't identifying the linked tables.

Is there another way to test if a table is indeed a linked table with ADO?

 

[Douglas J. Steele]

Definitely not with ADO, but possibly with ADOX. <g>

Yes, I've found the Type property to be unreliable in ADOX. That's yet
another reason to use DAO!