• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
trusxlsol
Asked:
trusxlsol
  • 2
1 Solution
 
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
 
trusxlsolBusiness Systems AnalystAuthor Commented:
Thank you Gustav it worked just fine.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now