Having planned so carefully, what do you do with the cursor once you’ve created it? I’ll give a typical example that uses a cursor to fill a list box. This example also brings together the concepts we’ve covered and illustrates them with a practical application.. Here’s the only code contained in the program, attached to the click event of the command button
Private Sub cmdFillList_Click()
Dim cnn As ADODB.Connection
Dim rstAuthors As ADODB.Recordset
Dim strCnn As String
'Create Connection
strCnn = "Provider=sqloledb;server=pc6742;Initial Catalog=pubs;Integrated Security=SSPI;"
Set cnn = New ADODB.Connection
cnn.Open strCnn
'Create Recordset
Set rstAuthors = New ADODB.Recordset
rstAuthors.CursorLocation = adUseServer
rstAuthors.Open "SELECT * FROM Authors", cnn, adOpenForwardOnly, adLockReadOnly
'Fill Listbox
lstNames.Clear
rstAuthors.MoveFirst
While Not rstAuthors.BOF And Not rstAuthors.EOF
lstNames.AddItem Trim(rstAuthors!au_fname) & " " & Trim(rstAuthors!au_lname)
rstAuthors.MoveNext
Wend
rstAuthors.Close
cnn.Close
End Sub
When the user clicks the button, the application initiates a connection to the pubs database on SQL Server, using a trusted connection. Then the code creates a new record set, called rstAuthors. This record set contains all the fields for all the rows in the authors table. I’ve made it a forward scrolling cursor with read-only locking, using the resources on the server. After the program clears the listbox, a loop moves through each record in the set, adding the first and last names of each author to the box. Note the MoveFirst and MoveNext methods of the recordset. These do exactly as their names indicate.