The Recordset Object Open Method

Everything you need to open an ADO Recordset is built into the Open method. You can use it without explicitly creating any other objects. The syntax of this method is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

All arguments are optional because the information they pass can be communicated to ADO in other ways. However, understanding each argument will help you to understand many important ADO concepts. The following topics will examine each argument of this method in more detail.

Source and Options Arguments

The Source and Options arguments appear in the same topic because they are closely related.

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The Source argument is a Variant that evaluates to a valid Command object, a text command (e.g., a SQL statement), a table name, a stored procedure call, a URL, or the name of a file or Stream object containing a persistently stored Recordset. If Source is a file path name, it can be a full path ("C:\dir\file.rst"), a relative path ("..\file.rst"), or a URL ("http://files/file.rst"). You can also specify this information in the Recordset object Source property and leave the Source argument blank.

The Options argument is a Long value that indicates either or both of the following:

• How the provider should evaluate the Source argument if it represents something other than a Command object.
• That the Recordset should be restored from a file where it was previously saved.

This argument can contain a bitmask of CommandTypeEnum or ExecuteOptionEnum values. A CommandTypeEnum passed in the Options argument sets the CommandType property of the Recordset.

Note   The ExecuteOpenEnum values of adExecuteNoRecords and adExecuteStream cannot be used with Open.

If the CommandType property value equals adCmdUnknown (the default value), you might experience diminished performance, because ADO must make calls to the provider to determine whether the CommandText property is a SQL statement, a stored procedure, or a table name. If you know what type of command you are using, setting the CommandType property instructs ADO to go directly to the relevant code. If the CommandType property does not match the type of command in the CommandText property, an error occurs when you call the Open method.

For more information about using these enumerated constants for Options and with other ADO methods and properties, see CommandTypeEnum and ExecuteOptionEnum.

ActiveConnection Argument

You can pass in either a Connection object or a connection string as the ActiveConnection argument.

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The ActiveConnection argument corresponds to the ActiveConnection property and specifies in which connection to open the Recordset object. If you pass a connection definition for this argument, ADO opens a new connection using the specified parameters. After opening the Recordset with a client-side cursor (CursorLocation = adUseClient), you can change the value of this property to send updates to another provider. Or you can set this property to Nothing (in Microsoft Visual Basic) or NULL to disconnect the Recordset from any provider. Changing ActiveConnection for a server-side cursor generates an error, however.

If you pass a Command object in the Source argument and also pass an ActiveConnection argument, an error occurs because the ActiveConnection property of the Command object must already be set to a valid Connection object or connection string.

CursorType Argument

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

As discussed in The Significance of Cursor Location, the type of cursor that your application uses will determine which capabilities are available to the resultant Recordset (if any). For a detailed examination of cursor types, see Chapter 8: Understanding Cursors and Locks.

The CursorType argument can accept any of the CursorTypeEnum values.

LockType Argument

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Set the LockType argument to specify what type of locking the provider should use when opening the Recordset. The different types of locking are discussed in Chapter 8: Understanding Cursors and Locks.

The LockType argument can accept any of the LockTypeEnum values.

Retrieving Multiple Recordsets

You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:

CREATE PROCEDURE ProductsWithAvgPrice 
AS
SELECT ProductID, ProductName, UnitPrice 
  FROM PRODUCTS 
  COMPUTE AVG(UnitPrice)

The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:

'BeginNextRs
    On Error GoTo ErrHandler:
    
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn
    
    objCmd.CommandText = "ProductsWithAvgPrice"
    objCmd.CommandType = adCmdStoredProc
    
    Set objRs = objCmd.Execute
    
    Do While Not objRs.EOF
        Debug.Print objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2)
        objRs.MoveNext
    Loop
    
    Set objRs = objRs.NextRecordset
    
    Debug.Print "AVG. PRICE = $ " & objRs(0)

    'clean up
    objRs.Close
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Exit Sub
    
ErrHandler:
    'clean up
    If objRs.State = adStateOpen Then
        objRs.Close
    End If
    
    If objConn.State = adStateOpen Then
        objConn.Close
    End If
    
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
'EndNextRs

For more information, see NextRecordset.