Avatar of Ray Erden
Ray Erden
Flag for United States of America asked on

Access 2016 - Table relinking via VBA process stopped working!

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
  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?
DatabasesMicrosoft AccessNetworkingVBA

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
crystal (strive4peace) - Microsoft MVP, Access

does tblLinkedTables2 have a field called TableName?
(the error message seems to indicate that it does not)

you mentioned "tblLinkedTables1" -- is tblLinkedTables2 the right table name?

on cleanup:
before Set rst = Nothing, add:

Open in new window

~~~~~~~~~ Compile ~~~~~~~~~
Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing any code.
from the menu in a VBE (module) window: Debug, Compile [the name of your project]
(Alt-F11 to switch to the code window)
Fix any errors on the highlighted lines.
Add needed references, remove unnecessary references, resolve missing references
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up by the compiler as well as many other errors than can be fixed before running.

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:
 Option Explicit  ' require variable declaration

Open in new window

If this was not done when the code was written, you will may need to DIM some variables -- it is best to do that anyway

have an awesome day,
Gustav Brock

I would say, that here:

Set tdf = dbs.TableDefs(rst!TableName)

Open in new window

you should look what rst!TableName returns or holds.
It must be, that the tablename stored does not exist (has not been linked) in the frontend.
Ray Erden

Gustav Brock,

For your comment on tdf = dbs.TableDefs(rst!TableName)

I checked every single table in both locations and there is no any missing table or any name change or name conflict, missing table name etc.
Checked the links in both locations and they all return data from their connected tables.

Table names in rst!TableName are intact and correct
Paths stoted in TableName are correct.

As far as Tools>References below are my findings:

It was working with the below references

Visual Basic for Applications
OLE Automation
Microsoft Access 16.0 Object Library

I added in my remedial process Microsoft DAO 3.6 Object Library and obtained below error,

Run time error 3265 Item not found in this collection

I removed Microsoft DAO 3.6 Object Library and obtained below error,

Compile Error: User defined type not defined

I tried to deselect Microsoft Access 16.0 Object Library I got a warning as below,

Can't remove control or reference in use

The only change that I applied was an additional table was created and above link structure was updated accordingly in both locations.  Also added the corresponding path of the new table to the TableName.

What else can cause error at this point I am totally clueless.  I will add to illustrate the problem two sample Access files to depict the problem better.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gustav Brock

Paths stoted in TableName are correct.

That's the error source. No path; rst!TableName.Value must hold a tablename only.
Ray Erden

I have on top below Option Explicit and Option Compare Database but still having below error.

Compile Error: User defined type not defined

Also I added rst. close before rst = Nothing but nothing changed.
Gustav Brock

The database reference is: Microsoft Office 16.0 Access database engine Object Library
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
crystal (strive4peace) - Microsoft MVP, Access


> "User defined type not defined"

which statement does this happen on? thanks

As Gustav said, make sure that the DAO library is referenced: Tools, References from the menu when you are in code.

> "rst. close ... but nothing changed"

you can't see it in the interface, but setting to nothing doesn't release memory if the recordset is sill open.  

have an awesome day,
Ray Erden

Gustav - Below is referenced

Microsoft Office 16.0 Access database engine Object Library

Crystal The error message "User defined type not defined"  happens on below line

Dim dbs As DAO.Database
Ray Erden

Crystal and Gustav,

I have attached two sample Access files to be able to illustrate my problem better. Below is the information about the setup.

File1 : TEST db
File2: LOOKUPS db

LOOKUPS db stores data only and there is a single table in it named as tbl_USD

TEST db is the one that changes its location between the network and local drive and the linked tables paths need to be changed as TEST db file changes its location.  

Public Function RelinkByListLocal() As Integer and Public Function RelinkByListNetwork() As Integer functions change table links after the TEST db file gets copied between drives back and forth and replaces the existing one on the target drive.

After I copy TEST db from one drive to the other I do below,

I run RelinkByListLocal()  function after copying the TEST file from the network into my local drive to convert the table links seeing the network location to the local tables.
I run RelinkByListNetwork()  function after copying the TEST file from my local drive into the network drive to convert the table links seeing the local drive to the network tables.

On the TEST db

tblLinkedTables1 stores the paths for the network drive
tblLinkedTables2 stores the paths for the local drive

qryLinks shows the current links
qryLinksTable make table query is the one that I used to create the tables storing paths.

You can use qryLinks to modify the tblLinked Tables1 and tblLinked Tables2 on your environment.

Would you please give it a try using the sample files to see what makes this code thick?  Again it has been working like a charm but stopped working and I don't have any explanation why.  I hope this setup makes it easier to look into the process and to isolate the issue.

Thank you both for your help on my problem.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gustav Brock

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ray Erden

Gustav Brock - The problem has been resolved after replacing the part of the existing code where applicable with the above 3 lines that you provided.  

Thank you very much for help.
Ray Erden

Thanks a bunch!
Gustav Brock

You are welcome.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.