The Connection Object

The Connection object is what connects the consumer to the provider; it’s the link between the program and the data. As you’ve already seen, the flat model of ADO means that Connection objects don’t need to be created explicitly. Instead, you can pass a connection string directly to a Command or Recordset object, and ADO will create the Connection object for you. However, explicitly creating a Connection object is worthwhile if you are planning to retrieve data from the data source more than once, because you won’t have to establish a fresh connection each time.

In the newsgroups during the early days of ADO, there was a lot of talk about connection pooling and whether the benefits really are worthwhile. One of the most time-consuming operations you can perform is the act of connecting to a data store, so anything that speeds this up is defined as "a good thing."

Connection pooling means that ADO will not actually destroy Connection objects unless it really needs to. The signal for destroying a Connection object is dictated by a timeout value; if the connection hasn’t been reused within the allotted time, it is destroyed. Here’s how it works. Suppose you open a connection, perform some data access, and close the connection. From your point of view, the connection is closed. But underneath, OLE DB keeps the connection in a pool, ready for it to be used again. If you decide that you need to open a connection to the same data store again, you will be given a Connection object from the pool of connections, and ADO doesn’t have to perform all of the expensive data store stuff again. You may not necessarily get the exact same connection object back, but you’ll get one that matches the same connection details that you previously used. In fact, existing objects will be given to anyone who requests them, so connection pooling is even more effective in a multi-user system.

One important point to note about connection pooling is that connections will be reused only if they match the exact connection details. So on multi-user systems, if you specify the same data store but use different user names and passwords, you will create a new connection rather than having one reused from the pool. This may seem like a disadvantage, but pooling must be done this way to avoid breaking security; it just wouldn’t be right to reuse a connection based on different user details. You could, of course, create a generic user and perform all of your data access through this user, thus maximizing the use of pooling. Under version 2.8 an access token check is performed before the SID when creating pooled sessions. This ensures that delegated credentials are used correctly.

You should also realize that connection pooling is not the same as connection sharing. An individual connection is not shared among multiple connection requests. Pooling means that closed connections are reused; open connections are not.

You can test to see whether connection pooling really is working by using a tool to monitor the active connections to a data store. For example, with Microsoft SQL Server you can use SQL Trace. Executing the following code shows only one connection being opened:

For iLoop = 1 To 5 
   objConn.Open strConn 
   objConn.Close
Next

However, if you turn off connection pooling, you’ll find that the five connections are opened and then immediately closed, one after another.

Although you probably wouldn’t want to, you can turn off connection pooling by adding the attribute OLE DB Services=-2 to the end of the connection string:

strConn = " . . .; OLE DB Services = -2"

Alternatively, you can achieve the same result by setting the Connection object’s Properties entry equal to -2 as in this example:

objConn.Properties("OLE DB Services") = -2

This property takes values from the DBPROPVAL_OS constants (see Appendix B). If you’ve already flicked to the back of the book to find the constant with a value of -2, you’ll notice there isn’t one. There is, however, a constant to turn on connection pooling, so to turn it off you must use some binary arithmetic. Take the value for turning on connection pooling and perform a logical NOT operation on it.

DBPROPVAL_OS_RESOURCEPOOLING has a value of 1, so:

DBPROPVAL_OS_RESOURCEPOOLING = 00000001
NOT DBPROPVAL_OS_RESOURCEPOOLING = 11111110

and this equals -2.

If you’re using an include file that contains these constants, you can use this format:

objConn.Properties("OLE DB Services") = _
         DBPROPVAL_OS_ENABLEALL 
         AND _ (NOT DBPROPVAL_OS_RESOURCEPOOLING)

This says we want all services enabled, apart from resource pooling.

The Properties collection is discussed in more detail in Chapter 8.

For ODBC connections, the ODBC Control Panel applet controls connection pooling. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

One important point to note about connection pooling is that the connection state is reset when the connection is returned to the pool. This means that if you have set any properties for the connection, the values of these properties will return to their default values. So, even though pooling returns the same connection to you, you cannot rely upon the connection’s previous state.

Now let’s take a look at how to work with the Connection object. The remainder of this chapter looks at the methods, properties, collections, and events that this object makes available to us. Let’s begin with the methods.

The BeginTrans method begins a new transaction.

Level = Connection.BeginTrans()

A transaction provides atomicity to a series of data changes to a recordset (or recordsets) within a connection, allowing all of the changes to take place at once, or not at all. Once a transaction has been started, any changes to a recordset attached to the Connection are cached until the transaction is either completed or abandoned. At that stage, all of the changes will be either written to the underlying data store (if the transaction is committed) or discarded (if the transaction is aborted).

