You can try it out yourself: Begin by creating a new, empty Access database and importing all the code modules you want to package as a global resource for your Access applications. Next, be sure all the procedures are declared globally or publically so they'll be accessible from other MDBs. Finally, create an MDE from this database to create a permanent, secure library of your routines.

At this point, you have several ways to make the code stored in your MDE library accessible to other databases. Perhaps the easiest way is to put the MDE on a file server somewhere on the network. Then, add a reference to the MDE to each database that needs its code.

The reference can be added manually or through code. Adding a reference through the Tools > References dialog is easy. Simply open the dialog, select the Browse button, and navigate to your MDE file. After you've selected your MDE, all the publicly declared procedures are accessible to the MDB.

But, it might be more useful to add the reference through code. Using code means you don't have to instruct your users how to perform this task, and the code can even repair a broken reference if necessary. This code segment shows how it's done:

Private Sub cmdAddTheReference_Click()
  Access.References.AddFromFile <Path-To-MDE-File>
End Sub
An example might be:
Access.References.AddFromFile _
   "\\Elvis\Drive_C\PublicCode\MyCode.mde"

In this case, I used a universal naming convention (UNC) path to eliminate the problems inherent when using mapped network drives. As long as Windows can resolve the path (\\Elvis is the server's name, Drive_C is a shared drive, and PublicCode is a folder), the VBA engine is able to use the code in the MDE. It's also possible to put tables, forms, reports, and other database components in an MDE library. For instance, an MDE library might be the ideal place to keep your error logs, message dialogs, and lookup tables (ZIP codes, etc.). You can even keep reports and data-entry forms in the MDE. Because it's an MDE, you know your users won't be trying to "improve" your forms and reports!

One caveat: Many Access developers are still using DAO to work with Access data. Keep in mind that CurrentDb refers to the database that's open right in front of the user. Use CodeDb to refer to the database where the code resides. When running code stored in an external MDE library, CodeDb references the library database, not the working MDB. Therefore, use DAO code like this to open a recordset in the library database:

Set rs = _
   CodeDb.OpenRecordset("tblMyTable", dbOpenDynaset)

When using ADO, be sure to correctly reference either the library or user database in the Connection or Recordset object's ConnectionString property.

To learn more Access architecture secrets, don't miss Mike Groh's session at the June 2003 Advisor DevCon in Las Vegas (http://Advisor.com/MSDevCon).