J
James Brown
Hi,
For a long while we've used a piece of code to relink Access linked tables
to a different path in a number of our systems (like a neater and more
friendly linked table manager). This code was written in DAO, ran from a
macro on startup, and has worked pretty consistently in all systems until
now. It seems now that with newer machines (esp. those using Access 2K in
Office XP) the code runs too fast for its own good and Access falls over
itself, returning an error. Since we launch most of our Access applications
from a VB launcher (that gets the backend path, workgroup path, Access path
etc) I decided it was time to move this code to ADOX in VB, because VB
usually seems to do things in the right order!
Unfortunately, this seems to suffer from exactly the same problem. The code
I am using is below, and is run just before the Shell command is used to
launch Access with the correct mde and workgroup:
Set catAdmin.ActiveConnection = cnAdmin ' Connection to the frontend
database
Set objTablesRelink = catAdmin.Tables ' Set the tables to a
ADOX tables object
' Loop through every table in the tables object
For Each objTableRelink In objTablesRelink
' Where objTableRelink is a ADOX table object
If objTableRelink.Properties("Jet OLEDB:Remote Table Name").Value <>
"" Then
' The table is a remote table, so check the data source, and
change it if necessary
If objTableRelink.Properties("Jet OLEDB:Link Datasource").Value
<> strBackendPath Then
objTableRelink.Properties("Jet OLEDB:Link Datasource").Value
= strBackendPath
End If
End If
Next
Set catAdmin = Nothing
Set objTablesRelink = Nothing
Set objTableRelink = Nothing
On a fast machine, sometimes the error "Object [tablename] could not be
found" is returned on a random table (could be any table in the 30 or so
that I'm trying to relink). Running the process again usually works without
error.
Has anyone else come across this problem, or know a fix for it? I'm
thinking of putting a little delay in between each table in the loop, but
that would seem a bit of a messy solution.
Thanks for your time,
Jamie.
For a long while we've used a piece of code to relink Access linked tables
to a different path in a number of our systems (like a neater and more
friendly linked table manager). This code was written in DAO, ran from a
macro on startup, and has worked pretty consistently in all systems until
now. It seems now that with newer machines (esp. those using Access 2K in
Office XP) the code runs too fast for its own good and Access falls over
itself, returning an error. Since we launch most of our Access applications
from a VB launcher (that gets the backend path, workgroup path, Access path
etc) I decided it was time to move this code to ADOX in VB, because VB
usually seems to do things in the right order!
Unfortunately, this seems to suffer from exactly the same problem. The code
I am using is below, and is run just before the Shell command is used to
launch Access with the correct mde and workgroup:
Set catAdmin.ActiveConnection = cnAdmin ' Connection to the frontend
database
Set objTablesRelink = catAdmin.Tables ' Set the tables to a
ADOX tables object
' Loop through every table in the tables object
For Each objTableRelink In objTablesRelink
' Where objTableRelink is a ADOX table object
If objTableRelink.Properties("Jet OLEDB:Remote Table Name").Value <>
"" Then
' The table is a remote table, so check the data source, and
change it if necessary
If objTableRelink.Properties("Jet OLEDB:Link Datasource").Value
<> strBackendPath Then
objTableRelink.Properties("Jet OLEDB:Link Datasource").Value
= strBackendPath
End If
End If
Next
Set catAdmin = Nothing
Set objTablesRelink = Nothing
Set objTableRelink = Nothing
On a fast machine, sometimes the error "Object [tablename] could not be
found" is returned on a random table (could be any table in the 30 or so
that I'm trying to relink). Running the process again usually works without
error.
Has anyone else come across this problem, or know a fix for it? I'm
thinking of putting a little delay in between each table in the loop, but
that would seem a bit of a messy solution.
Thanks for your time,
Jamie.