The Connection Object

 < Day Day Up > 

The Connection Object

The Connection object describes the connection between the application program and the database server. Coupled with the Errors collection and the Error object, the Connection object controls the logical connection to the database.

Key Properties and Methods of the Connection Object

Table 23-1 lists some of the key properties and methods associated with the Connection object.

Table 23-1: Key Properties and Methods of the Connection Object




Method: closes the connection to the database.


Property: defines how to connect to the database.


Property: specifies where the current record pointer is maintained. Can be: adUseClient or adUseServer.


Property: pointer to an Errors collection object containing the list of individual Error objects that describe the most recent error encountered on the connection.


Property: determines how the database is opened. Can be adModeRead, adModeWrite, adModeReadWrite, adModeShareDenyRead, adModeShareDenyWrite, adModeShareExclusive, or adModeShareDenyNone.

Open ConnectionString,
UserId, Password

Method: establishes a connection to the database using the optionally supplied ConnectionString, UserId, and Password values.


Property: describes the state of the connection. Can be adStateClosed or adStateOpen.

The Open method opens a connection to the database using the optional information. If not supplied, the information from the ConnectionString property will be used. The Close method terminates an open connection to the database server. The State property allows you to determine if the connection is open or closed to the database.

The CursorLocation property determines where the cursor is kept. Typically, you’ll set this property to adUseServer to simplify the programming involved, especially if you’re using Access. However, if you’re using SQL Server, Oracle, DB2, or some other shared database server, you might want to check with your database administrator to determine what value is appropriate.

The Mode property determines what level of data access you require. Specifying adModeRead means that your program only reads data from the database and that your program can coexist with other applications that specified adModeRead. If you plan only to write to the database (which isn’t very common), you can use the adModeWrite value. However, if you plan to read and write information from the database, you should choose the adModeReadWrite value.

The adModeShareDenyRead, adModeShareDenyWrite, and adModeShareExclusive properties all control how other programs will share the database with yours. In general, if you have multiple users sharing the same database, see your database administrator for the appropriate values for the Mode property.

Connecting to Different Database Management Systems

The ConnectionString property is the most important property in the Connection object. The exact information in the connection string varies depending on the type of database you want to use.

Connection strings are composed of a series of keywords and values separated by semicolons that provide the information necessary to locate the data provider and the database, along with any security information that might be required.

Connecting to Access Databases

Access databases rarely require little more than the name of the provider and the location of the database file in a connection string, like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel2k3\Customer.mdb

The Provider keyword selects the data provider for Access. For Access 2000 and newer databases, you should use the Microsoft.Jet.OLEDB.4.0 provider. Older Access databases might require this provider: Microsoft.Jet.OLEDB.3.51.


The database engine typically used to power an Access database is known as Microsoft Jet. In addition to Access, several other products rely on the Jet database engine. However, because most people access Jet only through Access, it’s common to refer to the database engine as Access as well.

The Data Source keyword points to the location of the database on disk. Typically, this is a file path as shown in the earlier example, although it can reference a file using a file share format (\\athena\d\Excel2k3\Customer.mdb).

Connecting to SQL Server Databases

Connecting to SQL Server databases involves a somewhat more complicated connection string, although it’s not as bad as you might expect.

Initial Catalog=Music;User ID=Wayne;Password=TopSecret

The Provider keyword references SQLOLEDB.1, which is the SQL Server data provider.

The Data Source keyword points to the database server that you want to access, and the Initial Catalog keyword indicates the name of the database on the particular database server that you want to open.

Finally, the User ID and Password keywords present authentication information to the database server. If you wish to use your Windows authentication information—the User ID that you gave when you logged onto Windows—you can replace the User Id and Password keywords with the Integrated Security keyword, as in the following connection string:

Initial Catalog=Music;Integrated Security=SSPI

Connecting to Other Databases

As long as you can find the proper data provider you can connect to many different types of databases. The main requirement is that the database supports OLE DB. Some of these databases and their providers are listed in Table 23-2.

Table 23-2: Other OLE DB Data Providers


Data Provider(s)


IBM OLE DB Provider for DB2


Microsoft OLE DB Provider for Oracle

Oracle Provider for OLE DB

In case the database you wish to use doesn’t support OLE DB, but supports only ODBC, Microsoft includes a special data provider named Microsoft OLE DB Provider for ODBC drivers, which translates OLE DB calls into ODBC. However, you should use this provider only if you can’t find an OLE DB provider because performance will suffer.

Using the Errors Collection

The Errors collection contains information about the errors that occur while executing a database command. Because it’s quite possible that one error might trigger several others when executing a command, you can’t always rely on the VBA Err object to reveal the true cause of the error. Therefore, the ADO Errors collection contains the full set of errors that occur while executing a command. (See Table 23-3.)

Table 23-3: Key Properties and Methods of the Errors Collection




Method: Removes all the items from the collection.


Property: Returns the number of items in the collection.


Property: returns the Error object at the location specified by index.


Clearing the Err
Even though you can’t rely on the Err object for all the details about an error, you can rely on the Err object to identify that an error has occurred. Just remember to use the Clear method to remove any previous information in the Err object before you execute your database request.

The Errors collection object is a typical collection object containing a Count property and an Item property that returns an Error object. You can use a For…Each loop to process all the items in the collection, and then use the Clear method to erase all the Error objects in the collection.


The Errors collection is populated only when an error occurs. Use the Clear method before you execute a command to avoid detecting an error that has already been handled.

Using the Error Object

The Error object contains a number of properties that describe a specific error condition. (See Table 23-4.) For the most part, these properties provide information about the error from several different perspectives, which might be necessary depending on the exact cause of the error.

Table 23-4: Key Properties of the Error Object




Contains a short text description of the error.


Contains a provider-specific error code.


Contains the OLE DB error code.


Contains the standard five-character ANSI SQL error code.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: