ADO Connection Object Properties and Collections

[Previous] [Next]

Let's take a look at the properties and collections exposed by the Connection object, which are described in the following table.

Connection Object Properties and Collections
Property or Collection Name	Data Type	Description
Attributes	Long	Controls the behavior of the Connection object after CommitTrans or RollbackTrans has been called
CommandTimeout	Long	Sets the length of time that queries on this connection can run before timing out
ConnectionString	String	Specifies how to connect to your database
ConnectionTimeout	Long	Sets the length of time that ADO will wait before an attempt to connect to your database times out
CursorLocation	Long	Sets the default value for the location of the cursor library for Recordsets opened on this Connection object
DefaultDatabase	String	When connecting to Microsoft SQL Server and other database servers that expose multiple databases, specifies which database on the server to use
Errors	Collection of Error objects	Each Error object contains information about an error that occurred on the Connection object
IsolationLevel	Long	Controls the level at which transactions for the database are isolated
Mode	Long	Sets the permissions for modification of the Connection object
Properties	Collection of Property objects	Stores information about provider-specific properties for the Connection object
Provider	String	OLE DB provider name
State	Long	Current state (open or closed) of the Connection object
Version	String	Version of ADO

Attributes Property

The Attributes property takes a value of type XactAttributeEnum and allows you to control the behavior of your Connection object after you've ended a transaction, as described in the following table.

XactAttributeEnum Values
Constant	Value	Description
(No constant defined)	0	Default
adXactCommitRetaining	131072	Creates a new transaction when you call the CommitTrans method of the Connection object
adXactAbortRetaining	262144	Creates a new transaction when you call the RollbackTrans method of the Connection object

By default, the Attributes property is set to 0. This property is a bitmask and can be set to either of the values in XactAttributeEnum (adXactCommitRetaining or adXactAbortRetaining) or to the sum of those two constants. For more information on using this property, see the "Managing Your Transactions" section.

CommandTimeout Property

Queries sometimes take longer to execute than expected. Your database server might be busy, or your network connection might be slow because of traffic. Maybe your query is extremely complex. Or maybe your query will return large amounts of data. In any case, you might want the query to fail after a given amount of time rather than letting it run ad infinitum.

The CommandTimeout property defines the length of time in seconds that your query will run before it times out and generates an error. The default value is 30 seconds. Once the database starts to return data from your query, the CommandTimeout value is ignored. For example, let's say that you've lost your mind and submitted a query that will return a hundred thousand rows of data into a client-side cursor. Chances are that it will take more than 30 seconds to retrieve that much data. As soon as the OLE DB provider signals to ADO that it has the first row of data from the query, the CommandTimeout value is ignored and ADO starts to retrieve the results of the query.

If you want to let your query run for as long as necessary without ever timing out, set the CommandTimeout property to 0.

ConnectionString Property

You use the ConnectionString property to define what database you'll connect to and how you'll connect to it. We'll describe building connection strings in more depth in the section "Using Data Links to Build Connection Strings," where we discuss the methods that use this property.

ConnectionTimeout Property

The ConnectionTimeout property is similar to the CommandTimeout property. You use it to define, in seconds, how long ADO will wait before it times out of its attempt to connect to your database. The default value for this property is 15 seconds. As with the CommandTimeout property, if you want the connection attempt to continue indefinitely, simply set this property to 0.

CursorLocation Property

This property determines how the results of your query will be stored. The CursorLocation property for the Connection object has the same functionality as the same property for the Recordset object. For more information, see the "CursorLocation Property" section in Chapter 4.

DefaultDatabase Property

Some database servers, such as Microsoft SQL Server, expose multiple databases on a single server. When you install SQL Server 7, for example, you automatically get a Master database and two sample databases: Pubs and Northwind. If you're connecting to SQL Server and you don't specify the database that you want to use by explicitly setting this property or by defining the database in your connection string, you'll be connected to the Master database by default.

Errors Collection

The ADO Connection object exposes an Errors collection that contains information about the errors that occurred on that database connection. There are times when a single operation will return multiple error messages. Each message is stored in an Error object in the Errors collection.

NOTE

---

This book will not cover the Error object in much depth. Perhaps ADO's greatest drawback is that it frequently generates error information that provides little to no help in determining the cause of the error. When an error occurs, the Errors collection often contains a single Error object whose Description property simply returns, "Errors occurred." Sad, but true.

The next time you use the Connection object, the previous contents of the Errors collection will be cleared.

IsolationLevel Property

You can use the IsolationLevel property to control the isolation level of the transactions on your Connection object. The property can be set to any one of the IsolationLevelEnum values listed in the following table.

