Microsoft Access Pot Pourri

Various Tips and Tricks

How To Get under the Hood of a Locked-Down MDB File
Looking to open locked-down Access databases? Hold down the Shift key when double-clicking an .MDB file, which will override all startup options.

Flag: /decompile
Usage: C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE /decompile DB.MDB

Decompiling an Access database will remove code bloat, bits of old code that may have been left behind after numerous revisions. Access compiles and stores VBA code within the .MDB file, but does not necessarily remove code that is no longer in use after code changes. Decompiling clears out all compiled code, and restarts the database with a clean slate.

After decompiling an Access database, two likely benefits will be a reduced MDB filesize and faster startup time. As such, decompiling often can be a worthwhile exercise. However, be sure to back up the MDB file prior to decompiling.

Passing Parameters between Forms and Reports (Access 2000 and Above)
There are situations when it is necessary to pass parameters between forms and reports. Using VBA, a form can invoke another form using the following code:

DoCmd.OpenForm (stFormName), , , , , , (OpenArgs)

stFormName is the form or report that gets invoked. OpenArgs is a string containing any parameters that are to be passed to stFormName. Passing one parameter is simple, but since OpenArgs cannot be an array, multiple parameters must be stacked in a string with delimiters inbetween. (Note: There are five other parameters for the DoCmd.OpenForm command, but are not covered in this example.)

The question remaining is how to interpret the OpenArgs in the invoked form or report. The easiest method is to add code in the form's On Open event. The OpenArgs string is stored in Me.OpenArgs. Again, passing one parameter is trivial, but if OpenArgs has several stacked parameters, the following snippet of code can parse the string into an array.

(Note: OpenArgs uses double semi-colons as its delimiter ";;")

Dim varOpenArgs as Variant

varOpenArgs = Split(Me.OpenArgs, ";;")

varOpenArgs(0) is the first parameter, varOpenArgs(1) is the second parameter, and so on.