MS Access 2016 - Dynamic update of linked table paths after location change of a database file

On the network drive I have an Access file with linked tables to another Access file within the same folder.  Due to a very slow processing over the network drive I do my test query runs on my local drive then put back the copy of this file on the network drive again. Target and source files are residing both on network and on local drive within same folder.

Each time I copy back and forth between two locations using the same file I have to manually change table links and needless to say this is very annoying and not efficient way of working so I need to get this process done automatically.  

To resolve this problem I attempted the following steps,

1. I ran the below query to get the table link path for each table on the file for the both locations,

 SELECT DISTINCTROW msysobjects.Name, msysobjects.Database, msysobjects.Connect
FROM msysobjects
WHERE (((msysobjects.Type)=6 Or (msysobjects.Type) Like "dsn*"))
ORDER BY msysobjects.Database;

2. Created a tblLinkedTables with field names TableName and DataFilePath from the above query results.

3. Placed the below function in a standard module

Public Function RelinkByList() As Integer
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim tdf As DAO.TableDef
   
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblLinkedTables", dbOpenSnapshot)
   
  ' Loop through the recordset, processing rows
  Do Until rst.EOF
    Set tdf = dbs.TableDefs(rst!TableName)
    tdf.Connect = rst!DataFilePath
    tdf.RefreshLink
    rst.MoveNext
  Loop
 
  Set tdf = Nothing
  Set rst = Nothing
  Set dbs = Nothing
End Function

4. Ran the RelinkbyList function via RunCode macro
 
When I run the above code via RunCode macro I get an error on " tdf.RefreshLink" part of the code, this line of the code gets highlighted in yellow.

I created the mysysobjects query and the function RelinkbyList code in both databases.  Again they are identical database files and I had to play with the table names back and forth as well.

So, I failed to achieve relinking my tables after copying the database file from one location to the other.  If anyone could help me to resolve the issue in my process as described above or offer a completely different approach it would be greatly appreciated.

Thank you for help in advance
Ray ErdenBusiness Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
See if this sample helps.
RelinkMultipleBEUsingForm160722.zip
0
Gustav BrockCIOCommented:
Your connect string is wrong. It is not the path only. It should look like:

;DATABASE=d:\folder\yourdatabase.accdb

Open in new window

Thus, it could be:

tdf.Connect = ";DATABASE=" & rst!DataFilePath.Value

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray ErdenBusiness Systems AnalystAuthor Commented:
Thank you Gustav it worked just fine.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.