Poplar ProductivityWare Articles:
Successful Use of the Microsoft Access 2002/XP Runtime Edition
by Jennifer Hodgdon

If you purchase Microsoft Office Developer Edition (ODE), you will receive, along with the usual Office programs (Word, Excel, Access, etc.) and some helpful programming utility programs (such as Visual Source Safe), an unlimited license to distribute Access databases with the Access Runtime (ART). The ART is a limited edition of Microsoft Access, which allows people who have not purchased a full version of Microsoft Access to use database applications you have created, and it can definitely be an economical way to distribute them. However, not every database application you create will run successfully with the ART, so this article discusses what you need to do to ensure that your databases will install and run correctly.

Much of the content of this article I learned through trial and error, and/or long sessions of searching on the Web, and my hope is that reading this article will spare you from having to make the same effort. Of course there is no guarantee that it will work for you -- I have only tested it on a limited number of Access 2002/XP databases, installed with the ART on a limited number of end-user computers. Besides this article, I would advise you to read about deploying ART applications in both the Access/Access Visual Basic help files and the book Microsoft Office XP Developer's Guide that comes with the ODE.

If you have any suggestions, comments, or corrections, please contact Poplar ProductivityWare.

Table of Contents:

• Introduction (above)
• Menus, Toolbars, and User Interface
• Components and References
• Connecting to Other Databases
• Testing your Application
• Installing your Application
• You may also wish to read our companion article on Access programming tips

Abbreviations used in this article:

• ART: Microsoft Access Runtime, generally referring to version XP/2002
• ODE: Microsoft Office Developer Edition, generally referring to version XP/2002
• VBA: Microsoft Visual Basic for Applications

---

Menus, Toolbars, and User Interface

One difference between the full version of Access and the ART is that the default Access toolbars are not available in the ART. Also, menu options are somewhat limited, Access help is not available, and users cannot view the database window or design views. Finally, if there is an error that in the full version of Access would pop up a debugging message, in the ART, it is supposed to simply shut down the Access application (though I haven't seen this happen, that's what the Microsoft documentation says will happen -- I'm not entirely sure).

For this reason, the ODE documentation and Access/Access Visual Basic for Applications (VBA) help files contain several suggestions for creating a database that will run properly under the ART. Some of the essentials (see the Access help for more information):

• Create a startup form for your database
• Have a separate help file for your database
• Create toolbars for your database, and probably custom menus as well
• Make sure that each VBA subroutine and function handles errors using the On Error statement

Back to table of contents

---

Components and References

When you are programming in VBA, you can add "components" and "references" to your Access project, which contain extensions to the Access object library. For instance, you might use the Microsoft Scripting Runtime to do common system tasks (such as finding a list of files in a directory folder), and the Microsoft Common Dialog Control to put file open and save dialog boxes on the screen for the user. You might also add a library for another Office application, if your project opens and manipulates Word or Excel documents.

The problem with using components and references with the ART is that for them to work correctly, your end user will have to have the exact version of the DLL or OCX file you had on your computer when you created the reference. Most of these DLL and OCX components and references depend on other DLLs, and some will not work unless special registry keys are also created when they are installed, so even if you have a license to distribute the DLL or OCX file your database references, actually getting the installation to work consistently on your end-users' machines is unlikely. So, my advice is to avoid components and references completely, if you are using the ART.

Here are some work-arounds for commonly-used components and references:

• Instead of using the Microsoft Common Dialog Control's File Open and File Save dialog boxes, call directly into the Common Dialog DLL (comdlg32.dll), which exists on even very early versions of Windows. The functions you will need to call are "GetOpenFileNameA" and "GetSaveFileNameA", and you can find program listings showing how to call them from VBA from a Web or Microsoft Knowledge Base search on either of those function names (perhaps with Visual Basic as an additional search term). (If you are not familiar with the Knowledge Base, you can currently access it by visiting the Microsoft web site, and then choosing Knowledge Base, in the Support menu item on the left.)
• Instead of using the Microsoft Scripting Runtime (or another component -- there are several available) to check for the existence of files, list files in directories, etc., use the built-in Dir function. (See the Access help file for documentation.)
• Instead of using Microsoft Word or Excel Object Library references to manipulate documents, try using built-in Access functions such as TransferSpreadsheet, OutputTo, and SendObject. (See the Access help file for documentation on these functions.) Unfortunately, if you need to do something more complex than what the built-in Access functions will allow, you probably will not be able to do it reliably within the ART unless you know that your users have the same version of Office installed as you do.
• It is safe to use the DAO object library, Access object library, OLE Automation reference, and Visual Basic for Applications reference, because these are all basically built into the ART installation.

Back to table of contents

---

Connecting to Other Databases

The Access help files, and other sources, suggest that if you need to manipulate an external Access database from within your application, you should (in VBA) call the CreateObject function to create an Application object, use the OpenCurrentDatabase and NewCurrentDatabase functions to attach a database to it, and then manipulate the external database using the standard Application object methods. While this method works fine when you are running the full version of Access, it doesn't work at all from the ART. First of all, the CreateObject function will not work for someone who doesn't have the full version of Access installed, and even if you use the Microsoft Knowledge Base's suggested ART work-around for CreateObject, the OpenCurrentDatabase and NewCurrentDatabase functions are disabled in the ART.

Luckily, you can use the DAO library's CreateDatabase and OpenDatabase functions instead, and the DAO library's Database object has most or all of the functionality you will need. (See the Access VBA help file for documentation on the functions and the DAO Database object, and you'll need to add the DAO Object Library reference to your project to use them.) Alternatively, you may be able to simply use the TransferDatabase function to copy tables from one database to another, and avoid manipulating the remote database directly (again, see the Access VBA help file for documentation).

Back to table of contents

---

Testing your Application

The ODE documentation and Access help file suggest that to get an idea of how your database application will run under the ART, you can simulate it by using the /runtime command-line startup option (see Chapter 8 of the Microsoft Office XP Developer's Guide, or the Access help file, for more information on how to do this).

However, I think that this is bad advice. There seem to be many differences between the ART and full versions of Access that are not covered by the /runtime option, so it is not a very good test of whether your application will work or not. Installing the application with the ART installer (see the section on installation) on your own development machine also doesn't work, because the fact that you have the full version of Access installed apparently makes your application behave differently than it would on your end user's machine. So, I believe that the only option is to test it on a machine that has never had the full version of Access installed, if you really want to see if your application will work with the ART.

Back to table of contents

---

Installing your Application

The ODE comes with a "Packaging Wizard", which is supposed to allow you to create installation programs to install your database application with the ART on your user's machines. I guess it's possible that the installation programs the wizard creates work sometimes, but my experience has been that they don't work consistently. I also found the wizard to be rather inflexible, and it lacked some options that I wanted for my installation.

Luckily, there is another option: a Canadian company called SageKey specializes in making software to install the ART, and their installer wizards actually work. Their stand-alone wizard has most of the options you will probably need, and if you have InstallShield or Wise, you can use the SageKey wizard to make a working ART installation as a starting point, and then use the other programs to customize the installation to your heart's content. I don't believe there is really any other viable option out there for making ART installations that actually work, so even though SageKey's wizard is not cheap, it's worth the cost in my opinion. (And no, they didn't have anything to do with this article, and didn't provide me with any free software, rebate, or other compensation for this endorsement. I'm just a satisfied customer.)

Back to table of contents

---