OpenRecordset Method

Applies To

Database Object, Dynaset-Type Recordset Object, QueryDef Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object, TableDef Object.

Description

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

Syntax

Set variable = database.OpenRecordset(source[, type[, options]])

Set variable = object.OpenRecordset([type[, options]])

The OpenRecordset method syntax has these parts.

Part	Description

variable	A variable that has been declared as an object data type Recordset.
database	The name of an existing Database object you want to use to create the new Recordset.
object	The name of an existing QueryDef, Recordset, or TableDef object you want to use to create the new Recordset. This argument can't be the name of a forward-only scrolling snapshot.
	If object refers to a dynaset or Snapshot-Type Recordset object, the type of the new object is the same as that of the Recordset specified by object. If object refers to a Table-Type Recordset object, the type of the new object is a Dynaset-Type Recordset.
source	A String specifying 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, the source can only be a table name.
type	If you don't specify a type, OpenRecordset creates a Table-Type Recordset if possible. If you specify an attached table or query, OpenRecordset creates a Dynaset-Type Recordset. This value can be one of the following Integer constants:
	u dbOpenTable — to open a Table-Type Recordset object.
	u dbOpenDynaset — to open a Dynaset-Type Recordset object.
	u dbOpenSnapshot — to open a Snapshot-Type Recordset object.

Part	Description

options	Any combination (or none) of the following Integer constants specifying characteristics of the new Recordset, such as restrictions on other users' ability to edit and view it:
	u dbAppendOnly — You can append only new records (Dynaset-Type Recordset only).
	u dbForwardOnly — The Recordset is a forward-only scrolling snapshot. Note that Recordset objects created with this option cannot be cloned and only support the MoveNext method to move through the records.
	u dbSQLPassThrough — SQL pass-through. Causes the SQL statement to be passed to an ODBC database for processing.
	u dbSeeChanges — Generate a run-time error if another user is changing data you are editing.
	u dbDenyWrite — Other users can't modify or add records.
	u dbDenyRead — Other users can't view records (Table-Type Recordset only).
	u dbReadOnly — You can only view records; other users can modify them.
	u dbInconsistent — Inconsistent updates are allowed (Dynaset-Type Recordset only).
	u dbConsistent — Only consistent updates are allowed (Dynaset-Type Recordset only).

Note

The constants dbConsistent and dbInconsistent are mutually exclusive. You can use one or the other, but not both in a given instance of OpenRecordset.

Remarks

If object refers to a QueryDef, or a dynaset- or Snapshot-Type Recordset, or if source refers to an SQL statement or a TableDef that represents an attached table, you can't use dbOpenTable for the type argument; if you do, a trappable error occurs.

If you want to use an SQL pass-through query on an attached table, you must first set the Connect property of the attached table's database to a valid ODBC connect string. For more information on this, see the Connect property.

Use the dbSeeChanges flag if you want to trap changes made by another user or another program on your machine when you're editing or deleting the same record. For example, if two users start editing the same record, the first user to execute the Update method succeeds. When the Update method is executed by the second user, a run-time error occurs. Similarly, if the second user tries to use the Delete method to delete the record, and it has already been changed by the first user, a run-time error also occurs.

Typically, if the user gets this error while updating, your code should refresh the contents of the fields and retrieve the newly modified values. If the error occurs in the process of deleting, 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.

Tip

Use the forward-only scrolling option (dbForwardOnly) to improve performance when your application makes a single pass through a Recordset opened from an ODBC data source.

When you close a Recordset using the Close method, it's automatically deleted from the Recordsets collection.

See Also

Connect Property, Type Property.

Example

See the Edit method example.

Example (Microsoft Access)

The following example opens a Dynaset-Type Recordset object and prints the number of records in the Recordset object.

Sub UKOrders()
    Dim dbs As Database, rst As Recordset
    Dim strSQL As String

    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Orders WHERE [ShipCountry] = 'UK'"
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    Debug.Print rst.RecordCount
End Sub

Example (Microsoft Excel)

This example displays a custom dialog box containing a list of all available recordsets in the NWINDEX.MDB database. The example opens a new recordset based on the recordset selected by the user and then copies the records onto Sheet1.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs1 As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Application.ScreenUpdating = False
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
Set label1 = theDialog.Labels.Add(78, 125, 240, 25)
label1.Text = "Recordsets for " & db.Name
i = 0

Do Until i = db.TableDefs.Count
    list1.AddItem (db.TableDefs(i).Name)
    i = i + 1
Loop
Sheets("Sheet1").Activate
Application.ScreenUpdating = True
If theDialog.Show = True Then
    If list1.Value = 0 Then
        MsgBox "You have not selected an item from the list."
    Else
        Set rs1 = db.OpenRecordset(db.TableDefs(list1.Value - 1).Name)
        ActiveCell.CopyFromRecordset rs1
    End If
End If
rs1.Close
db.Close