Jim Lewallen
Program Manager
Microsoft Data Access Group
July 1998
Summary: Describes the new functionality of ActiveX Data Objects (ADO) 2.0. (18 printed pages) Covers updates to ADO and descriptions of new features, including:
The Microsoft® Universal Data Access strategy is based on data sources exposing their data through OLE DB providers and applications using Microsoft ActiveX® Data Objects (ADO) or OLE DB to consume the data in a uniform way. ADO is a high-level set of objects developers can use to access OLE DB data from a wide variety of languages and products, including the tools in Microsoft Visual Studio™, Microsoft Office, Microsoft Visual Basic® Scripting Edition (VBScript) in Microsoft Internet Information Server (IIS) and Microsoft Internet Explorer, and other Automation-compatible languages.
ADO 2.0, in conjunction with the OLE DB service components and Remote Data Service (RDS) 2.0, has been enhanced to provide a rich set of functionality with which to build applications. This article introduces these new features and provides some examples of their use.
ADO 2.0 is the third release of ADO.
The first release exposed basic client/server functionality. It introduced developers to the world of OLE DB through the OLE DB Provider for ODBC. The first release was a subset of the features exposed by Remote Data Objects (RDO) 2.0 and was targeted at customers programming from Active Server Pages (ASP).
ADO 1.5, the next major release—which shipped with IIS 4.0 and Internet Explorer 4.0—focused on integration of RDS. It added some of the features that developers missed from RDO 2.0. New features in ADO 1.5 included disconnected recordsets, remoting, schema queries, and commands as methods of the connection object. This was the first release of ADO to be included in the Microsoft Data Access Components (MDAC), which is a package of components developed, tested, and released together to guarantee compatibility and stability for the user.
ADO 2.0 is the topic of this article. The feature set of ADO 2.0 is designed to provide for the data access needs of the developer using Visual Studio. ADO 2.0 implements or exposes the most interesting features of RDO 2.0, and adds some features that users have not seen in the past. Visual Studio has also been extended to embrace ADO and OLE DB: Data binding, visual design tools, OLE DB provider writer templates, and more are included in this release.
When describing the features of ADO, it is interesting to know that ADO itself does not implement some of the features. Some are implemented by specific OLE DB providers, the OLE DB Service Components, or RDS. Most of the features seen here are actually available to OLE DB programmers as well. This article's purpose, however, is to describe the new functionality made available to ADO programmers in ADO 2.0. It does not always attempt to describe which functionality is implemented by which component.
This article describes what's available to the ADO programmer in two sections: features that were implemented in the past in RDO or Data Access Objects (DAO); and features that are new to Microsoft data access application programming interfaces (APIs).
Often operations involving a database can take some time to process a query, connect to the data source, or retrieve results to the client. RDO 2.0 provided a way to do these operations asynchronously. With this ability, during a long operation developers might also do other operations—for example, display a progress dialog box or continue painting the form, or even start other asynchronous operations. Asynchronous operations can also be cancelled. A query that takes too long can be stopped and restarted with a smaller result. A connection that takes too long can be canceled and another server tried.
Generally, in an asynchronous operation a notification to the application signals that the operation is about to be asynchronously started. Later, the application is notified that the operation is completed. A notification or event is a signal to the application that something has happened. Developers write event handlers to process the event. In ADO, the names given to the events follow a pattern: Events that happen before an operation are named "Willxxx," and after are named "xxxComplete."
All notifications are exposed on the Connection and Recordset objects. The Connection object notifications deal with connecting to the data source, executing commands, and processing transactions. The Recordset notifications are related to fetching of result sets, scrolling, changing, and updating.
You specify the start of an asynchronous connection by using the Options parameter to the Connection object Open method. ADO implements the asynchronous connection operation internally. It does not rely on support from the OLE DB provider being used. Therefore, you should always be able to asynchronously connect.
Dim conn As New ADODB.Connection
Conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open , , , adAsyncConnect
If conn.State = adStateConnecting Then
Debug.Print "Still Connecting"
In this particular sample, there is no code to determine when the connection operation has completed. Most applications require that code because most operations on the connection will fail until it has successfully connected to the server.
A new property exposed on the ADO objects that support asynchronous operations is the State property. This property can be used to determine the state of an object: whether it is closed or open (adStateClosed, adStateOpen), or what asynchronous operation is occurring (adStateConnecting, adStateExecuting, adStateFetching).
The State property is most often useful in an environment in which events are not supported: It can be used in a loop to determine when an operation has completed. If an error occurs during the asynchronous operation, testing the State property may generate an error. State was implemented in this way so that, in environments without event support, the user can in some way be notified that an error occurred during the asynchronous operation.
Asynchronous connection can be done only from the Connection object. ADO allows the user to connect to a data source directly from the Command or Recordset object, but these objects cannot be used to asynchronously connect.
Execution is another operation that can take a significant amount of time. There are two places in which ADO allows asynchronous execution: the Connection.Execute method and the Command.Execute method.
Asynchronous execution covers a particular period of time. That is, it covers the time from when the data source is requested to begin an operation to the time that the first row is available to be fetched (assuming a row-returning result).
Asking for asynchronous execution is similar to asynchronous connection. The Options parameter to ADO execute methods must include the value adAsyncExecute. Note that most options parameters take more than one value, so adAsyncExecute could be joined with another option by using an OR operation.
Dim WithEvents conn As ADODB.Connection
Sub Form_Load()
Dim cmd As New ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password="""""";Initial Catalog=pubs"
conn.Open
cmd.Execute "select * from authors", conn, adAsyncExecute
Debug.Print "Command Execution Started."
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum,
ByVal pCommand As ADODB.Command, ByVal pRecordset
As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Debug.Print "Completed Executing the Command."
End Sub
In the preceding sample, an event handler has been implemented to print to the Debug window when the command has completed.
With large result sets, fetching can take a long time. With asynchronous fetching, the user can be shown some results almost immediately, and then the rest can be retrieved in the background.
Asynchronous fetching in ADO is implemented only for client cursors (CursorLocation = adUseClient). The actual background fetching is implemented by using the RDS cursor engine.
There are two types of asynchronous fetches, blocking and nonblocking. The difference between blocking and nonblocking is in how operations that would attempt to refer to unfetched rows are handled. In the blocking case, the function being requested (for example, MoveLast) is executed. ADO waits until the last row has been made available, then returns control to the application. In a nonblocking scenario, the MoveLast operation returns immediately, but the current row is changed to the last one fetched; the user would be placed only on the last row currently fetched, not on the last row that would eventually be fetched into the resultset. Thus, the user can see that row and all of the other rows already fetched. It is a subtle distinction, and there are uses for both cases.
By default, 50 rows are fetched before control is returned to the developer. The value 50 was chosen to represent approximately what might be displayed in a grid on a form. This number can be controlled by using the Initial Fetch Size property in the Recordset Properties collection.
Asynchronous fetching can be requested only by using the Recordset object, through the Open method's Options parameter. This is demonstrated in the following code snippet:
Dim WithEvents rst As ADODB.Recordset
Sub Form_Load()
Dim conn as New ADODB.Connection
Set Rst = New ADODB.Recordset
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set rst.ActiveConnection = conn
rst.CursorLocation = adUseClient
rst.Properties("Initial Fetch Size") = 1
rst.Open "select * from authors", conn, , , adAsyncFetch
Debug.Print rst.Fields(0) & " : First field, Fetching Started"
End Sub
Private Sub rst_FetchComplete(ByVal pError As ADODB.Error,
adStatus As ADODB.EventStatusEnum,
ByVal pRecordset As ADODB.Recordset)
Debug.Print "Completed Fetching Results."
End Sub
The example uses blocking (adAsyncFetch) asynchronous fetch.
It shows the use of only two of the notifications available in ADO. There are several others; to learn more, see the ADO 2.0 documentation.
Some operations are easy for a desktop database to do, but difficult (or, at the very least, exhibit poor performance characteristics) on a database server. ADO 2.0 exposes a Sort property and a Find method. With these features, the user can do desktop-style data manipulation against any data source from within ADO.
Find and Sort are implemented to use support from the provider. If the provider does not support the proper OLE DB interfaces, ADO will attempt to carry out these operations at the client by brute force. The brute-force operations can be slow, but there is another alternative. If client cursors are being used, these operations are optimized in three ways:
To sort a recordset, the developer sets the Recordset.Sort property to the name of the column that the recordset should be sorted by, as shown in the following code:
Sub main()
Dim conn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set rst.ActiveConnection = conn
rst.CursorLocation = adUseClient
rst.Open "select * from authors", conn, adOpenStatic, _
adLockBatchOptimistic
'Print out the records in their original order
While rst.EOF <> True
Debug.print rst!au_id, rst!au_lname, rst!au_fname
Rst.MoveNext
Wend
'Print out the records ordered by author last name
rst.Sort = "au_lname"
'Note – sort automatically moves you to the first row in the
'resultset so a MoveFirst is not needed.
While rst.EOF <> True
Debug.print rst!au_id, rst!au_lname, rst!au_fname
Rst.MoveNext
Wend
End Sub
Sort can also be used to sort by multiple columns. To sort by last name then first name in the previous sample, the command is as follows:
'Print out the records ordered by author last name then first name
rst.Sort = "au_lname, au_fname"
Finally, it is potentially interesting to sort one column in ascending direction, and another in descending. To do this you specify DESC following the name to be sorted in descending direction, as follows:
'Print out the records ordered by author last name then first name,
'with the first name sorted in descending order
rst.Sort = "au_lname, au_fname DESC"
Even after narrowing the results returned from the server by using the SQL command, it is often interesting to search for a particular row in the recordset. ADO has implemented a Find method that will search a named row for a specified value.
In the Visual Basic object browser, the Find method looks like this:
Find(Criteria As String, [SkipRecords As Long],
[SearchDirection As SearchDirectionEnum = adSearchForward], [Start])
This does seem complex for just finding a record in a recordset. However, here is a description of what each parameter to the method means:
If you understand how to use these parameters, you can get exactly the behavior you want from Find.
Sub main()
Dim conn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set rst.ActiveConnection = conn
rst.CursorLocation = adUseClient
rst.Open "select * from authors", conn, adOpenStatic, _
adLockBatchOptimistic
'Search from the beginning and find an author named Ringer
rst.Find "au_lname = 'Ringer'"
'Search from the beginning and find an author named Green
rst.Find "au_lname = 'Green'", , , adBookmarkFirst
'Search from the end and find an author named Ringer
rst.Find "au_lname = 'Ringer'", , adSearchBackward, adBookmarkLast
End Sub
Find works only with a single column. It does not support searching multiple columns. If Find does not find a match, it positions you at either end-of-file (EOF), if you were searching forward, or beginning-of-file (BOF), if you were searching backward.
Resync gives the developer a way to find the current values on the server for the specified records in the recordset, without requiring a complete reexecution of the original query. The Resync method has existed in ADO since version 1.0, but did not work for client cursors in ADO 1.5. Additionally, the effects of Resync were "destructive"—that is, Resync discarded any pending changes to the resynchronized row. This was an intentional design. The original implementation did not take disconnected operation into account.
In ADO 2.0, Resync has been enhanced to support client cursors. It has been additionally enhanced to have a nondestructive option that updates just the UnderlyingValue property for each field without discarding pending changes. The nondestructive option is currently available for client cursors only. It relies on an OLE DB interface, IRowsetRefresh, which was added in OLE DB 2.0.
Sub main()
Dim conn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
conn.Execute "create table testtable (dbkey int primary key, _
field1 char(10))"
conn.Execute "insert into testtable values (1, 'string1')"
Set Rst.ActiveConnection = conn
Rst.CursorLocation = adUseClient
Rst.Open "select * from testtable", conn, adOpenStatic, _
adLockBatchOptimistic
'Create an update to the server row that the
'client cursor doesn't know about
conn.Execute "Update testtable set field1 = 'CONFLICT' _
where dbkey = 1"
'Change the row on the client
Rst!field1 = "NewValue"
'Resync the row to find out what's on the server
Rst.Resync adAffectCurrent, adResyncUnderlyingValues
Debug.Print "Current Edited Value: " & Rst!field1.Value
Debug.Print "Value Before Editing: " & Rst!field1.OriginalValue
Debug.Print "Value On the Server: " & Rst!field1.UnderlyingValue
End Sub
There can be cases in which the Resync operation generates an error. These cases arise when the client cursor cannot deduce enough information from the provider to uniquely determine how to get the new values from the server. This can also happen if the user requests a read-only client cursor. In that case, a performance optimization ADO does not ask for the full recordset metadata and is unable to perform the Resync operation.
To help ensure the operation's success, your resultset should include the primary key for the table, or a column that has a unique key and is not opened as lock type adLockReadOnly.
Session pooling is a new feature of the OLE DB service components that ADO developers can benefit from. An OLE DB session corresponds to the ADO Connection object.
Pooling was first implemented in ODBC 3.0. Session pooling is designed for the Web scenario. It is intended to improve the performance of connecting and disconnecting in large applications by keeping a pool of live connections open for a short period of time, and then reusing them for new connection requests. This is an alternative to completely reconnecting to the data source. In session pooling, the security and other properties of the connection are respected. Pooled connections are only reused if matching requests are made from each side of the connection.
The ADO developer does not have to do anything to use pooling with the OLE DB providers included in MDAC 2.0. For providers not included in MDAC 2.0, you need to create a registry setting to notify the service components that the provider can safely be pooled. For more information about session pooling and service components, see the OLE DB 2.0 Software Development Kit (SDK) documentation.
Often, a developer finds a need for a place to temporarily store some data, or wants some data to act like it came from a server so it can participate in data binding in a user interface.
ADO 2.0 (in conjunction with the RDS client cursor) enables the developer to build an empty recordset by specifying column information and calling Open. The developer now has an empty recordset to work with. The following code snippet demonstrates this:
Dim Rst As New ADODB.Recordset
Rst.CursorLocation = adUseClient
'Add Some Fields
Rst.Fields.Append "dbkey", adInteger
Rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable
Rst.Fields.Append "field2", adDate
'Create the Recordset
Rst.Open , , adOpenStatic, adLockBatchOptimistic
'Add Some Rows
Rst.AddNew Array("dbkey", "field1", "field2"), _
Array(1, "string1", Date)
Rst.AddNew Array("dbkey", "field1", "field2"), _
Array(2, "string2", #1/6/1992#)
'Look at the values - a value of 1 for status column = newly record
Rst.MoveFirst
Debug.Print "Status", "dbkey", "field1", "field2"
While Rst.EOF <> True
Debug.Print Rst.Status, Rst!dbkey, Rst!field1, Rst!field2
Rst.MoveNext
Wend
'Commit the rows without ActiveConnection set resets the status bits
Rst.UpdateBatch adAffectAll
'Change the first of the two rows
Rst.MoveFirst
Rst!field1 = "changed"
'Now look at the status, first row shows 2 (modified row),
'second shows 8 (no modifications)
'Also note that the OriginalValue property shows the value
'before the modification
Rst.MoveFirst
While Rst.EOF <> True
Debug.Print
Debug.Print Rst.Status, Rst!dbkey, Rst!field1, Rst!field2
Debug.Print , Rst!dbkey.OriginalValue, _
Rst!field1.OriginalValue, Rst!field2.OriginalValue
Rst.MoveNext
Wend
The advent of RDS remoting makes at least one interesting scenario possible: First, fabricate a recordset in a business object, and then, by remoting, return that data to the client to receive updates. The data is then remoted back to the Web server. The business object now has a recordset with all the information necessary to save the changes; that is, which rows changed, how they were changed, which rows were newly added. You can also set a Filter of adFilterPending to show deleted rows.
Another feature of a creatable recordset is that pending operations can be "committed" to the recordset. Any time UpdateBatch is called on a client cursor that has no ActiveConnection set, the changes in the affected row (controlled by the AffectedRows parameter) will be committed to the buffer and the Status flags will be reset. The same applies to CancelBatch, except the changes in the buffer will be reverted and the flag will be reset.
An additional scenario is to use the fabricated recordset as the basis for an OLE DB provider of sorts. An ADO event called EndOfRecordset has been defined to notify the developer that the consumer has reached the end of the resultset. The developer then has the option to add new rows within the event. To the consuming application, it appears that the rows have been there all along. The following code sample demonstrates this feature:
Dim WithEvents rst As ADODB.Recordset
Sub form_load()
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Fields.Append "dbkey", adInteger
rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable
rst.Fields.Append "field2", adDate
rst.Open , , adOpenStatic, adLockBatchOptimistic
'Add Some Rows
rst.AddNew Array("dbkey", "field1", "field2"), _
Array(1, "string1", Date)
rst.AddNew Array("dbkey", "field1", "field2"), _
Array(2, "string2", #1/6/1992#)
Debug.Print "Status", "dbkey", "field1", "field2"
While rst.EOF <> True
Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2
rst.MoveNext
Wend
End Sub
Private Sub rst_EndOfRecordset(fMoreData As Boolean,
adStatus As ADODB.EventStatusEnum,
ByVal pRecordset As ADODB.Recordset)
If pRecordset.RecordCount < 50 Then
StartIndex = pRecordset.RecordCount
Debug.Print "Adding Some New Data"
For ii = 1 To 10
pRecordset.AddNew Array("dbkey", "field1", "field2"), _
Array(StartIndex + ii, "string1", Date)
Next
fMoreData = True
End If
End Sub
Portable computing (such as laptops) has generated the need for applications that can run in both a connected and disconnected state. ADO has added support for doing this by giving the developer the ability to save a client cursor recordset to disk and reload it later.
There are several interesting scenarios in which to use this type of feature, including:
To save and load recordsets, use the Recordset.Save and Recordset.Open(,,,,adCmdFile) methods on the ADO Recordset object:
Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
conn.Execute "create table testtable (dbkey int _
primary key, field1 char(10))"
conn.Execute "insert into testtable values (1, 'string1')"
Set rst.ActiveConnection = conn
rst.CursorLocation = adUseClient
rst.Open "select * from testtable", conn, adOpenStatic, _
adLockBatchOptimistic
'Change the row on the client
rst!field1 = "NewValue"
'Save to a file - the .DAT extension is an example, choose
'your own extension. The changes will be saved in the file
'as well as the original data
Kill "c:\temp\temptbl.dat"
rst.Save "c:\temp\temptbl.dat", adPersistADTG
Set rst = Nothing
'Now reload the data from the file
Set rst = New ADODB.Recordset
rst.Open "c:\temp\temptbl.dat", , adOpenStatic, _
adLockBatchOptimistic, adCmdFile
Debug.Print "After Loading the file from disk"
Debug.Print " Current Edited Value: " & rst!field1.Value
Debug.Print " Value Before Editing: " & rst!field1.OriginalValue
'Note that you can reconnect to a connection and
'submit the changes to the data source
Set rst.ActiveConnection = conn
rst.UpdateBatch
End Sub
Data shaping (otherwise known as hierarchical recordsets) is interesting and complex enough to be the topic of an entire article by itself. This article will cover it only briefly to describe the technology and its uses, and to provide some samples for those who want to experiment.
The idea behind data shaping is relatively simple: to model a one-to-many or many-to-many relationship between recordsets, and to provide grouping and aggregation over recordsets. For example, you can build a relationship between a customer's recordset and an orders recordset on the client workstation so that, for each customer record, a related set of orders records is visible in the orders recordset. (For those familiar with Xbase products, the concept is similar to that of the SET RELATION functionality.)
There are three types of hierarchies implemented in ADO 2.0:
Hierarchies are implemented in ADO using a new command syntax that defines how to shape the recordsets. Embedded within that syntax are the actual commands to be sent to the data source.
A sample of each type of hierarchy is shown in the following sections, as well as a sample that mixes both relation and group hierarchies.
The first command is the most basic of shape commands. It takes two recordsets (defined by the SQL statements in the curly braces) and then relates them on the CUSTOMERID fields in both recordsets:
SHAPE {select * from customers}
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid)
Which yields:
Customers.*
rsOrders
|
+----Orders.*
In the preceding diagram, the parent recordset contains all fields from the Customers table and a field called rsOrders, which provides a reference to the child recordset, which contains all the fields from the Orders table. The other examples use a similar notation.
The following sample executes this shape command and then prints out the results. For this sample, the data source has changed to the NWIND Access database included with Microsoft Visual Studio and Microsoft Office.
Sub shapetest()
Dim rst As New adodb.Recordset
strConnect = "Provider=MSDataShape;data _
provider=msdasql;Data Source=nwind;"
rst.Source = "shape {select * from customers} APPEND " & _
"({Select * from orders} As rsOrders " & _
"RELATE customerid to customerid)"
rst.ActiveConnection = strConnect
rst.Open , , adOpenStatic, adLockBatchOptimistic
printtbl rst, 0
End Sub
Sub printtbl(rs, indent)
Dim rsChild As adodb.Recordset
While rs.EOF <> True
For Each col In rs.Fields
If col.Type <> adChapter Then
Debug.Print Space(indent), col.Value,
Else
Debug.Print
Set rsChild = col.Value
printtbl rsChild, indent + 4
End If
Next
Debug.Print
rs.MoveNext
Wend
End Sub
This second sample is a variation on the original sample. It generates the same results as the first command. However, instead of getting all of the data for the orders recordset up front, it fetches data for the orders recordset only when the application attempts to look at any of the orders.
SHAPE {select * from customers}
APPEND ({select * from orders where customerid = ?} AS rsOrders
RELATE customerid TO PARAMETER 0)
There is no difference in the results between the two commands. However, because the expression handling code in the Data Shaping Provider is different than that in the server, in some cases the two commands might generate different results. This could be especially noticeable with relations involving Null values.
In the group hierarchy, only one source recordset is used, but two recordsets are returned from executing the command. The parent recordset contains group and aggregate information, and the child recordset contains the actual details of the individual rows. Consider the following sample:
SHAPE {select customerid, region from customers} rs1
COMPUTE COUNT(rs1.customerid) As CustCount, rs1 By region
This creates a two-recordset hierarchy. The shape of the resulting hierarchy is as follows:
CustCount
Rs1
|
+----customerid
region
CustCount is the calculated count of the number of customers in each group. RS1 is a recordset valued column. (The type for the Field.Type property is adChapter, to reflect the OLE DB column type DBTYPE_CHAPTER.)
Data shaping puts a lot of power in the developer's hands. However, the shape commands themselves can become complex. The ADO 2.0 documentation expands on the samples given here as well as describing the full grammar.
For ADO 2.0—and in general for MDAC 2.0—an extensive amount of work has gone into improving the overall performance. This is especially true for multitier applications. For providers that support it, OLE DB Service Components now provide session pooling (see discussion earlier in this article). MDAC 2.0 also scales to multiple processors much better than its predecessors did.
One new user-controllable feature in ADO 2.0 deserves mention. In ADO 1.0 and 1.5, all commands executed against a server returned a Recordset object—whether or not there were any results returned to the consumer application. ADO 2.0 adds a new option flag to Command.Execute and Connection.Execute that tells ADO specifically to not create a recordset, thus reducing overhead.
To turn on this feature, add the value of adExecuteNoRecords to the value being passed to the Options parameter, as shown in the following example. Note that adExecuteNoRecords works only with adCmdStoredProc and adCmdText command types. The others by definition should return results.
Sub NoRecords()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql65server;" _
& "User ID=sa;Password=''" ' ;Initial Catalog=pubs
conn.Open
'Use from the Connection Object Execute method
conn.Execute "drop table testtable", , adCmdText + adExecuteNoRecords
conn.Execute "create table testtable (dbkey int primary key,
field1 char(10))", , adCmdText + adExecuteNoRecords
'Or from a Command object
Set cmd.ActiveConnection = conn
cmd.CommandText = "insert into testtable values (?, ?)"
cmd.Parameters.Refresh
cmd.Prepared = True
For ii = 1 To 1000
cmd.Execute , Array(ii, "string" & ii), adCmdText + adExecuteNoRecords
Next
End Sub
Depending on the environment, this can improve performance and reduce memory overhead for non-row–returning commands.
ADO and RDS take a big step forward in version 2.0. They add features that make it easier for RDO 2.0 developers to migrate to ADO, as well as new features that enable developers to make richer applications.