VBA/Excel/Access/Word/Access/Recordset Open

adAsyncExecute - The source is executed asynchronously.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adAsyncExecute
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adAsyncFetchNonBlocking - The main thread never blocks when fetching.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adAsyncFetchNonBlocking
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adAsyncFetch - The initial quantity specified in the Initial Fetch Size property is fetched.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adAsyncFetch
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adCmdFile - The source is evaluated as a persisted recordset.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdFile
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adCmdStoredProc - The provider evaluates the source as a stored procedure.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdStoredProc
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adCmdTable - A SQL query is generated to return all rows from the table named in the source.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdTable
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adCmdTableDirect - The provider returns all rows in the table named in the source.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdTableDirect
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

adCmdUnknown - The type of command in the source is unknown.

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdUnknown
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

Connection error handler

 
Sub ConnectionErrorHandler()
  On Error GoTo Except
    Dim Connection As ADODB.Connection
    Set Connection = CurrentProject.Connection
    Dim Recordset As New ADODB.Recordset
    Recordset.Open "badTable", Connection
    Exit Sub
Except:
  Dim ADOError As ADODB.Error
  For Each ADOError In Connection.Errors
    MsgBox "ADO Error: " & ADOError.Description & " Native Error: " & _
      ADOError.NativeError & " SQL State: " & _
      ADOError.SQLState & "Source: " & _
      ADOError.Source, vbCritical, "Error Number: " & ADOError.Number
  Next ADOError
End Sub

Creating a Recordset Using a Connection Object

 
Sub CreateRecordset3()
    Dim cnn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst1 = New ADODB.Recordset
    Set rst2 = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    rst1.ActiveConnection = cnn
    rst1.Open "Select * From Employees"
    rst2.ActiveConnection = cnn
    rst2.Open "Select * From Products"
    Debug.Print rst1.GetString
    Debug.Print rst2.GetString
    rst1.Close
    rst2.Close
    cnn.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set cnn = Nothing
End Sub

Creating a Recordset Using a Connection String

 
Sub CreateRecordset1()
    "Declare and instantiate the recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    "Open the recordset
    rst.Open "Select * From Employees", CurrentProject.Connection
    "Print its contents
    Debug.Print rst.GetString
    "Close and destroy the recordset
    rst.Close
    Set rst = Nothing
End Sub

Creating a Recordset Using the ActiveConnection Property

 
Sub CreateRecordset2()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * From Employees"
    Debug.Print rst.GetString
    rst.Close
    Set rst = Nothing
End Sub

Get Recordset content by calling Recordset.GetString

 
Sub CreateRecordset1()
    "Declare and instantiate the recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    "Open the recordset
    rst.Open "Select * From Employees", CurrentProject.Connection
    "Print its contents
    Debug.Print rst.GetString
    "Close and destroy the recordset
    rst.Close
    Set rst = Nothing
End Sub

Opening a DAO Recordset Using a SQL SELECT Statement

 
Sub recorder()
    Dim myDatabase As dao.Database
    Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb")
    
    Dim myRecordset As dao.Recordset
    Set myRecordset = myDatabase.OpenRecordset(Name:="SELECT * FROM Customers WHERE Country="Germany"", Type:=dbOpenDynaset)
End Sub

Opening a DAO Recordset Using a Table

 
Sub dao()
    Dim myDatabase As dao.Database
    Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb")
    
    Dim myRecordset As dao.Recordset
    Set myRecordset = myDatabase.OpenRecordset(Name:="Customers", Type:=dbOpenTable)
End Sub

Opening a Recordset

 
Sub OpenRst()
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   With rst
      .Source = "Select * from Employees"
      .ActiveConnection = CurrentProject.Connection
      .Open
      Debug.Print rst.Fields.count
      .Close
   End With
   Set rst = Nothing
End Sub

Opening a Recordset Based on an SQL Statement

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set rst = conn.Execute("Select * from Employees")
   Debug.Print rst("LastName") & ", " & rst("FirstName")
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Opening a Recordset Based on a Table or Query

 
Sub OpenRst_TableOrQuery()
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    
    rst.Open "Employees", conn
    
    Debug.Print "CursorType: " & rst.CursorType & vbCr & "LockType: " & rst.LockType & vbCr _
    & "Cursor Location: " & rst.CursorLocation
    
    Do Until rst.EOF
    Debug.Print rst.Fields(1)
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
End Sub

