Working with the ADODB.Connection Object

Working with the ADODB.Connection Object

The Connection object is the primary top-level ADO component. You must successfully open a Connection object to a data source before you can use associated Command or Recordset objects.

Connection Properties

Table 30.1 lists the names and descriptions of the properties of the ADODB.Connection object.

Table 30.1. Properties of the ADODB.Connection Object

Property Name	Data Type and Purpose
Attributes	A Long read/write value that specifies use of retaining transactions by the sum of two constant values. The adXactCommitRetaining constant starts a new transaction when calling the CommitTrans method; adXactAbortRetaining starts a new transaction when calling the RollbackTrans method. The default value is 0, don't use retaining trans actions.
CommandTimeout	A Long read/write value that determines the time in seconds before terminating an Execute call against an associated Command object. The default value is 30 seconds.
ConnectionString	A String read/write variable that supplies specific information required by a data or service provider to open a connection to the data source.
ConnectionTimeout	A Long read/write value that determines the number of seconds before terminating an unsuccessful Connection.Open method call. The default value is 15 seconds.
CursorLocation	A Long read/write value that determines whether the client-side (adUseClient) or the server-side (adUseServer) cursor engine is used. The default is adUseServer.
DefaultDatabase	A String read/write variable that specifies the name of the database to use if not specified in the ConnectionString. For SQL Server examples, the value is the default Initial Catalog.
Errors	A pointer to the Errors collection for the connection that contains one or more Error objects if an error is encountered when attempting the connection. The later "Errors Collection and Error Objects" section describes this property.
IsolationLevel	A Long read/write value that determines the behavior or transactions that interact with other simultaneous transactions (see Table 30.2).
Mode	A Long value that determines read and write permissions for Connection objects (see Table 30.3).
Properties	A pointer to the OLE DB provider-specific (also called dynamic) Properties collection of the Connection object. Jet 4.0 databases have 94 Property objects and SQL Server databases have 93. The next section shows you how to enumerate provider-specific properties.
Provider	A String read/write value that specifies the name of the OLE DB data or service provider if not specified in the ConnectionString value. The default value is MSDASQL, the Microsoft OLE DB Provider for ODBC. The most common providers used in the programming chapters of this book are Microsoft.Jet.OLEDB.4.0, more commonly known by its code name, "Jolt 4," and SQLOLEDB, the OLE DB provider for SQL Server.
State	A Long read-only value that specifies whether the connection to the database is open, closed, or in an intermediate state (see Table 30.4).
Version	A String read-only value that returns the ADO version number.

Note

Most property values identified in Table 30.1 as being read/write are writable only when the connection is in the closed state. Some provider-specific properties are read/write, but most are read-only.

Provider-Specific Properties and Their Values

When you're tracking down problems with Connection, Command, Recordset, or Record objects, you might need to provide the values of some provider-specific properties to a Microsoft or another database vendor's technical service representative. To display the names and values of provider-specific ADODB.Property objects for an ADODB.Connection to a Jet database in the Immediate window, do the following:

1. In the declarations section of the VBA code for the frmADO_Jet or frmADO_MSDE form, add the following object variable declaration:

    Private prpProp As ADODB.Property 

   Property objects exist in the Properties collection, so you don't add the New keyword in this case.

2. After the End With statement for cnnNwind, add the following instructions to print the property names and values:

    Debug.Print cnnNwind.Properties.Count & _    " {SQL Server|Jet} Connection Properties" For Each prpProp In cnnNwind.Properties    Debug.Print prpProp.Name & " = " & prpProp.Value Next prpProp 

3. Press Ctrl+G to open the Immediate window and delete its contents.

4. Reopen the form in Datasheet view to execute the Form_Load event handler, and return to the VBA editor to view the result in the Immediate window (see Figure 30.13).

   Figure 30.13. The Immediate window displays the first 19 of the 93 provider-specific properties for a connection to SQL Server 2000.

   

