Table of contents
TOC
Collapse the table of content
Expand the table of content

The Recordset Object Open Method

office 365 dev account|Last Updated: 4/3/2018
|
3 Contributors

Applies to: Access 2013 | Access 2016

In this articleSource and Options ArgumentsActiveConnection ArgumentCursorType ArgumentLockType ArgumentRetrieving Multiple Recordsets

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 .OpenSource, 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.

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2018 Microsoft