O'Reilly logo

Access 2003 VBA Programmer's Reference by Armen Stein, Graham Seach, Teresa Hennig, Patricia Cardoza

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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") ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required