Database.OpenRecordset Method (DAO)

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.

Link provided by: The UtterAccess community | About the Contributors

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

About the Contributors

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.