Database.OpenRecordset Method (DAO)

Office 2013 and later

Last modified: March 09, 2015

Applies to: Access 2013 | Office 2013

In this article
About the Contributors

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

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

expression A variable that represents a Database object.




Data Type





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.




A RecordsetTypeEnum constant that indicates the type of Recordset to open.

Note 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.




A combination of RecordsetOptionEnum constants that specify characteristics of the new Recordset.

Note 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.




A LockTypeEnum constant that determines the locking for the Recordset.

Note 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


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 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: Community Member Icon The UtterAccess community | About the Contributors

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 | About the Contributors

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = 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 rest = 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!ToBeVisited = True

    'We've done what was needed. Now exit
    Exit Do


Set rstFiltered = Nothing
Set rst = Nothing

UtterAccess is the premier Microsoft Access wiki and help forum. Click here to join.

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.