The ADO Recordset object represents the result set of a query. You can use it to view data from a database.
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
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.
