Database.OpenRecordset method (DAO)

Article
03/18/2022

Applies to: Access 2013 | Office 2013

Creates a new Recordset object and appends it to the Recordsets collection.

Syntax

expression.OpenRecordset (Name, Type, Options, LockEdit)

expression A variable that represents a Database object.

Parameters

Name	Required/optional	Data type	Description
Name	Required	String	The source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects in Microsoft Access database engine databases, the source can only be a table name.
Type	Optional	Variant	A RecordsetTypeEnum constant that indicates the type of Recordset to open. NOTE: If you open a Recordset in a Microsoft Access workspace and you don't specify a type, OpenRecordset creates a table-type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset.
Options	Optional	Variant	A combination of RecordsetOptionEnum constants that specify characteristics of the new Recordset. NOTE: The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying a LockEdit argument when Options uses the dbReadOnly constant also causes an error.
LockEdit	Optional	Variant	A LockTypeEnum constant that determines the locking for the Recordset. NOTE: You can use dbReadOnly in either the Options argument or the LockedEdit argument, but not both. If you use it for both arguments, a run-time error occurs.

Return value

Recordset

Remarks

Typically, if the user gets this error while updating a record, your code should refresh the contents of the fields and retrieve the newly modified values. If the error occurs while deleting a record, your code could display the new record data to the user and a message indicating that the data has recently changed. At this point, your code can request a confirmation that the user still wants to delete the record.

You should also use the dbSeeChanges constant if you open a Recordset in a Microsoft Access database engine-connected ODBC workspace against a Microsoft SQL Server 6.0 (or later) table that has an IDENTITY column, otherwise an error may result.

Opening more than one Recordset on an ODBC data source may fail because the connection is busy with a prior OpenRecordset call. One way around this is to fully populate the Recordset by using the MoveLast method as soon as the Recordset is opened.

Closing a Recordset with the Close method automatically deletes it from the Recordsets collection.

Note

If source refers to an SQL statement composed of a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to open the Recordset. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and SQL only accepts U.S. decimal characters.

Link provided by the UtterAccess community. UtterAccess is the premier Microsoft Access wiki and help forum.

Transfer data from Access to Excel

Example

The following example shows how to open a Recordset that is based on a parameter query.

Sample code provided by the Microsoft Access 2010 Programmer’s Reference.

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    
    'Get the parameter query
    Set qdf = dbs.QueryDefs("qryMyParameterQuery")
    
    'Supply the parameter value
    qdf.Parameters("EnterStartDate") = Date
    qdf.Parameters("EnterEndDate") = Date + 7
    
    'Open a Recordset based on the parameter query
    Set rst = qdf.OpenRecordset()

The following example shows how to open a Recordset based on a table or a query.

    Dim dbs As DAO.Database
    Dim rsTable As DAO.Recordset
    Dim rsQuery As DAO.Recordset
    
    Set dbs = CurrentDb
    
    'Open a table-type Recordset
    Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable)
    
    'Open a dynaset-type Recordset using a saved query
    Set rsQuery = dbs.OpenRecordset("qryMyQuery", dbOpenDynaset)

The following example shows how to open a Recordset based on a Structured Query Language (SQL) statement.

    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    'Open a snapshot-type Recordset based on an SQL statement
    strSQL = "SELECT * FROM Table1 WHERE Field2 = 33"
    Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The following sample shows how to use the Filter property to determine the records to be included in a subsequently opened Recordset.

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstFiltered As DAO.Recordset
    Dim strCity As String
    
    Set dbs = CurrentDb
    
    'Create the first filtered Recordset, returning customer records
    'for those visited between 30-60 days ago.
    Set rst = dbs.OpenRecordset(_ 
        "SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
        "AND Date()-30 ORDER BY LastVisitDate DESC")
    
    'Begin row processing
    Do While Not rst.EOF
        
        'Retrieve the name of the first city in the selected rows
        strCity = rst!City
    
        'Now filter the Recordset to return only the customers from that city
        rst.Filter = "City = '" & strCity & "'"
        Set rstFiltered = rst.OpenRecordset
    
        'Process the rows
        Do While Not rstFiltered.EOF
            rstFiltered.Edit
            rstFiltered!ToBeVisited = True
            rstFiltered.Update
            rstFiltered.MoveNext
        Loop
    
        'We've done what was needed. Now exit
        Exit Do
        rst.MoveNext
       
    Loop
    
    'Cleanup
    rstFiltered.Close
    rst.Close
    
    Set rstFiltered = Nothing
    Set rst = Nothing