VBA/Excel/Access/Word/Access/Recordset Cursor

Check after using the MoveNext method whether the end of the file has been reached

 
Sub moveNext()
    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)
    myRecordset.moveNext
    If myRecordset.EOF Then myRecordset.MovePrevious
End Sub

Designating the Cursor Location

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

Move cursor in a Recordset with MoveNext

 
Sub PropNulls()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "Employees", CurrentProject.Connection
    Do Until rst.EOF
        Debug.Print rst!EmployeeID, rst!Region
        rst.MoveNext
    Loop
End Sub

moves 5 records backward from the current record in a DAO recordset:

 
Sub MoveRows()
    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)
    myRecordset.Move Rows:=-5
End Sub

Move the resultset cursor with MoveNext method

 
Sub cursorMove()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Set conn = New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
   Set myRecordset = New ADODB.Recordset
   With myRecordset
      .Open "Customers", conn, adOpenKeyset, adLockOptimistic
      .Filter = "City="Madrid" and Country="Spain""
   End With
   Do Until myRecordset.EOF
      Debug.Print myRecordset.Fields(1).Value
      myRecordset.MoveNext
   Loop
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Moving Around in a Recordset

 
Sub MoveAround()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   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 Customers where ContactTitle = "Owner"", _
       conn, adOpenForwardOnly, adLockReadOnly
   Do While Not rst.EOF
      For Each fld In rst.Fields
         Debug.Print fld.Name & " = " & fld.Value
      Next
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Moving Through the Records in a Recordset:MoveFirst - To the first record in a recordset

 
Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from Employees"
    Debug.Print rst("EmployeeID")
    rst.MoveNext
    Debug.Print rst("EmployeeID")
    rst.MoveLast
    Debug.Print rst("EmployeeID")
    rst.MovePrevious
    Debug.Print rst("EmployeeID")
    rst.MoveFirst
    Debug.Print rst("EmployeeID")
    rst.Close
    Set rst = Nothing
End Sub

Moving Through the Records in a Recordset:MoveLast - To the last record in a recordset

 
Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from Employees"
    Debug.Print rst("EmployeeID")
    rst.MoveNext
    Debug.Print rst("EmployeeID")
    rst.MoveLast
    Debug.Print rst("EmployeeID")
    rst.MovePrevious
    Debug.Print rst("EmployeeID")
    rst.MoveFirst
    Debug.Print rst("EmployeeID")
    rst.Close
    Set rst = Nothing
End Sub

Moving Through the Records in a Recordset:MoveNext - To the next record in a recordset

 
Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from Employees"
    Debug.Print rst("EmployeeID")
    rst.MoveNext
    Debug.Print rst("EmployeeID")
    rst.MoveLast
    Debug.Print rst("EmployeeID")
    rst.MovePrevious
    Debug.Print rst("EmployeeID")
    rst.MoveFirst
    Debug.Print rst("EmployeeID")
    rst.Close
    Set rst = Nothing
End Sub

Moving Through the Records in a Recordset:MovePrevious - To the previous record in a recordset

 
Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from Employees"
    Debug.Print rst("EmployeeID")
    rst.MoveNext
    Debug.Print rst("EmployeeID")
    rst.MoveLast
    Debug.Print rst("EmployeeID")
    rst.MovePrevious
    Debug.Print rst("EmployeeID")
    rst.MoveFirst
    Debug.Print rst("EmployeeID")
    rst.Close
    Set rst = Nothing
End Sub

Navigate through a recordset

 
Public Sub RecordsetNavigation()
  Const SQL As String = "SELECT * FROM Customers"
    
  Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=C:\mydb.mdb;Persist Security Info=False"
  Dim Recordset As Recordset
  Set Recordset = New Recordset
  Call Recordset.Open(SQL, ConnectionString, adOpenDynamic)
  
  Recordset.MoveLast
  
  While Not Recordset.BOF
    Debug.Print Recordset.Fields("CompanyName")
    Recordset.MovePrevious
  Wend
End Sub

Set Cursor Location to adUseClient

 
Sub SortRecordset()
    Dim intCounter As Integer
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open "Select * from Employees"
    Debug.Print "NOT Sorted!!!"
    Do Until rst.EOF
        Debug.Print rst("EmployeeID")
        rst.MoveNext
    Loop
    Debug.Print "Now Sorted!!!"
    rst.Sort = "[EmployeeID]"
    Do Until rst.EOF
        Debug.Print rst("EmployeeID")
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

Set CursorLocation to adUseServer

 
Sub SupportsMethod()
    "Declare and instantiate a Recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseServer
    "Open the recordset, designating that the source
    "is a SQL statement
    rst.Open Source:="Select * from Employees ", _
        Options:=adCmdText
    "Determine whether the recordset supports certain features
    Debug.Print "Bookmark " & rst.Supports(adBookmark)
    Debug.Print "Update Batch " & rst.Supports(adUpdateBatch)
    Debug.Print "Move Previous " & rst.Supports(adMovePrevious)
    Debug.Print "Seek " & rst.Supports(adSeek)
    rst.Close
    Set rst = Nothing
End Sub

Set CursorType to adOpenDynamic

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

Set CursorType to adOpenForwardOnly

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

Set CursorType to adOpenKeyset

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

Set CursorType to adOpenStatic

 
Sub StaticRecordset1()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from Employees", _
        CursorType:=adOpenStatic
    Debug.Print rst.RecordCount
    rst.Close
    Set rst = Nothing
End Sub

Supplying the CursorType as a Parameter of the Open Method

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

Supplying the CursorType as a Property of the Recordset Object

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

Using the Recordset Movements() Methods on a Recordset Object

 
Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from Employees"
    Debug.Print rst("EmployeeID")
    rst.MoveNext
    Debug.Print rst("EmployeeID")
    rst.MoveLast
    Debug.Print rst("EmployeeID")
    rst.MovePrevious
    Debug.Print rst("EmployeeID")
    rst.MoveFirst
    Debug.Print rst("EmployeeID")
    rst.Close
    Set rst = Nothing
End Sub