Access 2003 VBA Programmer's Reference

7.5. Creating ADO Recordsets

ADO recordsets are basically the same as DAO recordsets, with a few notable differences, as explained below.

7.5.1. Creating a Standard Recordset

In DAO you would create a recordset, like this

Set rs = db.OpenRecordset( source, options)

But in ADO, you could do it like this:

rs.Open Source, ConnectionString,
CursorType, LockType, Options

The Source argument can be an SQL statement, the name of a table, the name of a stored procedure, a URL, or a provider-specific text or command. The ConnectionString argument can be a Connection object or a Connection string. Appendix J contains a list of all the available CursorType, LockType, and Options values.

You can supply more than one Options value by using the AND operator, for example:

rs.Options = adCmdStoredProc And adAsyncFetchNonBlocking

7.5.2. Creating a Recordset from a Command Object

Suppose you need to create a recordset that is based on a parameter query. Most often, you won't know what values to supply until you get to that point in your code. The problem is, of course, how to supply those values?

The answer is to base your recordset on a Command object, which itself is based on the Parameter query. How does this solve your dilemma? The following is a typical example.

Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset 'Build the Command object With cmd .ActiveConnection = CurrentProject.Connection .CommandText = "qryPrices" .CommandType = adCmdTable .Parameters.Refresh .Parameters("City") ...