IsolationLevelEnum Values
Constant	Value	Description
adXactUnspecified	-1	Indicates that the provider is using an isolation level that cannot be determined
adXactChaos	16	Indicates that you cannot overwrite pending changes from more highly isolated transactions
adXactBrowse, adXactReadUncommitted	256	Allows you to view changes pending in another transaction Subject to nonrepeatable reads and phantom rows
adXactCursorStability, adXactReadCommitted	4096	Default; ensures that your transaction does not view any pending updates Subject to nonrepeatable reads and phantom rows, but immune to dirty reads (See "Definitions of Some Transactional Terms" in the section "IsolationLevel Property" for explanations of these terms.)
adXactRepeatableRead	65536	Ensures that your transaction does not view any pending updates and that rows you read are not modified by other transactions Subject to phantom rows
adXactIsolated, adXactSerializable	1048576	Specifies complete isolation from other transactions

Although the values might make this property look like a bitmask, you cannot set this property to the sum of two of these constants. Not all of these values are supported for all OLE DB providers, ODBC drivers, or databases. To learn more about using this property, see "Managing Your Transactions."

Mode Property

Use the Mode property to control the privileges (read-only, read/write) on your Connection object. You can set this property to any of the values available in ConnectModeEnum, which are shown in the following table.

ConnectModeEnum Values
Constant	Value	Description
adModeUnknown	0	Default; uses the default permissions to your database
adModeRead	1	Connects to your database with read-only permissions
adModeWrite	2	Connects to your database with write-only permissions
adModeReadWrite	3	Connects to your database using read/write permissions
adModeShareDenyRead	4	Prevents other users from reading your database while your connection is open
adModeShareDenyWrite	8	Prevents other users from modifying your database while your connection is open
adModeShareExclusive	12	Prevents other users from connecting to your database while your connection is open
adModeShareDenyNone	16	Allows other users to open the same Record with read and write permissions but prevents other users from using adModeShareDenyWrite or adModeShareExclusive

Administrators of client/server databases such as those implemented in Oracle and SQL Server might shudder at the thought of a property that a programmer can set in an application that limits database access to a single user at a time. However, those administrators don't need to lie awake at night worrying about such a property, since its capabilities are limited. Setting the Mode property to adModeShareExclusive before connecting to most databases won't prevent other users from connecting to the database. This property is designed for developers building applications for Microsoft Access databases and works only on Access.

You can read a value from or write a value to the Mode property while the Connection object is closed; when it's open, you can only read the value. Although the ConnectModeEnum values make this property look like a bitmask, it isn't. You can set the Mode property to only one of the values in ConnectModeEnum, or to the sum of constants in the enumerated data type if that sum equals another value in that type. For example, you can set the Mode property to adModeRead + adModeWrite because their values add up to the value of adModeReadWrite. You cannot, however, set the Mode property to adModeRead + adModeShareDenyWrite.

A write-only connection to an Access database is not possible, thus the adModeWrite and adModeShareDenyRead constants cannot be used on their own. These constants can be used only with their counterparts, adModeRead and adModeShareDenyWrite.

Properties Collection

ADO is designed to allow you to connect to all sorts of databases. Since no two database systems are exactly alike, exposing all their major features by means of a single object model is impractical, if not impossible. The standard properties, methods, and events of the Connection object allow ADO to expose a base level of functionality; additional functionality is exposed through the dynamic Properties collection. Some of the entries in the Properties collection, such as the Prompt property, apply to most OLE DB providers.

Actually, the Prompt property might appear familiar to RDO users. The rdoConnection object exposes similar functionality through the OpenConnection and EstablishConnection methods. You can set the Prompt property in ADO to any value in ConnectPromptEnum while the connection is closed:

 cnDatabase.Properties("Prompt").Value = adPromptNever 

After you've set either the Provider property or the ConnectionString property to specify the OLE DB provider you'll use to connect to your database, you'll see provider-specific items in the Properties collection. For example, if you set the packet size when you connect to SQL Server, the Packet Size property in the Properties collection will be set to that value. Similarly, you can set the Jet OLEDB:System Database property when you connect to an Access database, and that property will be set in the Properties collection.

If you want to learn more about provider-specific entries in the Connection object's Properties collection, take a look at the documentation on these OLE DB providers in the Microsoft Data Access SDK, programmatically navigate through the Properties collection on the Connection object after specifying the provider, or examine the All tab on the Data Link Properties property sheet. We'll talk more about using data links in the section "Using Data Links to Build Connection Strings," where we discuss connection strings in more depth.

Provider Property

This property contains the name of the provider that the Connection object uses to connect to your database. The Provider property is read/write when the Connection object is closed and read-only otherwise.

State Property

You can check the value of the State property to determine the current state of the Connection object. The Connection object has only three possible states: closed, open, or currently trying to connect to your database, as the following table shows.

ObjectStateEnum Values Applicable to the Connection Object
Constant	Value	Description
adStateClosed	0	Indicates that the Connection object is closed
adStateOpen	1	Indicates that the Connection object is open
adStateConnecting	2	Indicates that the Connection object is currently trying to connect to your database

Version Property

The Version property is read-only and returns a string that you can use to determine the current version of ADO installed. If you build an application with one version of ADO and run that code on a machine with a newer version of ADO installed, the Version property will return the newer version number.