Automating Microsoft Access with VBA

To work with objects from other applications, you must first create those objects in your Access code. There are three ways to do this:

• Using the CreateObject function

• Using the GetObject function

• Using early binding

We show you each of these ways in turn.

Using CreateObject

The CreateObject function enables you to specify an object by its name, and creates an instance of that object. Here's a short example:

Sub CreateObjectDemo() ' Use Word as as automation server ' via CreateObject Dim objWord As Object Set objWord = CreateObject("Word.Application") objWord.Visible = True objWord.Quit End Sub

As you can see, this code uses the special variable type Object to refer to the object that it creates. Object is a variable type that can refer to any object: a Word document, an Excel chart, an Access form, or anything else.

The CreateObject function turns this generic Object variable into a particular type of object. The argument to CreateObject is what's known as a ProgID: a value that signifies the type of object that you want to create. You can put together most ProgIDs that you need from the name of the application plus the name of the object within that application. So, Word.Application refers to the Application object supplied by Word (as distinct from, say, the built-in Application object provided by Access).

As soon as you run the line of code that calls CreateObject, the variable objWord now refers to an instance of the Word Application object. This object represents all of Microsoft Word (just as the Access Application object represents an entire Access session). But if you put a breakpoint in the code and execute this line, you won't find Word anywhere. That's because Word, like most automation servers, starts itself invisibly when it's launched from an automation call. The next line of code sets the Visible property of the Word application to True; that line causes the application to become visible on your screen.

Finally, the code calls the Quit method of the Word Application object. This is the equivalent of choosing Exit from the File menu within Word, and causes Word to vanish again.

Note that, apart from CreateObject itself, there's no new syntax here. After you have retrieved an automation object, you can use its methods and properties just like those of any other object within Access.

Using GetObject

The GetObject function is very close to the CreateObject function. Here's a sample that uses GetObject:

Sub GetObjectDemo() ' Use Word as as automation server ' via GetObject Dim objWord As Object Set objWord = GetObject("", "Word.Application") objWord.Visible = True objWord.Quit End Sub

If you run this code, you'll find that it works the same as CreateObjectDemo. But there are two differences between CreateObject and GetObject:

• CreateObject always creates a new instance of the automation server. GetObject uses an existing instance if there's one running on your system.

• GetObject takes an optional first parameter specifying a document. If you call GetObject("c:\temp\doc1.doc", "Word.Application"), it will load the specified document into the Word application as well as create the Application object, if necessary.

GetObject is useful when you want to work with specific documents, or when you're concerned with minimizing the resource impact of your automation code on a low-memory system (because it won't launch extra copies of the server unnecessarily).

Using Early Binding

Both CreateObject and GetObject use what's known as late binding. With late binding, you don't specify in your code which object type to use; instead, you use the generic Object for your variables and let VBA figure it out. The third way to create objects, using early binding, enables you to use the native variable types from an automation server directly in your VBA code.

TIP

Late binding works with or without a reference to the type library for an automation server. Early binding requires a reference to the appropriate type library. Although VBA provides a way to add a reference (using the References collection), our experience has been that this is unreliable in practice.

Here's the sample code converted to use early binding:

Sub EarlyBindingDemo() ' Use Word as as automation server ' via early binding Dim objWord As Word.Application Set objWord = New Word.Application objWord.Visible = True objWord.Quit End Sub

With early binding, you actually use the object's native type in your VBA code. This is made possible by having a reference set to the object's automation server. After you've done this, you can declare and instantiate the automation object using exactly the same code as you use with a native Access object.

We recommend using early binding for these reasons:

• You don't have to remember two different syntaxes for creating objects.

• You get the benefit of IntelliSense when you're writing code, which means you can catch errors before they affect your users.

• Early binding is faster than CreateObject or GetObject.