Opening a Recordset Based on a Table or Query with SQL command

 
Sub ConnectAndExec()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Set conn = New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set rst = conn.Execute("Select * from Employees")
   Debug.Print rst.Source
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Opening a Recordset Based on Criteria

 
Sub OpenRst_WithCriteria()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set rst = New ADODB.Recordset
   rst.Open "SELECT * FROM Employees WHERE ReportsTo is Null", _
       conn, adOpenForwardOnly, adLockReadOnly
   Do While Not rst.EOF
      Debug.Print rst.Fields(1).Value
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Opening a Recordset Directly

 
Sub OpenRst_Directly()
Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   With rst
      .Source = "Select * From Employees"
      .ActiveConnection = CurrentProject.Connection
      .Open
   End With
   MsgBox rst.Fields(1)
   rst.Close
   Set rst = Nothing
End Sub

Opening a Recordset with Inconsistent Updates

 
Sub InconsistentUpdates()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Properties("Jet OLEDB:Inconsistent") = True
    rst.Open Source:="Select * from Employees " & _
        "INNER JOIN Projects " & _
        "ON Employees.ClientID = Projects.ClientID", _
        Options:=adCmdText
    rst("Projects.ClientID") = 1
    rst.Update
    Debug.Print rst("Projects.ClientID")
    rst.Close
    Set rst = Nothing
End Sub

Options of Open Method: adCmdText - The provider evaluates the source as a command

 
Sub OptionsParameter()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from Employees", _
        Options:=adCmdText
    rst("City") = "Village"
    rst.Update
    Debug.Print rst("City")
    rst.Close
    Set rst = Nothing
End Sub

Recordset.Open method

 
Sub RecSetOpen()
   Dim rst As ADODB.Recordset
   Dim strConnection As String
   strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set rst = New ADODB.Recordset
   With rst
      .Open "Select * From Customers", _
          strConnection, adOpenForwardOnly
      .Save CurrentProject.Path & "\MyResultset.dat"
      .Close
   End With
   Set rst = Nothing
End Sub

sets the locking type in the call to the Open method

 
Private Sub Form_Open(Cancel As Integer)
    Dim con As ADODB.Connection
    Dim myRecordset As ADODB.Recordset
    Dim strFrmNm As String
    
    Set myRecordset = New ADODB.Recordset
    Set con = New ADODB.Connection
    
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\mydb.mdb;"
       
    myRecordset.Open "SELECT * FROM Employees", con, adOpenKeyset, adLockOptimistic
    Set Me.Recordset = myRecordset
    
    myRecordset.Close
    con.Close
    Set myRecordset = Nothing
    Set con = Nothing
End Sub

The RecordCount Property Is Not Supported with a Forward-Only Recordset

 
Sub ForwardOnlyRecordset()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from Employees"
    Debug.Print rst.RecordCount
    rst.Close
    Set rst = Nothing
End Sub

Use dbOpenSnapshot when opening an recordset

 
Sub exaRecordsets()
    Dim db As Database
    Dim rsTable As Recordset
    Dim rsDyna As Recordset
    Dim rsSnap As Recordset
     
    Set db = CurrentDb
    Set rsTable = db.OpenRecordset("Employees")
    Debug.Print "TableCount: " & rsTable.RecordCount
    Set rsDyna = db.OpenRecordset("Employees", dbOpenDynaset)
    Debug.Print "DynaCount: " & rsDyna.RecordCount
    rsDyna.MoveLast
    Debug.Print "DynaCount: " & rsDyna.RecordCount
    Set rsSnap = db.OpenRecordset("Employees", dbOpenSnapshot)
    Debug.Print "SnapCount: " & rsSnap.RecordCount
    rsSnap.MoveLast
    Debug.Print "SnapCount: " & rsSnap.RecordCount
    rsTable.Close
    rsDyna.Close
    rsSnap.Close
 
End Sub