Click here to Skip to main content
Click here to Skip to main content
Go to top

Automate the Build of Microsoft Access Applications

, 15 May 2006
Rate this:
Please Sign up or sign in to vote.
A class for automating the build of Access applications.

Build Access Application - Screenshot

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

benherron
Web Developer
Australia Australia
Ben is a freelance developer currently based in Sydney, Australia. His first computer was an Atari 800 acquired in about 1981. Once the games had all been played to death, Ben turned his attention to programming and found that he liked it.
 
Since then he has completed a uni degree in IT and worked for several companies including banks, government authorities and call centres. The highlights of his career so far have been writing a ticket allocation system for the Sydney 2000 Olympics and developing Telephony solutions.
 
Ben's last contract ended when the company decided to outsource to China. The job before that he lost because the company outsourced to India and Malaysia. Ben would appreciate anyone wanting to Home-Source development work to him. He has recently been consulting to small businesses helping with databases, reporting, sales and marketing solutions.

Comments and Discussions

 
QuestionInvalid argument? Pinmembervbevan1-Feb-12 19:26 
GeneralSome bugs found Pinmemberralfonat17-Dec-06 23:50 
GeneralRe: Some bugs found Pinmemberbigbadben22-Dec-06 18:47 
GeneralRe: Some bugs found PinmemberRamBert12-Apr-07 11:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 15 May 2006
Article Copyright 2006 by benherron
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid