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
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
Set tdf = Nothing
Set rst = Nothing
Set dbs = Nothing
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