The return value indicates the level of nested transactions. This will be 1 for a top-level transaction and will be incremented by 1 for each subsequent contained transaction. You can ignore this value if you don’t need to keep track of transaction levels.

Not all providers support transactions, and calling this method against a provider that does not support transactions will generate an error. To check that transactions are supported, you can check the Transaction DDL dynamic property of the connection’s Properties collection. For example:

intSupported = objConn.Properties("Transaction DDL")
If intSupported = DBPROPVAL_TC_ALL Then
   ' transactions are fully supported
   objConn.BeginTrans
End If

DBPROPVAL_TC_ALL has a value of 8. The constants are explained in more detail in Appendix B, and the Properties collection in Appendix C.

For a good description of transactions, check out the MSDN article titled Microsoft SQL Server: An Overview of Transaction Processing Concepts and the MS DTC, available at http://msdn.microsoft.com/library/backgrnd/html/msdn_dtcwp.htm.

Nested transactions allow you to have transactions within transactions, and allow you to segment your work in a more controlled manner. For example, consider the situation shown in this diagram of nested transactions:

Transaction A starts; this is the first transaction, and no changes made within this transaction will be visible outside of the transaction (unless dirty reads are being used, which allow you to read values before a transaction is committed). Then Transaction B starts, and the nesting level is now 2. While B is running, the changes in B are not visible to Transaction A. When Transaction B finishes, it either commits or rolls back; at this stage, A can see the changes made in B, but processes outside of Transaction A cannot. The same happens with C; its changes will not be visible to A until C commits or rolls back. Once A commits, all the changes in A, B, and C are visible to other processes. Note that if A rolls back its changes, it rolls back transactions B and C, irrespective of whether they have committed or not.

It’s possible to use connection attributes (set the Attributes property to include adXactCommitRetaining) to force transactions to start automatically on commit and rollback, and this can have serious consequences when nesting transactions. This is because every time you commit or rollback a transaction, a new one is automatically started. Imagine some code like this:

objConn.BeginTrans           ' start first transaction
   objConn.BeginTrans        ' start nested transaction
         ' do some processing
      objConn.CommitTrans    ' commit nested transaction

If auto-transaction mode is in place, then as soon as this nested transaction is committed, another nested transaction is started. You don’t have a way to commit a transaction without it starting another, so you can never get back to the level 1 transaction.

You might never use nested transactions (or be able to for that matter-some providers, including the Provider for ODBC, don’t support them), but it’s good to be aware that this problem can arise.

See also the CommitTrans method and the RollbackTrans method.

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The Cancel Method

This method cancels the execution of a pending, asynchronous Execute or Open operation.

Connection.Cancel

This is particularly useful when writing applications that allow a user to specify connection details, or when users explicitly log on to a connection. If, after a certain delay, the connection has not been established, you can inform the user and offer the option of canceling the connection attempt. For example, in Visual Basic you could open the connection asynchronously and offer the user a Cancel button, which would call this method.

This method closes an open connection and any dependent objects.

Connection.Close

Closing a connection does not remove it from memory, and you can change its settings and reopen it. To free the object from memory (assuming no one else is using the object), you must set the object variable to Nothing. For example:

objConn.Close
Set objConn = Nothing

To avoid getting an error when trying to close a connection that is not open, you can check the Connection object’s State property:

If objConn.State = adStateOpen Then
   objConn.Close
End If
Set objConn = Nothing

When the connection’s Close method is called, associated recordsets and commands behave differently. Any Recordset objects associated with the connection are closed. Any Command objects associated with the connection will persist, but the ActiveConnection parameter will be cleared, thus disassociating the command from any connection.

When the Close method is called, any pending changes in Recordset objects associated with the connection will be cancelled. If you call the Close method while a transaction is in progress, an error will be generated. This is a run-time error, number 3246, saying that the Connection object cannot be explicitly closed while in a transaction. In comparison, if a Connection object falls out of scope, any incomplete transactions will be rolled back, and in this case no error is generated.

The CommitTrans Method

This method saves any pending changes and ends the current transaction.

Connection.CommitTrans

All changes made since the previous BeginTrans will be written to the data store. This only affects the most recently opened transaction, and you must resolve lower-level transactions before resolving higher-level ones. So, if you are nesting transactions, you cannot start two transactions and then Commit or Abort the outer transaction without first Committing or Aborting the inner transaction, because they refer to the same connection.

If the Connection object’s Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call.

