troubleshooting Question

Access 2016 - Table relinking via VBA process stopped working!

Avatar of Ray Erden
Ray ErdenFlag for United States of America asked on
DatabasesMicrosoft AccessNetworkingVBA
13 Comments1 Solution387 ViewsLast Modified:
Please refer to my posting "Posted on 2017-12-20" with the question header "MS Access 2016 -  Dynamic update of linked table paths after location change of a database file".  Gustav Brock was the expert who helped me to resolve this issue on my original posting.

Summary of the problem:

Copying the same database file between the local and network locations with linked tables can be very tedious work.  To make my life easier to work with that structure I have been using functions that was automatically changing the links back and forth depending on the location.  If I was on my local drive changing from the network path to the local path.  Both locations have the identical file and folder structure.


Here is the current status of the process that has been working up until this morning.

File and Process Setup:

  • I have identical Access files on my local drive and on the network location.
  • Not very infrequently I copy these files back and forth and relinking tables between local and network drives. This have been done via VBA function.  As part of this process I created tables using a query based on mysysobjects to display the current link table paths.  I have two tables like this one for the local drive and one for the network drive.
  • Also, I came up with two sets of VBA code again to change the links using the available tables with the applicable paths in either location .  This process have been working like a charm since January 1st.
  • VBA function has not been changed
  • File names, file path and folder structure have not been changed
  • Linked table names have not been changed
  • Field names have not been touched
  • Table names holding link paths did not change

If it matters the only change is that I created a new linked table and accounted for it by adding to those tables storing the linked table paths and that was the only change.

But the process has been giving me error Runtime error 3265 Item not found in this collection.

When the error message is generated the yellow bar is on the below line

Set tdf = dbs.TableDefs(rst!TableName) - not that it helps a lot (sometimes) I still wanted to include it.

Upon receiving this error message first thing that I did was checking my Tools>References on the VBE screen and checked Microsoft DAO 3.6 Object Library.  I tried on a separate test file and it worked only once on my local drive by changing the link seeing the table over the network to the table residing on my local drive.

On the network it still was not working and giving me the same error. After so many trial and errors the current status is that it is not working and keep giving the same error again this is the process without any changes up until this am.

Considering the possibility of a corrupt Access file I created a brand new file and exported all the objects from the one I have been working with but this did not help either at all.

Additional Information:

I use Access 2016 and have the below checked under References,

  • Visual Basic for Applications
  • Microsoft Access 16.0 Object Library
  • OLE Automation
  • Microsoft DAO 3.6 Object Library


Below is the code for this process:

Public Function RelinkByListLocal() As Integer

I use this function to change the links from seeing the network drive to my local drive hence the name ending with Local. Same code for changing from local to network on the network with a function name ending network using tblLinkedTables1 storing network file paths for links.
 
Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim tdf As DAO.TableDef
   
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblLinkedTables2", dbOpenSnapshot)
   
  ' Loop through the recordset, processing rows
  Do Until rst.EOF
    Set tdf = dbs.TableDefs(rst!TableName)
    'tdf.Connect = rst!DataFilePath
    tdf.Connect = ";DATABASE=" & rst!DataFilePath.Value
    tdf.RefreshLink
   
    rst.MoveNext
  Loop
 
  Set tdf = Nothing
  Set rst = Nothing
  Set dbs = Nothing
End Function


So can any expert please offer some insight and help on this problem that I have been working non-stop since this morning without any resolution in sight so far?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros