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

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

   <source lang="vb">

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

</source>
   
  

Designating the Cursor Location

   <source lang="vb">

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

</source>
   
  

Move cursor in a Recordset with MoveNext

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Move the resultset cursor with MoveNext method

   <source lang="vb">

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

</source>
   
  

Moving Around in a Recordset

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Navigate through a recordset

   <source lang="vb">

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

</source>
   
  

Set Cursor Location to adUseClient

   <source lang="vb">

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

</source>
   
  

Set CursorLocation to adUseServer

   <source lang="vb">

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

</source>
   
  

Set CursorType to adOpenDynamic

   <source lang="vb">

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

</source>
   
  

Set CursorType to adOpenForwardOnly

   <source lang="vb">

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

</source>
   
  

Set CursorType to adOpenKeyset

   <source lang="vb">

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

</source>
   
  

Set CursorType to adOpenStatic

   <source lang="vb">

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

</source>
   
  

Supplying the CursorType as a Parameter of the Open Method

   <source lang="vb">

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

</source>
   
  

Supplying the CursorType as a Property of the Recordset Object

   <source lang="vb">

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

</source>
   
  

Using the Recordset Movements() Methods on a Recordset Object

   <source lang="vb">

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

</source>