Querying the database with the Recordset object

The ADO Recordset object represents the result set of a query. You can use it to view data from a database.

Sample code

You can try this routine by placing a command button named cmdQuery on a form and pasting the routine into its Click event. Run the program and click CmdQuery to connect, display a message on the database server window, execute a query and display the first few rows in message boxes, and then disconnect.

Private Sub cmdQuery_Click()
   ' Declare variables
   Dim myConn As New ADODB.Connection
   Dim myCommand As New ADODB.Command
   Dim myRS As New ADODB.Recordset
   On Error GoTo ErrorHandler

   ' Establish the connection
   myConn.Provider = "ASEOLEDB"
   myConn.ConnectionString = "Data Source = MANGO:5000; User ID=sa;PWD=;" +
      "Initial Catalog=pubs2;"
   myConn.Open

   'Execute a query
   Set myRS = New Recordset
   myRS.CacheSize = 50
   myRS.Source = "Select * from customer"
   myRS.ActiveConnection = myConn
   myRS.LockType = adLockOptimistic
   myRS.Open
   
   'Scroll through the first few results
   For i = 1 To 5
     MsgBox myRS.Fields("company_name"), vbInformation
     myRS.MoveNext
   Next
   myRS.Close
   myConn.Close
   Exit Sub 
ErrorHandler:
   MsgBox Error(Err)
   Exit Sub 
End Sub

Notes

The Recordset object in this example holds the results from a query on the Customer table. The For loop scrolls through the first several rows and displays the “company_name” value for each row.

This is a simple example of using a cursor from ADO.