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?
Ray ErdenBusiness Systems AnalystAsked:
Who is Participating?
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.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 BrockCIOCommented:
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 ErdenBusiness Systems AnalystAuthor Commented:
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.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Gustav BrockCIOCommented:
Paths stoted in TableName are correct.

That's the error source. No path; rst!TableName.Value must hold a tablename only.
Ray ErdenBusiness Systems AnalystAuthor Commented:
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 BrockCIOCommented:
The database reference is: Microsoft Office 16.0 Access database engine Object Library
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:

> "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 ErdenBusiness Systems AnalystAuthor Commented:
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 ErdenBusiness Systems AnalystAuthor Commented:
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.
Gustav BrockCIOCommented:
It helps if you use the actual field names of your table:

    Set tdf = dbs.TableDefs(rst!Name.Value)
    'tdf.Connect = rst!DataFilePath
    tdf.Connect = ";DATABASE=" & rst!Database.Value

Open in new window

And the reference to the database engine was still missing. The code must compile to work.

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:
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 ErdenBusiness Systems AnalystAuthor Commented:
Thanks a bunch!
Gustav BrockCIOCommented:
You are welcome.
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

From novice to tech pro — start learning today.