ADO Connection Object Functions and Methods

[Previous] [Next]

Now let's examine each of the functions and methods exposed by the Connection object and listed in this next table.

Connection Object Functions and Methods
Function or Method Name Description
BeginTrans Initiates a transaction
Cancel Cancels an asynchronous attempt to connect to your database
Close Closes the connection to your database
CommitTrans Commits the current transaction
Execute Submits a query to your database
Open Opens a connection to your database
OpenSchema Retrieves schema information from your database
RollbackTrans Rolls back the current transaction

BeginTrans Method

The BeginTrans method initiates a transaction on your database. Actually, BeginTrans is a function that returns the nesting level of the transaction, but don't let the return value fool you. Currently, you can't nest ADO transactions. If you call BeginTrans more than once, you'll receive an error stating that only one transaction can be active on this session. BeginTrans takes no parameters.

The "Managing Your Transactions" section of this chapter provides more detailed information about this method.

Cancel Method

The Cancel method is intended to cancel an asynchronous query or an asynchronous attempt to connect to your database. Cancel takes no parameters.

Close Method

You can use the Close method to close an open connection. You might want to check the State property in your code before calling this method, to prevent generating an error. Calling the Close method on a Connection object that's already closed will generate an error. Close takes no parameters.

CommitTrans Method

The CommitTrans method commits the pending changes in the current transaction. CommitTrans takes no parameters. To learn more about using this method, see the "Managing YourTransactions" section later in this chapter.

Execute Method

You can use the Execute method to submit a query to your database. This method has three parameters, as shown here:

 Connection.Execute CommandText, RecordsAffected, Options 

The first parameter, CommandText, is a string that specifies the query you want to submit. This is the only parameter of the Execute method that is required. Here's an example:

 cnDatabase.Execute "DELETE FROM MyTable WHERE ID = 7" 

RecordsAffected, the second parameter, is an optional output parameter that you can use to determine how many rows were affected by your query. This parameter returns a value of type long and can come in handy if you're submitting action queries to update data:

 strSQL = "DELETE FROM MyTable WHERE ID = 7" cnDatabase.Execute strSQL, lngRecordsAffected MsgBox lngRecordsAffected & " record(s) deleted" 

The last parameter is the Options parameter, which also is optional and takes a long value. You can use this parameter with a value from CommandTypeEnum or one of the ExecuteOptionEnum values listed in the following table. For more information on specifying the command type and on the possible values for CommandTypeEnum, see the documentation on the CommandType property of the Recordset and Command objects in Chapter 4 and Chapter 5, respectively.

ExecuteOptionEnum Values
Constant Value Description
adAsyncExecute 16 Executes the query asynchronously
adAsyncFetch 32 Fetches the results of the query asynchronously
adAsyncFetchNonBlocking 64 Fetches the results of the query asynchronously without blocking
adExecuteNoRecords 128 Specifies that the query does not return records

Perhaps you're calling a stored procedure that will take a while to execute, or perhaps your query requires a great deal of processing by the server before it returns results. In that case, you can execute your query asynchronously by using the adAsyncExecute constant, and the rest of your code will continue to execute while ADO waits for the results of your query. (To determine when your query has completed, use the ExecuteComplete event on the Connection object.)

If you're using a client-side Recordset, you can use the adAsyncFetch constant to fetch the results of your query asynchronously. Once you submit your query and ADO has retrieved the number of rows specified in the Recordset object's CacheSize property, the adAsyncFetch constant specifies that your code continue to execute while ADO retrieves the rest of the results of the query asynchronously.

By default, the Execute method on the Connection object returns a Recordset object. If you use code such as the following to try to create an updatable Recordset, you will still have a read-only Recordset once the Execute method has completed:

 'Create a new Recordset. Set rsResults = New ADODB.Recordset 'Set the Recordset to use a keyset cursor and optimistic locking. 'These settings make the Recordset updatable. rsResults.CursorType = adOpenKeyset rsResults.LockType = adLockOptimistic 'Call Connection.Execute and retrieve a new, nonupdatable ' Recordset object. Set rsResults = cnDatabase.Execute(strSQL) 

The Recordset object is a return value, not an output parameter. The rsResults object variable in this example is not passed into the Execute method; the Execute method generates an entirely new Recordset object and returns it. This new Recordset object overwrites the old Recordset object referenced by the rsResults object variable.

NOTE
While the fact that the Recordset object generated by the Execute method will overwrite the existing rsResults object variable might be obvious to many programmers, one of the most commonly asked questions on the external ADO newsgroups is, "Why is the setting I've assigned to the LockType/CursorLocation/MaxRecords property ignored when I call the Execute method on the Connection and Command objects?"

