Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

800

Closing an instance of an Automation object

Most Automation object variables are closed when the variable goes out of scope. However, allow-

ing an object to close itself doesn’t necessarily free up all resources that are used by the object. In

fact, it’s possible that the object will continue to exist even after Access is shut down. Whenever

possible, explicitly close the instance of the Automation object and set it to Nothing.

The best way to close an instance of an Automation object is to combine the two techniques,

like this:

WordObj.Quit

Set WordObj = Nothing

Nothing is a value reserved for object variables and signals VBA that you’re truly done with the

variable. The VBA engine will do its best to remove all traces of the object variable from memory,

reducing the chances of “memory leaks,” which are so common to applications using Automation.

Looking at an Automation

Example Using Word

Perhaps the most common Office application that is used for Automation from a database applica-

tion like Access is Microsoft Word. Using Automation with Word, you can create documents con-

taining information from Access databases. The following section demonstrates an example of

merging information from an Access database with a Word document.

Ordinarily, you create a merge document in Word and bring field contents in from the records of

an Access database. This method relies on using Word’s Bookmark objects, which are replaced by

the contents of Access database fields, and requires that you perform this action in Word — thus.

limiting the scope and capability of the function.

The following example uses the Orders form, which calls a module named WordIntegration.

The WordIntegration module contains a function named MergetoWord(), which uses the

Word Thanks.dotx template file.

Note
When you try to run this example, make sure that the path for the template in the VBA code is the actual path
in which the Thanks.dotx template file resides. This path may vary from computer to computer.

The items that are discussed in this Word Automation example include the following:

l (^) Creating an instance of a Word object
l Making the instance of Word visible
l (^) Creating a new document based on an existing template
l Using bookmarks to insert data