Using the MoveLast Method to Count Number of Rows

Since the RecordCount property only returns the number of rows accessed, we need to use the MoveLast method of the Recordset object to access all of the records. By moving to the last record in the record set, RecordCount will store the number of rows in the entire record set.

Using MS-SQL to Count Number of Rows

Moving to the last record in a recordset may take significantly longer than executing a dynamic MS-SQL query against the Database object. If this is the case, MS-SQL can be used to count the number of records. This is one form:

m_strSQL = "SELECT Count (*) As RecCount " _
                    & "FROM tblMytable"
Set m_rsDAO = m_dbDAO.OpenRecordset(m_strSQL)

g_varReturn = m_rsDAO.Fields("RecCount")

where g_varReturn is a Public variant variable that will not generate a runtime error if the Fields property returns Null. In the above example, Null will never be returned but it is better to be safe than sorry!

More Use of MS-SQL Strings

Please see pages 16-17 to 16-19 for more ways to use MS-SQL strings.

Using the FindFirst and FindNext Methods to Find Data

The FindFirst and FindNext methods can be used to find all of the data in a given set of records. Below is a code fragment that finds the first order date for CustomerID =100 and then continues through the recordset until all order dates are found:

strCriteria = "CustomerID = 100"
With m_rsDAO
    .FindFirst strCriteria
    strList = .Fields("OrderDate")
    Do Until .NoMatch
        strList = strList & vbNewLine & .Fields("OrderDate")
        .FindNext strCriteria
    Loop
End With

The Boolean NoMatch property of the Recordset object makes up the Do...Loop condition.

General Form for Modifying Data

Use the Edit and Update methods to change data in a Recordset:

With m_rsDAO
    .Edit
    .Fields("MyField") = "Some new string value."
    .Update
End With

Note that omitting Update and then moving off of the row will cause data to be lost.