Export (0) Print
Expand All

Database.OpenRecordset Method (DAO)

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.

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

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

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.

Community Additions

ADD
Show:
© 2014 Microsoft