Why an add-in or referenced library Database?
Reuse of general purpose utility code such as relinking tables, current database path and name and all sorts of other code we developers have created over the years we want to share.
if you want to create an add-in for wide spread use outside your control then you should consider creating the MDE as an add-in with the USysRegInfo table. Otherwise just setting a reference works fine as Access 2000 and newer look in the same folder as the calling MDE for reference MDE. However setting references doesn't work if you're going to distribute an add-in using MDEs with a different path unless they are identical on your system and the workstation on which the Access databases are going to be installed. (If you are still running Access 97, and you shouldn't be now, see the last section on this page.)
How to use an Access database as a reference
Manually set a reference by going into a code module and clicking on Tools > References. The referenced Access database must be in the same folder as the calling database otherwise it's a lot of extra work. You can use the free Auto FE Updater to ensure the FE MDB/MDE/ACCDB/ACCDE are distributed along with the referenced Access databases.
How to use an Access database as an add-in
Read up on using the USysRegInfo table.
Microsoft's Pages on References and RefLibPaths
Forms in Add-Ins
App means the main mdb which has the reference to the add-in.
Calling a form in an add-in from the app
You have to call a subroutine in the add-in which in turn opens the form.
Referencing tables or queries in app
If the form references tables or queries in the app you need to set the recordsource of the form in the On Open Event. You also need to add the "IN C:\Program Files\MyDirectory\MyApp.MDB" clause to any queries referencing tables in your apps MDB.
Calling a form in the App from the add-in
Just execute a docmd.openform
Accessing Tables in Apps MDB
Set dbsMDB = CurrentDb
Set dbsMDB = CurrentDb
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
You can use the CodeDb function in a code module to determine the name of the Database object that refers to the database in which code is currently running. Use the CodeDb function to access Data Access Objects (DAO) that are part of a library database.
If you update your add-in on your system, then work on a app and ship it, you must ship the updated add-in as well. Otherwise you get an ugly error message. You will still get the ugly error message even if you didn't change any subroutine or function names or parameters.
Choose unique function/subroutine names which will never, ever conflict with function/subroutine names in your app or any other possible external reference such as Outlook, Word, Excel, Visio or others. I chose to prefix all mine with tt_
If you run into a problem in the add-in, and its an MDB, Access will allow you to open and view the code in the add-in. Trouble is as soon as you exit the code, even though you saved the code, it actually isn't saved. You've lost your work. You must open the add-in as a separate MDB and update it directly.
Jurgen Welz jwelz at hotmail.com did a performance test on using subroutines in add-ins. The first time you call a subroutine in an add-in it takes 200 milliseconds. All subsequent calls take 11 milliseconds. Even when you've closed and re-opened the database or rebooted your system.
What is interesting is that calls to subroutines within the main MDB/MDE also take 11 milliseconds. Thus the first time you do the call to an add-in you're taking a slight performance hit but thereafter its exactly the same.
Other Microsoft Links
The documentation on RefLibPaths in Access 97 tells you that an MDB will look in the same path. But the documentation doesn't tell you that an MDE will not look in the same directory as it was run from for the add-in. Thus you need to use a registry key which you can set in the ODE. See RefLibPaths in the Access Help. However the documentation on RefLibPaths, at least in the Access 97 help, isn't very clear.
For Access 95 the like is
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\ACCESS\7.0 however I
haven't personally tested this.
Under that key you need to add a key where Value Name is the name of your add-in MDB/MDE and Value Data is the path of your add-in.
[ Access Tips | Access | Main ]
Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.
Granite Fleet Manager - the best designed fleet maintenance tracking and management system available
Website copyright © 1995-2013 Tony Toews