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!TableNam
e) - 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.
I use Access 2016 and have the below checked under References,
Below is the code for this process:
- Visual Basic for Applications
- Microsoft Access 16.0 Object Library
- OLE Automation
- Microsoft DAO 3.6 Object Library
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
Set tdf = Nothing
Set rst = Nothing
Set dbs = Nothing
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?
(the error message seems to indicate that it does not)
you mentioned "tblLinkedTables1" -- is tblLinkedTables2 the right table name?
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 windowIf 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,