Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

491

Debug.Print cnn.Provider

cnn.Close

Set cnn = Nothing

End Sub

When working with ADO, it’s very important to close an object (if the object supports a Close

method) and set it to Nothing when your code is done with the object. ADO objects tend to stay

in memory once they’ve been opened, and must be explicitly closed and discarded (set to

Nothing) to clear them from memory. If an ADO object is not properly terminated, it may remain

in memory causing problems for users.

A Connection object requires the provider information and the data source. The provider speci-

fies which ADO provider (essentially a driver) to attach to the Connection object. For example,

there is a provider for SQL Server databases: one for the Jet database engine, and another for the

ACE database engine. Each provider knows how to connect to a different type of data, and endows

the Connection object with features specific to the data source.

The downside to the Connection object, and one that causes a lot of problems for Access devel-

opers, is the correct syntax to use for the Connection object’s ConnectionString property.

The ConnectionString must be properly composed and must reference a provider that is

installed on the local machine.

There is a little trick to discovering the ConnectionString to use against an ADO provider.

Start by creating an empty text file, and change its extension from .txt to .udl (universal data

link). Next, double-click on the empty file, and Windows opens the Data Link Properties dialog

box (shown in Figure 13.10). Use this dialog box to select a provider (on the Provider tab) and a

data source (on the Connection tab); then close the dialog box.

FIGURE 13.10

Use the Data Link Properties dialog box to set up a connection string.

Provider tab Connection tab