The Recordset object returned by the Execute method inherits the default cursor type from the Connection object, based on the value of the Connection object's CursorLocation property. Because all client-side Recordset objects are static, if you use the Execute method with a Connection object whose CursorLocation property is set to adUseClient, you'll generate a static Recordset. If your Connection object's CursorLocation property is set to adUseServer (the default), the cursor type of the Recordset object returned from the call to the Execute method will be forward-only.

In short, if you want to maintain any control over the Recordset object generated by your query, use the Open method on the Recordset object rather than the Execute method of the Connection object. (See Chapter 4 for more information on using the Recordset's Open method.)

The best use of the Execute method is for action queries—queries that will not return a recordset. Just be sure to use the adExecuteNoRecords constant in the Options parameter. This will speed up the execution of your query because ADO will not attempt to fetch the query's results or generate a new Recordset object.

Another tip to improve performance is to use the appropriate value for the CommandTypeEnum in the Options parameter rather than letting ADO guess what type of query you're submitting.

Open Method

You use the Open method to connect to your database. The Open method on the Connection object has four parameters, all of which are optional:

 Connection.Open ConnectionString, UserID, Password, Options 

In the Open method, you can specify a connection string rather than setting the ConnectionString property prior to calling the method. The UserID and Password parameters are fairly self-explanatory; they accept string values that allow you to specify the user and password, respectively.

If you want to connect to your database asynchronously, simply use the adAsyncConnect constant (a member of the ConnectOptionEnum type) in the Options parameter. Your code will continue to run while ADO attempts to connect to your database. Once the attempt to connect completes (whether it succeeds or fails), the ConnectComplete event on the Connection object will fire. Not all OLE DB providers support asynchronous operations.

NOTE
I've noticed somewhat unexpected behavior when using the SQL Server OLE DB provider to connect asynchronously. The provider will try to locate the server synchronously. Once the provider locates the server, it will try to establish a connection asynchronously. Thus, the call to the Open method will not appear asynchronous if the server you're trying to connect to is unavailable.

If you're connecting to your database asynchronously, you might want to disable some of your application's functionality while you attempt the connection. For example, you would not want to try to submit a query to a Connection object that's still trying to connect. When the ConnectComplete event fires, you can determine whether the connection succeeded and enable the functionality in your application to submit the query, if appropriate.

OpenSchema Method

Perhaps your application allows the user to build queries at run time with a slick user interface. In that case, your application might need to retrieve metadata from your database. You'll want to provide a list of tables and the names of the fields in each table. You might even want to retrieve foreign key constraints to show relationships within the database. The OpenSchema method returns a Recordset object to help you retrieve this type of information:

 Set Recordset = Connection.OpenSchema QueryType, Criteria, SchemaID 

To use this method, you must specify a schema type in the QueryType parameter. Numerous values are available for the schema type and are contained in SchemaEnum. See the ADO Help files for a complete list of values. (Keep in mind that data access providers are not required to support all schemas.)

Generally, you want to put some restrictions on the schema that you're retrieving. For example, you might want to see the list of columns in a particular table. Simply specifying adSchemaColumns in the first parameter will retrieve information for all columns in all tables. The second parameter on the OpenSchema method, Criteria, is optional and takes a Variant array that defines the restriction you want to place on the schema. Each schema type has its own options for restrictions, and there are too many to list here. Your best bet is to look in the Microsoft Data Access SDK for the restrictions available for any particular schema type.

The current documentation for adSchemaColumns lists four available restrictions: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME. If you wanted to retrieve the columns for only the Customers table in your database, you could use code such as the following:

 Dim rsSchema As ADODB.Recordset Dim aRestrictions As Variant aRestrictions = Array(Empty, Empty, "Customers", Empty) Set rsSchema = cnDatabase.OpenSchema(adSchemaColumns, _ aRestrictions) 

We created a Variant array with the same number of entries as the number of available restrictions for the schema. We used the desired entry in the Variant array and designated the other entries as Empty.

The OpenSchema method returns a Recordset object with the schema information. If you want to be able to sort or scroll through the resulting Recordset, set the CursorLocation property of the Connection object to adUseClient prior to calling OpenSchema.

The OpenSchema method has another optional parameter—SchemaID. The documentation states that you can set this parameter to a schema's globally unique identifier (GUID) if you've set the QueryType to adSchemaProviderSpecific.

RollbackTrans Method

This method rolls back the pending changes in the current transaction. RollbackTrans takes no parameters. To learn more about using this method, see the "Managing Your Transactions" section.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net