5. 

   To find a definition of a provider-specific property of Jet or SQL Server data sources, connect to the Microsoft Web site, copy or type the name of the property in the Search For text box, add double quotes (") to the beginning and end of the term, and click Search. Click the appropriate link (usually the first) to display the definition of the property (see Figure 30.14).

   Figure 30.14. Most Jet and SQL Server provider-specific properties have pages in the Microsoft Developer Network (MSDN) library for the Platform SDK (psdk). Click Show TOC to find your relative location within the library.

   

6. After you've satisfied your curiosity about provider-specific properties and their values, comment out or delete the added code. Sending a significant amount of data to the Immediate window delays opening the form.

Note

Pages that define SQL Server-specific properties specify values by reference to DBPROPVAL_... constants whose values aren't included in the table. Many searches for Jet-specific Property object definitions lead to the "Appendix C: Microsoft Jet 4.0 OLE DB Provider-Defined Property Values" page (http://msdn.microsoft.com/library/techart/daotoadoupdate_topic15.htm), which provides a set of constant values that you can add to an Access module.

Some of the SQL Server provider-specific properties appear in a list on the All page of the Data Link Properties dialog for a project's connection. To view these properties and their values, when set, open the NorthwindCS project, choose File, Connection to open the Data Link Properties dialog, and click the All tab (see Figure 30.15).

Figure 30.15. The All page of the Data Link Properties dialog for the NorthwindCS connection to MSDE displays a few of the 93 provider-specific properties of the OLE DB Provider for SQL Server (SQLOLEDB).

Tip

The "Appendix A: DAO to ADO Quick Reference" page (http://msdn.microsoft.com/library/techart/daotoadoupdate_topic13.htm) of the "Migrating from DAO to ADO" white paper contains a table that translates DAO objects and properties to ADO objects, properties, and provider-specific Jet properties. To create an easily searchable version, copy the table to the clipboard, paste it into a Word document and save the file in both .doc and .htm formats. Importing the .htm table to a Jet or SQL Server table lets you view the contents in a searchable datasheet (see Figure 30.16). Contents of the Microsoft Web site are copyrighted, so the table isn't included in this chapter's example databases.

Figure 30.16. This Jet table was created by importing a copy of the HTML table of the "DAO to ADO Cross Reference" page from the Microsoft Web site. Property or method names with number suffixes, such as DefaultType1, refer to footnotes in the source Web page.

Transaction Isolation Levels

The ability to specify the transaction isolation level applies only when you use the BeginTrans, CommitTrans, and RollbackTrans methods (see Table 30.6 later in this chapter) to perform a transaction on a Connection object. If multiple database users simultaneously execute transactions, your application should specify how it responds to other transactions in-process. Table 30.2 lists the options for the degree of your application's isolation from other simultaneous transactions.

Table 30.2. Constant Enumeration for the IsolationLevel Property

IsolationLevelEnum	Description
adXactCursorStability	Allows reading only committed changes in other transactions (default value).
adXactBrowse	Allows reading uncommitted changes in other transactions.
adXactChaos	The transaction won't overwrite changes made to transaction(s) at a higher isolation level.
adXactIsolated	All transactions are independent of (isolated from) other transactions.
adXactReadCommitted	Same as adXactCursorStability.
adXactReadUncommitted	Same as adXactBrowse.
adXactRepeatableRead	Prohibits reading changes in other transactions.
adXactSerializable	Same as adXactIsolated.
adXactUnspecified	The transaction level of the provider can't be determined.

Note

Enumeration tables in this book list the default value first, followed by the remaining constants in alphabetical order. Where two members of Table 30.2 represent the same isolation level, one of the members is included for backward compatibility.

The Connection.Mode Property

Unless you have a specific reason to specify a particular ADODB.Connection.Mode value, the default adModeUnknown is adequate. The Jet OLE DB provider defaults to adModeShareDenyNone. The Access Permissions list on the Advanced page of the Data Link properties page for SQLOLEDB is disabled, but you can set the Mode property with code. Table 30.3 lists all the constants for the Mode property.

Table 30.3. Constant Enumeration for the Mode Property

ConnectModeEnum	Description
adModeUnknown	No connection permissions have been set on the data source (default value).
adModeRead	Connect with read-only permission.
adModeReadWrite	Connect with read/write permissions.
adModeRecursive	If an adModeShareDeny... flag is specified, applies the mode to child records of a chaptered (hierarchical) Recordset object.
adoModeRecursive	Used in conjunction with the Record objects, which this chapter doesn't cover.
adModeShareDenyNone	Don't deny other users read or write access.
adModeShareDenyRead	Deny others permission to open a read connection to the data source.
adModeShareDenyWrite	Deny others permission to open a write connection to the data source.
adModeShareExclusive	Open the data source for exclusive use.
adModeWrite	Connect with write-only permission.

Tip

You often can improve performance of client/server decision-support applications by opening the connection as read only (adModeRead). Modifying the structure of a database with SQL's DDL usually requires exclusive access to the database (adModeShareExclusive).

The Connection.State Property

Table 30.4 lists the constants that return the state of the Connection object. These constants also are applicable to the State property of the Command and Recordset objects.

It's common to open and close connections as needed to reduce the connection load on the database. (Each open connection to a client/server database consumes a block of memory.) In many cases, you must test whether the Connection object is open or closed before applying the Close or Open method, or changing Connection property values, which are read-only when the connection is open.

Table 30.4. Constant Enumeration for the State Property

ObjectStateEnum	Description
adStateClosed	The Connection (or other object) is closed (default value).
adStateConnecting	A connection to the data source is in progress.
adStateExecuting	The Execute method of a Connection or Command object has been called.
adStateFetching	Rows are returning to a Recordset object.
adStateOpen	The Connection (or other object) is open (active).

Errors Collection and Error Objects

Figure 30.17 illustrates the relationship between top-level ADO components and their collections. The dependent Errors collection is a property of the Connection object, and if errors are encountered with any operation on the connection, contains one or more Error objects. The Errors collection has one property, Count, which you test to determine whether an error has occurred after executing a method call on Connection and Recordset objects. A collection is required, because it's possible for an object to generate several errors.

Figure 30.17. The Connection, Command, and Recordset objects have Properties and Errors collections. The Command object also has a Parameters collection and the Recordset object has a Fields Collection. The new Record object isn't included in this diagram.

The Errors collection has two methods, Clear and Item. The Clear method deletes all current Error objects in the collection, resetting the value of Count to 0. The Item method, which is the default method of the Errors and other collections, returns an object reference (pointer) to an Error object. The syntax for explicit and default use of the Item method is

 Set errName = cnnName.Errors.Index({strName|intIndex}) Set errName = cnnName.Errors({strName|intIndex}) 

The Error object has the seven read-only properties listed in Table 30.5. Error objects have no methods or events. The InfoMessage event of the Connection object, described in the "Connection Events" section later in this chapter, fires when an Error object is added to the Errors collection and supplies a pointer to the newly added Error object.

Table 30.5. Property Names and Descriptions of the Error Object

Property Name	Description
Description	A String value containing a brief text description of the error
HelpContext	A Long value specifying the error's context ID in a Windows Help file
HelpFile	A String value specifying the full path to and name of the Windows Help file, usually for the data provider
NativeError	A Long value specifying a provider-specific error code
Number	A Long value specifying the number assigned to the error by the provider or data source
Source	A String value containing the name of the object that generated the error, ADODB. ObjectName for ADO errors
SQLState	A String value (SQLSTATE) containing a five-letter code specified by the ANSI/ISO SQL-92 standard, consisting of two characters specifying Condition, followed by three characters for Subcondition

For the basics of error handling in VBA, see "Handling Runtime Errors," p. 1176.

Note

Unfortunately, not all RDBMS vendors implement SQLSTATE in the same way. If you test the SQLState property value, make sure to follow the vendor-specific specifications for Condition and Subcondition values.

Listing 30.1 is an example of code to open a Connection (cnnNwind) and a Recordset (rstCusts) with conventional error handling; rstCusts supplies the Recordset property of the form. The "Non-existent" table name generates a "Syntax error in FROM clause" error in the Immediate window. The Set ObjectName = Nothing statements in the error handler recover the memory consumed by the objects.

Listing 30.1 VBA Code That Writes Error Properties to the Immediate Window

 Private Sub Form_Load    Dim cnnNwind As New ADODB.Connection    Dim rstCusts As New ADODB.Recordset    On Error GoTo CatchErrors    cnnNwind.Provider = "Microsoft.Jet.OLEDB.4.0"    cnnNwind.Open CurrentProject.Path & "\Northwind.mdb", "Admin"    With rstCusts       Set .ActiveConnection = cnnNwind       .CursorType = adOpenKeyset       .LockType = adLockBatchOptimistic       .Open "SELECT * FROM Non-existent"    End With    Set Me.Recordset = rstCusts    Exit Sub CatchErrors:    Dim colErrors As Errors    Dim errNwind As Error    Set colErrors = cnnNwind.Errors    For Each errNwind In colErrors       Debug.Print "Description: " & errNwind.Description       Debug.Print "Native Error: " & errNwind.NativeError; ""       Debug.Print "SQL State: " & errNwind.SQLState       Debug.Print vbCrLf    Next errNwind    Set colErrors = Nothing    Set errNwind = Nothing    Set rstCusts = Nothing    Set cnnNwind = Nothing    Exit Sub End Sub 

Note

The frmErrors form of ADOTest.mdb and ADOTest.adp incorporates the preceding code. Open the form to execute the code, change to Design view, open the VBA editor, and press Ctrl+G to read the error message in the Immediate window.

Connection Methods

Table 30.6 lists the methods of the ADODB.Connection object. Only the Execute, Open, and OpenSchema methods accept argument values. The OpenSchema method is of interest primarily for creating database diagrams, data transformation for data warehouses and marts, and online analytical processing (OLAP) applications.

Table 30.6. Methods of the ADODB.Connection Object

Method	Description
BeginTrans	Initiates a transaction; must be followed by CommitTrans and/or RollbackTrans.
Close	Closes the connection.
CommitTrans	Commits a transaction, making changes to the data source permanent. (Requires a prior call to the BeginTrans method.)
Execute	Returns a forward-only Recordset object from a SELECT SQL statement. Also used to execute statements that don't return Recordset objects, such as INSERT, UPDATE, and DELETE queries or DDL statements. You use this method to execute T-SQL stored procedures, regardless of whether they return a Recordset.
Open	Opens a connection based on a connection string.
OpenSchema	Returns a Recordset object that provides information on the structure of the data source, called metadata.
RollbackTrans	Cancels a transaction, reversing any temporary changes made to the data source. (Requires a prior call to the BeginTrans method.)

The Connection.Open and Connection.OpenSchema Methods

You must open a connection before you can execute a statement on it. The syntax of the Open method is

 cnnName.Open [strConnect[, strUID[, strPwd, lngOptions]]]] 

Alternatively, you can assign the connection string values to the Connection object's Provider and ConnectionString properties. The following example, similar to that for the Recordset object examples early in the chapter, is for a connection to Northwind.mdb in the same folder as the application .mdb:

 With cnnNwind    .Provider = "Microsoft.Jet.OLEDB.4.0"    .ConnectionString = CurrentProject.Path & "\Northwind.mdb"    .Open End With 

In this case, all the information required to open a connection to Northwind.mdb is provided as property values, so the Open method needs no argument values.

If you're creating a data dictionary or designing a generic query processor for a client/server RDBMS, the OpenSchema method is likely to be of interest to you. Otherwise, you might want to skip the details of the OpenSchema method, which is included here for completeness. Schema information is called metadata, data that describes the structure of data.

Tip

ADOX 2.7 defines a Catalog object for Jet 4.0 databases that's more useful for Jet databases than the generic OpenSchema method, which is intended primarily for use with client/server RDBMs. The Catalog object includes Groups, Users, Tables, Views, and Procedures collections.

The Connection.Execute Method

The syntax of the Connection.Execute method to return a reference to a forward-only ADODB.Recordset object is

 Set rstName = cnnName.Execute (strCommand, [lngRowsAffected[, lngOptions]]) 

Alternatively, you can use named arguments for all ADO methods. Named arguments, however, require considerably more typing than conventional comma-separated argument syntax. The named argument equivalent of the preceding Set statement is

 Set rstName = cnnName.Execute (Command:=strCommand, _    RowsAffected:=lngRowsAffected, Options:=lngOptions) 

If strCommand doesn't return a Recordset, the syntax is

 cnnName.Execute strCommand, [lngRowsAffected[, lngOptions]] 

The value of strCommand can be an SQL statement, a table name, the name of a stored procedure, or an arbitrary text string acceptable to the data provider.

Tip

For best performance, specify a value for the lngOptions argument (see Table 30.7) so the provider doesn't need to interpret the statement to determine its type. The optional lngRowsAffected argument returns the number of rows affected by an INSERT, UPDATE, or DELETE query; these types of queries return a closed Recordset object. A SELECT query returns 0 to lngRowsAffected and an open, forward-only Recordset with 0 or more rows. The value of lngRowsAffected is 0 for T-SQL updates queries and stored procedures that include the SET NOCOUNT ON statement.

Table 30.7. Constant Enumeration for the lngOptions Argument of the Execute Method for Connection and Command Objects

CommandTypeEnum	Description
adCmdUnknown	The type of command isn't specified (default). The data provider determines the syntax of the command.
adCmdFile	The command is the name of a file in a format appropriate to the object type.
adCmdStoredProc	The command is the name of a stored procedure.
adCmdTable	The command is a table name, which generates an internal SELECT * FROM TableName query.
adCmdTableDirect	The command is a table name, which retrieves rows directly from the table
adCmdText	The command is an SQL statement.

Forward-only Recordset objects, created by what's called a firehose cursor, provide the best performance and minimum network traffic in a client/server environment. However, forward-only Recordsets are limited to manipulation by VBA code. If you set the RecordSource property of a form to a forward-only Recordset, controls on the form don't display field values.

Connection Events

Events are useful for trapping errors, eliminating the need to poll the values of properties, such as State, and performing asynchronous database operations. To expose the ADODB.Connection events to your application, you must use the WithEvents reserved word (without New) to declare the ADODB.Connection object in the Declarations section of a class or form module and then use a Set statement with New to create an instance of the object, as shown in the following example:

 Private WithEvents cnnName As ADODB.Connection Private Sub Form_Load    Set cnnName = New ADODB.Connection    ...    Code using the Connection object    ...    cnnName.Close End Sub 

The preceding syntax is required for most Automation objects that source (expose) events. Event-handling subprocedures for Automation events often are called event sinks. Source and sink terminology derives from the early days of transistors; the source (emitter) supplies electrons and the sink (collector) accumulates electrons.

Table 30.8 lists the events that appear in the Procedures list of the code-editing window for the cnnName Connection object and gives a description of when the events fire.

Table 30.8. Events Fired by the ADODB.Connection Object

Event Name	When Fired
BeginTransComplete	After the BeginTrans method executes
CommitTransComplete	After the CommitTrans method executes
ConnectComplete	After a Connection to the data source succeeds
Disconnect	After a Connection is closed
ExecuteComplete	On completion of the Connection.Execute or Command.Execute method call
InfoMessage	When an Error object is added to the ADODB. Connection.Errors collection
RollbackTransComplete	After the RollbackTrans method executes
WillConnect	On calling the Connection.Open method but before the connection is made
WillExecute	On calling the Connection.Execute or Command.Execute method, just before the command executes a connection

Tip

Take full advantage of ADO events in your VBA data-handling code. Relatively few developers currently use event-handling code in ordinary database front ends. ADO's event model will be of primary interest to developers migrating from Access 97's RDO to ADO. Developers of data warehousing and OLAP applications, which often involve very long-running queries, are most likely to use events in conjunction with asynchronous query operations.