Recordset.OpenRecordSet

Open a new Recordset. This allows you to navigate a set of records (or rows) in a table. n.b. In almost all circumstances an SQL query will perform faster than a RecordSet object.

Syntax
      expression.OpenRecordSet(Name, Type, Options, LockEdit)

Key
   expression A variable that represents a Recordset object.

   Name     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 Access database
            engine databases, the source can only be a table name.

   Type     The type of Recordset to open.
            dbOpenTable (1), dbOpenDynaset (2), dbOpenSnapshot (4)
            dbOpenForwardOnly (8), dbOpenDynamic (16)

            Default = dbOpenTable for Access tables 
            or dbOpenDynaset for a linked table/query.

   Options  A combination of RecordsetOptionEnum constants that
            specify characteristics of the new Recordset.
The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying a lockedits argument when options uses the dbReadOnly constant also causes an error. lockedits Locking for the Recordset: dbOptimistic (3), dbPessimistic (2), dbOptimisticBatch (5), dbOptimisticValue (1)

The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying a lockedits argument when options uses the dbReadOnly constant also causes an error.

Use the dbSeeChanges constant if you open a Recordset for an ODBC/SQL Server table.

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.

Example

Dim db As Database
Dim rst As Recordset

Set dbs = OpenDatabase("Northwind.mdb")
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
      
With rst
   ' Populate recordset.
.MoveLast
.MoveFirst End With

“Twenty years from now, you will be more disappointed by the things you didn't do than by the ones you did do. So throw off the bowlines, sail away from the safe harbour. Catch the trade winds in your sails. Explore. Dream. Discover” ~ Mark Twain

Related:

FindFirst/Last/Next/Previous Record
MoveFirst/Last/Next/Previous Record
RecordSet.Close - Close a recordset
RecordSet.Requery - re-execute the recordset query
RunSQL - Run an SQL query.
Update - Save a recordset
CancelUpdate - Cancel recordset changes


© Copyright SS64.com 1999-2014
Some rights reserved