Opening a Recordset With DAO and ADO

Like DAO, ADO Recordset objects can be opened from several different objects. In ADO, a Recordset can be opened with the Connection object Execute method, the Command object Execute method, or the Recordset object Open method. ADO Recordset objects cannot be opened directly from Table, Procedure, or View objects. ADO Recordset objects opened with the Execute method are always forward-only, read-only recordsets. If you need to be able to scroll or update data within the Recordset you must use the Recordset object Open method.

The CursorType, LockType, and Options parameters of the Open method determine the type of Recordset that is returned. The table below shows how the parameters to the DAO Recordset object Open method can be mapped to ADO properties.

DAO Recordset type	ADO Recordset properties or parameters
dbOpenDynaset	CursorType=adOpenKeyset
dbOpenSnapshot	CursorType=adOpenStatic
dbOpenForwardOnly	CursorType=adOpenForwardOnly
dbOpenTable	CursorType=adOpenKeyset, Options=adCmdTableDirect

DAO Recordset Options values	ADO Recordset properties
dbAppendOnly	Properties("Append-Only Rowset")
dbSQLPassThrough	Properties("Jet OLEDB:ODBC Pass-Through Statement")
dbSeeChanges	No equivalent.
dbDenyWrite	No equivalent.
dbDenyRead	No equivalent.
dbInconsistent	Properties("Jet OLEDB:Inconsistent") = True
dbConsistent	Properties("Jet OLEDB:Inconsistent") = False

DAO Recordset LockType values	ADO Recordset LockType values
dbReadOnly	adLockReadOnly
dbPessimistic	adLockPessimistic
dbOptimistic	adLockOptimistic

The Microsoft Jet Provider does not support a number of combinations of CursorType and LockType — for example, CursorType=adOpenDynamic and LockType=adLockOptimistic. If you specify an unsupported combination, ADO will pass your request to the Microsoft Jet Provider, which will then degrade to a supported CursorType or LockType. Use the CursorType and LockType properties of the Recordset once it is opened to determine what type of Recordset was created.

The following listings demonstrate how to open a forward-only, read-only Recordset, then prints the values of each field.

DAO

Sub DAOOpenRecordset()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field

  ' Open the database
  Set db = DBEngine.OpenDatabase(".\nn.mdb")

  ' Open the Recordset
  Set rst = db.OpenRecordset _
     ("SELECT * FROM Customers WHERE Region = 'WA'", _
     dbOpenForwardOnly, dbReadOnly)

  ' Print the values for the fields in
  ' the first record in the debug window
  For Each fld In rst.Fields
     Debug.Print fld.Value & ";";
  Next

  Debug.Print

  ' Close the recordset
  rst.Close

End Sub

ADO

Sub ADOOpenRecordset()

  Dim cnn As New ADODB.Connection
  Dim rst As New ADODB.Recordset
  Dim fld As ADODB.Field

  ' Open the connection
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=.\nn.mdb;"

  ' Open the forward-only,
  ' read-only recordset
  rst.Open _
     "SELECT * FROM Customers WHERE Region = 'WA'", _
     cnn, adOpenForwardOnly, adLockReadOnly

  ' Print the values for the fields in
  ' the first record in the debug window
  For Each fld In rst.Fields
     Debug.Print fld.Value & ";";
  Next

  Debug.Print

  ' Close the recordset
  rst.Close

End Sub

In the DAO and ADO code above, the Recordset is opened and then the data in the first record of the Recordset is printed to the Debug window by iterating through each field in the Fields collection and printing its Value.