HandsOn Counting the Number of Returned Records

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the CountRecords procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub CountRecords()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim myarray As Variant Dim returnedRows As Integer Dim r As Integer 'record counter Dim f As Integer 'field counter

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset rst.Open "SELECT * FROM Employees", _

conn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Return all rows into array myarray = rst.GetRows() returnedRows = UBound(myarray, 2) + 1

MsgBox "Total number of records: " & returnedRows

' Find upper bound of second dimension For r = 0 To UBound(myarray, 2) Debug.Print "Record " & r + 1 ' Find upper bound of first dimension

Part II

' Print data from each row in array Debug.Print Tab; _

rst.Fields(f).Name & " = " & myarray(f, r)

Next f Next r rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

Notice how the CountRecords procedure prints the contents of the array to the Immediate window by using a nested loop.

0 0

Post a comment