I know, you’re asking why in God’s name would you create a new table Relinker when there are a great number already available!
The answer is simple, it could be done better.
The Problem
I do a lot of remote development which means I need to relink to remote database through slow VPN connection and I was wasting a great deal of time while relinking took place. As such, I did a few tests and notice that most, if not all relinker, including the built-in Linked Table Manager (LTM), performed the same operations
- For each table
- Open the source database
- Relink the single table
- Close the source database
The Better Approach
The fact of the matter is that most of the relinking time is actually spent Opening and Closing the source database, over and over, and over! What a waste. I knew this could be optimized to only open a source database once and relink all its’ tables and so I created my Table Relinker.
My table relinker works in a slightly different way
- For each source database
- Open the source database
- Relink all the tables associated with that source database
- Close the source database
This way each database is only opened once!
One word of caution, I created for my needs, so at present, it only handles linked Access tables.
In my tests with remote back-ends, my version decreased relinking times by 60-82%! The more tables you have in your database, the greater the time saving will be!
Disclaimer/Notes:
If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime
In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.
Download a Demo Database
Feel free to download a demo copy by using the link provided below:
Download “MS Access Table Relinker (accdb x32)” Relinker.zip – Downloaded 11598 times – 637.59 KBVersion History
V1.001 (2017-08-17)
Added support for password protected back-ends
V1.000 (2017-08-15)
Initial release
Implementation
So how can you use this in your database?
It’s surprisingly simple.
Copy/Import the following database objects into your database
- Macros
- mcr_RelinkTables_Force
- mcr_RelinkTables_IfReqd
- Modules
- mod_DB_Tables_Relinker
- mod_Dialogs
- mod_FilesFolders
Then you simply call the relink function or one of the Macros when you need.
Forceful Relinking
Using
Call RelinkTables(True)
or
mcr_RelinkTables_Force
You will prompt for the user to locate the BE file regardless if the current links are valid or not. This is what I called a forceful relinking.
Optional Relinking
Using
Call RelinkTables(False)
or
mcr_RelinkTables_IfReqd
Access will validate the current links.
- If they are valid, nothing else is done
- If they aren’t valid, then it will prompt the user to locate the BE file to be used and then relink those tables associated with the deprecated link path/file
That’s it! Copy a few object and call the function or Macro that suits your situation.
Real-life Implementation
In the Demo database, the AutoExec is a prime usage example. Typically, as part of your startup routine, you would call the mcr_RelinkTables_IfReqd macro to ensure that the linked tables are validate, if not get the user to update them.
I hope this helps!
I’ve tried it but nothing happens, can you explain a few steps that must be passed?
I will update the post to include some instructions.
Thank you. I’ve put this into my database.
Works great! Thank you.
Can you provide a similar code for ODBC’s?
Thank you
Thank you again for sharing this. It is very useful!
i used it but give me an error because my database has a password
I tried to use it but got an error that the “This key is already associated with an element of this collection”. This occurs on the macro as:
collBETablesSrcs.Add Item:=sBackEnd, Key:=sBackEnd
I suspect I’m missing something obvious but can you advise?
Thanks!
Same here. Did you found a solution?
Got the same problem this morning, Do we have an actual solution for this situation ?
Can you provide some repro information. I’ve tried the code on multiple versions of Access and even different bitnesses and have yet to get that error. Can you provide your Access version (2007, 2010, 2013, …), bitness (32 or 64-bit), build number (if applicable).
Thank you.
Sorry was out for the last two weeks.
this morning found out it was part of the ribbon, remove the Callback module and the USysRibbon table and it work perfectly. Sorry about the delay. My investigation was not complete. By the way it always refreshing reading your code.
Léopold Poirier
I did something much like this in 2010. The speed was greatly increased with the backend (source) staying open. I am going to compare this to that one and see what the differences are besides the obvious. (Different versions of access, 2007 vs 2016)
Thank you again for sharing this. Great work as usual, The VBA Relink Tables work perfectly for me. I’d like to include the possibility of using an ( .ini ) file as second choice If the the BE file is not located. To reduce intervention of user on non classic or network installation. Saw a mention “Read ini File?” ! Did you ever use it ?