See also the BeginTrans method and the RollbackTrans method. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The Execute Method

This method executes the query, SQL statement, stored procedure, or provider-specific text.

Set Recordset = _
     Connection.Execute(CommandText, _
                       [RecordsAffected], [Options])

Options can be one or more of the CommandTypeEnum constants:

• adCmdText, for a SQL string

• adCmdTable, for a table name, whose columns are returned via a SQL command

• adCmdTableDirect, for a table name, whose columns are all returned

• adCmdStoredProc, for a stored procedure name

• adCmdFile, for a saved recordset

• adCmdUnknown, for an unknown command type

• adCmdUnspecified, to indicate the command type is unspecified (ADO will work out the command type itself, but this will lead to poorer performance, so you should always explicitly set the command type.)

You can also add the following ExecuteOptionEnum modifiers to Options:

• adAsyncExecute, for asynchronous execution

• adAsyncFetch, for asynchronous fetching

• adAsyncFetchNonBlocking, for asynchronous fetching that does not block

• adExecuteNoRecords, for a non-row returning command

More details of these options appear under the CommandType property in Chapter 4.

The Execute method returns a new Recordset object, even if it is not used. If the command did not return any rows, the recordset will be empty. If you specify the adExecuteNoRecords option, a null recordset is returned. The Recordset object returned is always a forward-only, read-only cursor (often called a firehose cursor). However, you can specify different cursor types by using the Open method of the Recordset object instead of the Connection’s Execute method.

You can use this method with or without some arguments, and with or without it returning a recordset. For example, to return a recordset, you could use this syntax:

strCommandText = "SELECT * FROM authors"
Set rsAuthors = objConn.Execute (strCommandText, ,_
           adCmdText)

If the statement specified in the strCommandText parameter does not return any rows, then you should include the adCmdExecuteNoRecords option; this can improve performance, because the null recordset is not automatically returned:

strCommandText = "UPDATE titles SET price = price * 1.10"
objConn.Execute strCommandText, , adCmdText + _
           adExecuteNoRecords

To supply multiple values, you add them together.

If you want to find out how many records were affected by the command, you can use the RecordsAffected argument, passing in a variable. This is especially useful for action queries, where a recordset is not returned:

strCommandText = "UPDATE titles SET Price = Price * 1.10"
objConn.Execute strCommandText, lngRecsAffected, adCmdText
Print lngRecsAffected & " were updated."

If you are using RecordsAffected and you find that it always returns -1 when you expect a different value, check if data store options are stopping the return of this information. SQL Server, for example, has a SET NOCOUNT ON statement, which stops the number of rows affected by a command from being returned. Check that this option is not set anywhere in your command (you might not realize it’s there if you are using a stored procedure), and check that it’s not set as a global database variable.

To have the command executed asynchronously, you can also add one of the asynchronous flags to the Options argument. For example:

objConn.Execute strCommandText, lngRecsAffected, _
                              adCmdText + adAsyncExecute

An ExecuteComplete event will be raised when this operation finishes. This happens even if the command is executed synchronously, but is more useful when asynchronous operations are in use. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

This method opens a connection to a data source so that commands can be executed against it.

