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:
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!TableNam
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,
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("tblLink
' Loop through the recordset, processing rows
Do Until rst.EOF
Set tdf = dbs.TableDefs(rst!TableNam
'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?
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!TableNam
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("tblLink
' Loop through the recordset, processing rows
Do Until rst.EOF
Set tdf = dbs.TableDefs(rst!TableNam
'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?
DatabasesMicrosoft AccessNetworkingVBA
Last Comment
Gustav Brock
I would say, that here:
It must be, that the tablename stored does not exist (has not been linked) in the frontend.
Set tdf = dbs.TableDefs(rst!TableName)
It must be, that the tablename stored does not exist (has not been linked) in the frontend.
ASKER
Gustav Brock,
For your comment on tdf = dbs.TableDefs(rst!TableNam
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.
For your comment on tdf = dbs.TableDefs(rst!TableNam
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.
Paths stoted in TableName are correct.
That's the error source. No path; rst!TableName.Value must hold a tablename only.
ASKER
Crystal
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.
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.
The database reference is: Microsoft Office 16.0 Access database engine Object Library
hi,
> "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,
crystal
> "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,
crystal
ASKER
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
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
ASKER
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.
TEST.accdb
dimdb_LOOKUPS.accdb
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.
TEST.accdb
dimdb_LOOKUPS.accdb
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
Thank you very much for help.
ASKER
Thanks a bunch!
You are welcome.
(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:
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 anywayhave an awesome day,
crystal