[ Main | Access | Tips ]

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.

• ACC2000: How to Create USysRegInfo Table for Installing Add-ins Q201735
• Creating the USysRegInfo Table for a Microsoft Access 2000 Add-In

Microsoft's Pages on References and RefLibPaths

• ACC2000: How Access 2000 Resolves Visual Basic for Applications References (Q248941)
• ACC2002: How Access 2002 Resolves Visual Basic for Applications References (Q286300)
• Add-ins, Templates, Wizards, and Libraries 

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 RSHelp = dbsMDB.OpenRecordset("zHelpFormsHelpContextIDs")

Or

Set dbsMDB = CurrentDb
dbsMDB.Execute ("DELETE * FROM zReportFieldName IN '" & _
     tt_CurrentAppPathNameExt & "';"), dbFailOnError

Or

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstTables = dbCurrent.OpenRecordset _
     ("z Delete Table Contents in ascending sequence", DB_OPEN_SNAPSHOT)

CodeDB function

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.

Gotchas

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.

Performance tests

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

• HOWTO: Build an Office 2000 COM Add-In in Visual Basic Q238228
• ACC: Creating and Testing Add-ins Paper Available in Download Center Q143270

Access 97

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.
For Access 97 the key is \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access

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.

Example:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\RefLibPaths
Value Name: zTestLib.mde
Value Data: c:\Program Files\Granite\Testing\"

ACC97: How Access 97 Resolves Visual Basic for Applications References (Q280465)