Automate the Build of Microsoft Access Applications

• Download source - 128 Kb

Introduction

There are several tasks that usually should be performed before releasing a non-trivial Access application. Typically, this includes removing Access objects that are no longer required, compiling, compacting and repairing the database, and making an MDE file. With Access, even after following these steps, sometimes the resulting application file is larger than what you would get, if you had created a new Access application and re-imported all your objects into the new MDB.

This article and code provides a means to automate this process, allowing the preparation and release of an Access application to be performed as part of a batch build script. The use of daily build scripts is a common practice for development teams using other platforms such as C++ and .NET. There is no reason why this practice should not also be undertaken by teams or individuals working with Access. Suggested reading on this topic is "Joel on Software" by Joel Spolsky. He lists having a daily automated build process as one of his 12 things that every software shop should do.

The code for building an Access application is incorporated into a class module called AccessApp. This class module represents an instance of a new Access application that is to be built. Methods of the class allow for:

• Import of Access objects from other MDB (references, menus and toolbars, import/export specifications, tables, queries, forms, reports, macros, modules, and data access pages).
• Compile application.
• Compact/repair database.
• Make MDE.

Using the code

You may use the class module methods to tailor your Access build to your needs. The downloadable files include code that demonstrates receiving build parameters as command-line arguments. The idea is to include the Access build as part of a BAT file. The process is to launch the AccessBatchBuild.mdb, passing the build parameters. The AccessBatchBuild.mdb will then execute the build steps, and on completion, terminate the Access instance, allowing the batch script to move onto any additional steps in your build sequence. A typical example of what the BAT file would contain to build the Access application is:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" 
        "D:\AccessBatchBuild.mdb" /nostartup 
        /x AccessBatchBuild /cmd D:\dbFrom.mdb/D:\dbTo.mdb

In plain English, this says: Launch MS Access; Open AccessBatchBuild.mdb; Invoke the macro AccessBatchBuild; and pass the From and To MDB locations as arguments. The outputs in this example will be two files: dbTo.mdb and dbTo.mde.

Additionally, there is a GUI interface provided - comprising a form that allows selection of a source Access application and specification of the location to save the resultant Access application that is built. See the screen image.

The code that performs the build creates an instance of the class and then performs the required steps. It looks like:

Dim FromApp As Application
Dim ToApp As AccessApp

'' The 'From' App is just a regular old Access.Application object.
Set FromApp = New Access.Application
FromApp.OpenCurrentDatabase FromMDBPath

'' The 'To' App is an AccessApp object
'' and does all the work of importing.
Set ToApp = New AccessApp
ToApp.Path = ToMDBPath
ToApp.NewApp

ToApp.ClearReferences

ToApp.ImportObjects FromApp, acReferences
ToApp.ImportObjects FromApp, acTables
ToApp.ImportObjects FromApp, acQueries
ToApp.ImportObjects FromApp, acForms
ToApp.ImportObjects FromApp, acReports
ToApp.ImportObjects FromApp, acDataAccessPages
ToApp.ImportObjects FromApp, acMacros
ToApp.ImportObjects FromApp, acModules
ToApp.ImportObjects FromApp, acRelationships
ToApp.ImportObjects FromApp, acImpExpSpecs
ToApp.ImportObjects FromApp, acCommandBars

ToApp.Compile
ToApp.CompactRepair
ToApp.MakeMDE

Set ToApp = Nothing

FromApp.CloseCurrentDatabase
Set FromApp = Nothing

The purpose of each class method should be apparent.

Points of interest

The hardest objects to import were the menus and toolbars. This involved a bit of recursion to walk the menu tree - where a menu may contain submenus.

The class presented here does not include error handling or logging, and perhaps such enhancements should be made. As such, if a runtime error or MDB compilation error is encountered during the build - the batch script will most likely get stuck with Access hanging - waiting for manual intervention. Feel free to add error handling and logging. In theory, the build process should not encounter errors as procedural controls should be in place such that the source objects for the build are in a state ready for release. Also, please be careful if you decide to build over the top of an existing MDB (i.e., source MDB = destination MDB). The class will handle this, and will create a temporary interim copy of the source MDB, in order to achieve this. But if things go horribly wrong, it is possible that you could lose your application. I suggest that you make the source MDB path different from the destination build MDB path to eliminate this risk.

Various Access settings are not retained when using the build. For example, the application Start Up settings (e.g., Form to launch at startup) are not retained. My idea is that your Access application should be setting these programmatically when the application is launched. The alternative is to enhance this build class to also carry across any settings like this that need to be retained from the source MDB.

Finally, I need work - so please contact me if you have anything for me. Also, I am interested in your opinion about this. Any bugs too, of course, testing this code was the most boring part, and therefore the end product may still need a bit of polish.

History

Initial submission on 14-May-2006.