Connection.Open([ConnectionString], [UserID], _ 
                       [Password], [Options]

Options can be one of the ConnectOptionEnum constants. At the moment, the only constants supported are adAsyncConnect (which indicates the connection should be made asynchronously) and adConnectUnspecified (the default, which indicates a synchronous connection is to be made).

The ConnectionString property takes its value from the ConnectionString argument if used.

Values passed in the UserID and Password arguments override similar values passed in the ConnectionString argument-unless the Password you supply in the argument is blank, in which case the ConnectionString password still seems to take effect.

Under version 2.8, connecting to the data source depends upon the zone in which the data source originates and whether the provider supports Integrated Security (SQL, MSDataShape, Remote, and Directory Services). If the provider supports Integrated Security and the user credentials are supplied as part of the connection string, the connection is always allowed. If the provider supports Integrated Security and the credentials aren’t supplied (i.e., the current user credentials are used), or the provider doesn’t support Integrated Security, then the connection depends upon the IE settings for user authentication and logon, which can be:

• Automatic logon with current user name and password-the connection is allowed.

• Prompt for user name and password-the connection is not allowed.

• Automatic logon only in Intranet zone-the user is prompted with a security warning.

• Anonymous logon-the connection is not allowed.

Some typical connections strings are shown in the following examples. For the ODBC Provider connecting to an Access database, you would use:

Driver={Microsoft Access Driver (*.mdb)}; DBQ=database_file

The DBQ argument points to the physical path name of the Access database. To connect to SQL Server, again using ODBC, you would use:

Driver={SQL Server}; Server=server_name;
        Database=database_name;
        UID=user_name; PWD=user_password

Switching over to the native OLE DB drivers, connecting to Access would be like this:

Provider=Micrsoft.Jet.OLEDB.4.0; Data Source=database_file

For SQL Server, using the native driver, it would be:

Provider=SQLOLEDB; Data Source= server _ name; Initial
            Catalog= database _ name; User Id= user _ name;
                              Password= user _ password

More details of the different connection strings you can use for the more common OLE DB Providers appear under the ConnectionString property and in Chapter 2. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

This method obtains database schema information from the provider.

Set Recordset = Connection.OpenSchema(Schema, _
                    [Criteria], [SchemaID])

Each value of SchemaEnum has a specific set of criteria values. This large list is included in Appendix B.

This method is most useful for obtaining table and procedure names from a data store. For example:

Set rsTables = objConn.OpenSchema(adSchemaTables)
While Not rsTables.EOF
   Debug.Print rsTables("TABLE_NAME")
   Debug.Print rsTables("TABLE_TYPE")
   rsTables.MoveNext
Wend

The various schemas are examined in detail in Appendix D.

For multidimensional (OLAP) providers using adSchemaMembers, the restrictions can be either the columns in the members schema or one of the MDTREEOP constants, as defined in Appendix L.

An alternative method of obtaining schema information is to use the ADOX library, as discussed in Chapter 10.

This method cancels any changes made during the current transaction and ends the transaction.

Connection.RollbackTrans

All changes made since the previous BeginTrans will be cancelled. This affects only the most recently opened transaction, and like CommitTrans, you must resolve lower-level transactions before resolving higher-level ones.

Also, if the Connection object’s Attributes property is set to adXactCommitAbort, the provider automatically starts a new transaction after a RollbackTrans call.

Also see the BeginTrans method and the CommitTrans method. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Properties of the Connection Object

The Attributes Property

This property indicates the transactional facilities of a Connection object.

Long = Connection.Attributes
Connection.Attributes = Long

Its value can be one or more of the adXactAttributeEnum constants:

• adXactCommitRetaining, to ensure that a new transaction is started automatically after a CommitTrans

• adXactAbortRetaining, to ensure that a new transaction is started automatically after a RollbackTrans

• A combination of both, to indicate that a new transaction is started automatically after an existing transaction is finished

Note that not all providers support this property.

In Visual Basic you can combine two or more values by ORing them together:

objConn.Attributes = adXactCommitRetaining _
                 OR adXactAbortRetaining

Beware of automatic transaction enlistment when using nested transactions, because this can lead to problems when committing the higher-level transactions. For more details on this see the Nested Transactions subsection of the discussion of the BeginTrans method, earlier in this chapter.

This property indicates how long, in seconds, to wait while executing a command before terminating the command and generating an error. The default is 30.

Long = Connection.CommandTimeout
Connection.CommandTimeout = Long

If the timeout period is reached before the command completes execution, an error is generated and the command cancelled. Setting this property to zero will force the provider to wait indefinitely.

For example, the following code will ensure that an error is generated if the command doesn’t complete within 10 seconds:

objConn.CommandTimeout = 10

Note that the Command object’s CommandTimeout property does not inherit the value set here. So the CommandTimeout property of a Connection object applies only to statements executed through the use of the Execute method.

This property contains the details used to create a connection to a data source.

String = Connection.ConnectionString
Connection.ConnectionString = String

ADO supports only the following five arguments in the connection string (all other arguments are ignored by ADO and passed directly to the provider):

• Provider= identifies the name of the provider

• File Name= identifies the name of the provider-specific file containing connection information (for example, a UDL file)

• Remote Provider= is the name of a provider that should be used when opening a client-side connection (this applies only to RDS)
  
• Remote Server= is the path name of the server that should be used when opening a client-side connection (this applies only to RDS)

• URL= is the absolute URL identifying a resource, such as a file or directory

The provider can change the connection string while the connection is being established, because it fills in some of its own details.

You cannot pass both the Provider and File Name arguments. Specifying a File Name will cause ADO to load the specified Data Link (.udl) file, which contains all of the necessary connection information. For more information about Data Link files, see Chapter 2.

Under version 2.8, reading of the ConnectionString property has the following actions:

1. If the connection is open, the connection string is obtained from the underlying OLE DB provider.

2. The dynamic property "Persist Security Info" (OLE DB property DBPROP_AUTH_PERSIST_ SENSITIVE_AUTHINFO) is checked. If true, then the password information is returned along with the connection string; otherwise, the password information is omitted.

Some examples of various connection strings are shown here. For the ODBC provider connecting to Microsoft Access:

Driver={Microsoft Access Driver (*.mdb)}; DBQ= database_name

For the ODBC provider connecting to Microsoft SQL Server:

Driver={SQL Server}; Server=server_name; _ 
          Database=database_name;
          UID=user_name; PWD=password

You can also use an existing DSN. To use a DSN called pubs, you would specify this connection string:

DSN=pubs; UID=sa; PWD=

The pros and cons of using a DSN versus a full ODBC connect string are discussed in Chapter 14.

When using the OLE DB provider for ODBC, notice that you can omit the Provider option (because it is the default). For the OLE DB provider connecting to Microsoft Access:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=database_name

For the OLE DB provider connecting to Microsoft SQL Server:

Provider=SQLOLEDB; Data Source=server_name;
     Initial Catalog=database_name; User Id=user_name;
     Password=user_password

For example, to connect to a SQL Server using the OLE DB Provider, you would do something like this:

objConn.ConnectionString = "Provider=SQLOLEDB; " & _ 
                        "Data Source=TIGGER;" & _ 
                        "Initial Catalog=pubs; " & _
                        "User Id=davids; Password=letmein"
objConn.Open

If, when you open a connection, you pass the connection details into the ConnectionString argument, then the ConnectionString property will be filled in with these details.

For more information on connection strings, refer to Chapter 2. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

This property indicates how long, in seconds, to wait while trying to establish a connection before aborting the attempt and generating an error. The default is 15 seconds.

Long = Connection.ConnectionTimeout
Connection.ConnectionTimeout = Long

If the timeout period expires before the connection opens, an error is generated and the connection cancelled. Setting this property to zero will force the provider to wait indefinitely.

You cannot set this property once the connection has been established.

This property sets or returns the location of the cursor engine.

CursorLocationEnum = Connection.CursorLocation
Connection.CursorLocation = CursorLocationEnum

You can set this property to one of the following CursorLocationEnum values:

• adUseClient, to use a client-side cursor

• adUseClientBatch, to use a client-side cursor

• adUseServer, to use a server-side cursor
  
• adUseNone, to indicate no cursor services are used (included for backward compatibility and should not be used)

A disconnected recordset can be achieved only by setting the CursorLocation property to adUseClient.

A Recordset created against the Connection object will inherit the value set here.

Changing the CursorLocation property has no effect on existing Recordsets associated with a Connection. The performance issues surrounding various cursor types are discussed in Chapter 14.

This property indicates the default database for a Connection object.

String = Connection.DefaultDatabase
Connection.DefaultDatabase = String

You can access objects in other databases by fully qualifying the objects, if the data source or provider supports this.

For example, when using Microsoft SQL Server, you can create two recordsets on different databases:

objConn.DefaultDatabase="pubs"

rsOne.Open "authors", objConn, _ 
             adOpenKeyset, adLockReadOnly, adCmdTable

rsTwo.Open "Sales.dbo.Orders", objConn, _
             adOpenKeyset, adLockReadOnly, adCmdTable

The first uses the authors table from the default pubs database, and the second uses the Orders table in the Sales database. Note that this option is not available when using the RDS client-side Connection objects.

This property indicates the level of transaction isolation for a Connection object.

IsolationLevelEnum = Connection.IsolationLevel
Connection.IsolationLevel = IsolationLevelEnum

The isolation level allows you to define how other transactions interact with yours, and whether they can see your changes and vice versa.

This value comes into effect only when you call the BeginTrans method. The provider may return the next greater level of isolation if the requested level is not available.

The value can be one of the IsolationLevelEnum constants:

• adXactUnspecified indicates that the provider is using a different isolation level to the one you specified, but it cannot determine which level.

• adXactChaos indicates that a higher-level transaction has control over the records. This means that you cannot overwrite any pending changes from another user.

• adXactBrowse or adXactReadUncommitted allows you to view uncommitted changes in another transaction. Be careful when using either of these values, because the changes in another transaction have not been committed; therefore, they could be rolled back, leaving you with invalid values.

• adXactCursorStability or adXactReadCommitted (the default) indicates that you can view changes in other transactions only after they have been committed. This guarantees the data’s state. However, new records and deleted records will be reflected in your recordset.

• adXactRepeatableRead doesn’t allow you to see changes made from other transactions unless you re-query the recordset. Once you have re-queried the recordset, you can see new records that might have been added by other users to the records that compose your recordset.

• adXactIsolated or adXactSerializable indicates that transactions are completely isolated from each other. This means that all concurrent transactions will produce the same effect as if each transaction were executed one after the other.

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The Mode Property and Provider Property

This property indicates the available permissions for modifying data in a Connection.

ConnectModeEnum = Connection.Mode
Connection.Mode = ConnectModeEnum

The value can be one of the ConnectModeEnum constants:

• adModeUnknown (default) indicates that permissions cannot be determined, or haven’t been set yet.

• adModeRead grants read-only permissions.

• adModeWrite grants write-only permissions.

• adModeReadWrite grants read/write permissions.

• adModeRecursive indicates that the adShare permissions should be applied recursively.

• adModeShareDenyRead prevents other users from opening a connection with read permissions. 
   
• adModeShareDenyWrite prevents other users from opening a connection with write permissions.

• adModeShareExclusive prevents other users from opening a connection.

• adModeShareDenyNone allows users to open a connection with any permissions and ensures that neither read nor write permissions can be denied to other users.

You can use this property to set or return the provider access permission for the current connection.

You cannot set this property on open connections, and not all providers support all options.

This property indicates the name of the provider for a Connection object.

String = Connection.Provider
Connection.Provider = String

It also can be set by the contents of the ConnectionString property.

Note that specifying the provider in more than one place can have unpredictable results. Microsoft doesn’t actually specify what "unpredictable" means in this case, but it’s probably best to set this in only one place. The Provider property must be set before trying to access any provider-specific dynamic properties; otherwise, the default OLE DB Provider for ODBC is assumed.

If no provider is specified, the default is MSDASQL, the Microsoft OLE DB Provider for ODBC. The providers supplied with MDAC 2.6 are:

• MSDASQL for ODBC

• MSIDXS for Index Server (if Index Server is installed)

• ADSDSOObject for Active Directory Services

• Microsoft.Jet.OLEDB.4.0 for Microsoft Jet databases, both Access 2000 and earlier version

• SQLOLEDB for SQL Server

• MSDAORA for Oracle

• MSDataShape for the Microsoft Data Shape with hierarchical recordsets(discussed in more detail in Chapter 11)

• MSDAIPP.DSO.1 for Internet Publishing

• MSDAOSP for developing simple OLE DB providers that expose data in simple tabular (row/column) format
  
• MSPersist for persisting or saving a Recordset to a file or object that supports the standard COM IStream interface (such as ASP’s Request or Response objects or ADO’s Stream object)

Remember that providers aren’t deleted when a later version is installed, so upgrading to 2.8 will still show the providers from an earlier version.

The various Provider and ConnectionString options are discussed in more detail under the ConnectionString property, and in Chapter 2. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The State Property and Version Property

This property describes whether the Connection object is open or closed. For asynchronous connections it also indicates whether the state is connecting, opening, or retrieving.

Long = Connection.State

This will be one of the following ObjectStateEnum constants:

• adStateOpen for an open connection

• adStateClosed for a closed connection

• adStateConnecting for an asynchronous connection still connecting to a provider

• adStateExecuting for an asynchronous connection executing a command

• adStateFetching for an asynchronous connection fetching data

You can use the State property to ensure that you don’t generate errors when closing a connection. For example, to ensure that a transaction is committed:

If objConn.State = adStateOpen Then
   objConn.CommitTrans
   objConn.Close
End If

The Version Property

This property indicates the version number of the MDAC components.

String = Connection.Version

Note that the provider’s version number can be obtained from the Connection object’s Properties collection.

This property is read-only. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Events of the Connection Object

Connection events can be quickly summarized as follows:

• BeginTransComplete is raised after a transaction has begun.

• CommitTransComplete is raised after a transaction has been committed.

• ConnectComplete is raised after the connection has been established.

• Disconnect is raised after a connection has been closed.

• ExecuteComplete is raised after an Execute method call has completed.

• InfoMessage is raised when the provider returns extra information.

• RollbackTransComplete is raised after a transaction has been rolled back.

• WillConnect is raised just before the connection is established.

• WillExecute is raised just before a statement is executed.

These events work in both synchronous and asynchronous modes.

All events will have a bidirectional parameter, adStatus, to indicate the status of the event. This is of type EventStatusEnum, and on entry to the procedure can be one of the following constants:

• adStatusOK indicates that the action that caused the event was successful.

• adStatusErrorsOccured indicates that errors or warnings occurred, in which case the Errors collection should be checked.

• adStatusCantDeny indicates on a Will... event that you cannot cancel the action that generated the event, and on a ...Complete event indicates that the action was cancelled.

• adStatusUnwantedEvent indicates that the action that generated the event should no longer generate events.

On a Will... event (and assuming adStatusCantDeny is not set), before the procedure exits you can set adStatus to adStatusCancel to cancel the action that caused this event. This also generates an error indicating the event has been cancelled. For example, assume that you have moved from one record to the next; this will raise a WillMove event on the recordset. If you decide not to move to another record, you can set adStatus to adStatusCancel, and the Move action will be cancelled. This allows you to cancel actions where the data is incorrect.

When using Will... events, one thing to watch for is implicit method calls. Taking the preceding Move as an example, if the current record has been edited, then ADO implicitly calls Update. In this case you might get more than one Will... event. I discuss this in more detail in Chapter 5.

If you no longer wish to receive events for a particular action, then before the procedure exits, you can set adStatus to adStatusUnwantedEvent, and they will no longer be generated. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The BeginTransComplete Event

This event fires after a BeginTrans method call finishes executing.

BeginTransComplete(TransactionLevel, pError, _
                               adStatus, pConnection) 

You can use this to trigger other operations dependent upon the transaction having been started. For example, you might like to build a transaction monitoring system, and you could log the start of the transaction in this event.

The CommitTransComplete Event

This event fires after a CommitTrans method call finishes executing.

CommitTransComplete(pError, adStatus, pConnection)

You can use this to trigger other operations that are dependent upon the transaction having been completed successfully, such as updating log files or an audit trail.

The ConnectComplete Event

This event fires after a connection is established.

ConnectComplete(pError, adStatus, pConnection)

You can use the ConnectComplete event to examine the details of the connection and whether it completed successfully. For example, the following Visual Basic example shows how you could use the ConnectComplete event:

Private Sub objConn_ConnectComplete( _ 
             ByVal pError As ADODB.Error, _
             adStatus As ADODB.EventStatusEnum, _
             ByVal pConnection As ADODB.Connection)

   Select Case adStatus
   Case adStatusErrorsOccurred
      Print "Errors occurred whilst attempting to connect."
      Print "Connection String is: " & _ 
             pConnection.ConnectionString
      Print "Error description: " & pError.Description
   Case adStatusOK
      Print "Connection successful."
   End Select
End Sub

The nonhighlighted code is the definition of the event procedure and is automatically created by Visual Basic. (For more on using events with Visual Basic, see Chapter 2.) 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The Disconnect Event

This event fires after a connection is closed.

Disconnect(adStatus, pConnection) 

You can also use it to track users as they log on to and off from data sources. It can also be useful to alert users when a connection drops unexpectedly.

The ExecuteComplete Event

This event fires after a statement has finished executing.

ExecuteComplete(RecordsAffected, pError, _
              adStatus, pCommand, _
              pRecordset, pConnection)

This allows you to examine whether the command completed successfully and how many records it affected. You can use this instead of the Execute method’s RecordsAffected argument. The following Visual Basic code shows the use of this event:

Private Sub objConn_ExecuteComplete( _
        ByVal RecordsAffected As_Long, _
        ByVal pError As ADODB.Error, _
        ByVal adStatus As ADODB.EventStatusEnum, _
        ByVal pCommand As ADODB.Command, _
        ByVal pRecordset As ADODB.Recordset, _
        ByVal pConnection As ADODB.Connection)

The nonhighlighted code is the Visual Basic generated event procedure.

The InfoMessage Event

This event fires whenever a connection event operation completes successfully and the provider returns additional information, such as a warning.

InfoMessage(pError, adStatus, pConnection)

The parameters define what type of information message this is. This is particularly useful when dealing with ODBC data sources, especially to SQL Server, because it returns informational messages that could be logged in an audit trail.

For example, you could connect to the pubs database on SQL server with this connect string:

Driver={SQL Server}; Server=Tigger; Database=pubs; _
              UID=sa; PWD=

and then put this code into the InfoMessage event procedure:

Private Sub oConn_InfoMessage(ByVal pError As _
              ADODB.Error, _ 
              adStatus As ADODB.EventStatusEnum, _
              ByVal pConnection As ADODB.Connection)
   Dim objError As ADODB.Error

   Debug.Print pError.Description

   For Each objError In pConnection.Errors
      Debug.Print vbtab; objError.Description
   Next
End Sub

On my server, the above code generates the following warnings when connecting to the pubs database in SQL Server using the OLE DB Provider for ODBC:

[Microsoft][ODBC SQL Server Driver]
   [SQL Server]Changed database context to 'master'.
[Microsoft][ODBC SQL Server Driver]
   [SQL Server]Changed database context to 'master'.
[Microsoft][ODBC SQL Server Driver]
   [SQL Server]Changed language setting to 'us_english'.
[Microsoft][ODBC SQL Server Driver]
   [SQL Server]Changed database context to 'pubs'.

In general, this event can be used to track connection messages or actions on the connection that return the ODBC SQL_SUCCESS_WITH_INFO result. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

The RollbackTransComplete Event

This event fires after a RollbackTrans method call has finished executing.

RollbackTransComplete(pError, adStatus, pConnection)

You can use this event to trigger other operations that depend upon the transaction having failed, such as writing to log files. For example:

Private Sub objConn_RollbackTransComplete( _ 
             ByVal pError As ADODB.Error, _
             adStatus As ADODB.EventStatusEnum, _
             ByVal pConnection As ADODB.Connection)

Print "Transaction was rolled back. " & _ 
      "Changes have not been saved."

End Sub

This could be extremely useful in nightly batch jobs.

The WillConnect Event

This event fires before a connection is opened, indicating that the connection is about to be established.

WillConnect(ConnectionString, UserID, Password, _ 
            Options, adStatus, pConnection)

The parameters supplied can be changed before the method returns-for instance if the user has specified certain connection attributes, but you wish to change them. As an example, imagine an application that allowed the user to specify connection details. You could prevent them from connecting as a certain user, but allow the connection to be established as another:

Private Sub objConn_WillConnect(ConnectionString As _
          String, UserID As String, Password As String, _
          Options As Long, _
          adStatus As ADODB.EventStatusEnum, _ 
          ByVal pConnection As ADODB.Connection)

   If adStatus = adStatusOK Then
      Select Case UserID
      Case "sa"
         Print "Connection as system " & _
                "administrator not allowed."
         adStatus = adStatusCancel 
      Case "Guest"
         UserID = "GuestUser"
         Password = "GuestPassword"
      End Select
   End If

End Sub

This stops the user trying to connect as sa and cancels the connection attempt. If a user tries to connect as Guest, then the user ID is changed to GuestUser and the connection proceeds. This allows you to have a set of real user details that are hidden, while exposing a viewable set of user details.

The WillExecute Event

This event fires before a pending command executes on the connection.

WillExecute(Source, CursorType, LockType, _ 
       Options, adStatus, pCommand, _
       pRecordset, pConnection) 

The execution parameters can be modified in this procedure, because it is called before the command executes.

This is particularly useful when building user-query type applications where the user has the ability to set details of the connection, because it allows you to examine the parameters and modify them if necessary. For example:

Private Sub objConn_WillExecute(Source As String, _ 
     CursorType As ADODB.CursorTypeEnum, _ 
     LockType As ADODB.LockTypeEnum, _ 
     Options As Long, adStatus As ADODB.EventStatusEnum, _
     ByVal pCommand As ADODB.Command, _
     ByVal pRecordset As ADODB.Recordset, _
     ByVal pConnection As ADODB.Connection)

   If Source = "SalaryDetails" Then
      Print " Nice try, but you're not " & _
            "allowed to look at these"
      adStatus = adStatusCancel
   End If

End Sub

The preceding code cancels the event if someone tries to connect to the SalaryDetails table.

You can also use this technique to protect against ad hoc insertions and deletions, and it is an easy way to build business logic into the connection. A better way to protect against this sort of amendment to data or tables is to implement proper security and to use a three-tier business model, where data access is only possible through controlled operations. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Collections of the Connection Object

The collections are discussed in depth in Chapter 8.

The Errors Collection

This collection contains all Error objects created in response to a single failure involving the provider.

Connection.Errors

The Errors collection is only cleared by ADO when another ADO operation generates an error.

Note that along with errors, the Errors collection often contains warnings and other informational messages from the provider. For this reason, it’s a good idea to use the Errors collection’s Clear method before certain operations; this ensures that warnings are relevant to the most recent operation. In particular, you should use the technique on a Recordset object’s Resync, UpdateBatch, and CancelBatch methods, or on its Filter property. On a Connection object, use this technique for the Open method. Explicitly clearing the errors allows you to use the Count property of the collection to quickly identify whether errors have occurred.

The Properties Collection

This collection contains all Property objects for a Connection object.

Properties = Connection.Properties

The Properties collection is discussed in more detail in Chapter 8, and a full list of the available properties is in Appendix C. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.