Download a Zip file with the utility and demo back end files (version 2.2, Dec. 17, 2009).
Change in version 2.2:
- Revised to create the LDB file for the back-end database by opening the database via a DAO database object variable. This speeds up the looping through the TableDefs and reconnecting substantially
Change in version 2.1:
- Revised so that Linked Table Manager button is invisible when it's opened in the runtime
Changes in version 2.0:
- Compiled all the code modules into the dlgReconnect form's module so that you have to import only that form and won't run the risk of conflicts with public functions/subs with the same names as those used in the form
- Converted to A2000 format
Change in version 1.4:
- Revised CheckLinks error handler to behave properly with Error 3024
Changes in version 1.3:
- Rewrote CheckLinks routine to have an error handler. This insures that the back end will not be falsely reported as present when an error occurs (my bad!).
- Reformatted CheckLinks code to meet my current standards.
Changes in version 1.2:
- Changed behavior of LOCATE button so that is enabled at all times when the dialog is opened with "Override" OpenArgs.
- Made LINKED TABLE MANAGER command button usable in both Access97 and Access2K/2K2.
- Added error handling to LINKED TABLE MANAGER command button in case it's not installed.
- Added progress bar to the Jet and nonJet linking routines.
- Revised nonJet linking so that it does not delete the existing link until after it is known that the recreation of the link was successful.
The purpose of this utility is to provide a drop-in solution to reconnecting to back end data files for circumstances where:
- There is more than one back end data file.
- The back end data files are in multiple formats, not just Jet.
The key point to this utility is that it provides a simple, easy-to-understand use interface for reconnecting to multiple data sources. My motivation for putting this together came from the fact that I kept needing to write custom code for all my apps with multiple back ends (which includes most of my apps these days). I felt that the whole process could be modularized in a way that could be re-usable in any app, and this utility is the result.
At present, the utility handles Jet files and database formats that can be linked to via TransferDatabase (though I've been unable to test ODBC back ends, having no non-Jet ODBC data sources). I would like to have help in creating tools to handle spreadsheet and text file back ends, which remain unimplemented in Version 1.2.
I am retaining my copyright on this code, but am allowing free distribution and enhancement of the code as ong as my copyright notice is maintained in the code. In the spirit of the open source movement, I also require as a condition of use (on the honor system, of course) that anyone who enhances or extends the utility send the changes back to me for consideration for inclusion (with full credit, of course) in future releases.
I also ask that if you find this code useful, drop me a line and let me know how you are using it, or if you have any bugs to report or suggestions for improvements.
Instructions for Use
- Download Zip file with the utility and demo back end files
- Unzip the files to a folder of your choice.
- Open Reconnect.mdb. Simple instructions for using the code are given on the startup form, along with a button to demo the utility. Note that the Excel link is not yet handled by the application -- if you try to relink to it, the process will fail, leaving the Reconnect.mdb with no link to the Excel spreadsheet. In general, just import the "dlgReconnect" form and the three modules into your front-end MDB (or add the code from the modules to your existing modules), then open dlgReconnect in the startup routine of your front end database (you'll want to open it as a dialog, of course). The button to open the Linked Table Manager will not work if you do not have the advanced wizards installed, but the error is now properly handled, with instructions on installing it. Also, the Linked Table Manager command button will now work for both Access97 and Access2K/2K2.
- The code has lots of comments in it, some of which point out areas that need extension, revision or strengthening.