|
Dim rs As New ADODB.Recordset
Dim arr() As Variant Dim reccount As Long rs.Open "SELECT * FROM Publishers", "DSN=pubs", , , adCmdText ' get all the rows in one shot arr() = rs.GetRows() ' now you can determine the number of recordset reccount = UBound(arr, 2) + 1 ' continue to process the values, now in arr() ' ... 'In some cases, however, you can't use this approach. 'For example, the number of returned rows might be too high '(and the arr() array would therefore take too much memory). 'Or you might be using an updateable cursor (e.g. a dynamic 'cursor), and you don't want to read and process all the 'values twice, once in the GetRows method and once using a 'MoveNext loop. 'In all these cases you should submit two distinct SQL queries 'to the database, one to determine the number of rows in the 'Recordset, and the next one to retreive the actual rows: Dim cn As New ADODB.Connection, rs As New ADODB.Recordset Dim reccount As Long cn.Open "DSN=pubs" ' first, retrieve the number of records rs.Open "SELECT COUNT(*) FROM publishers", cn, , , adCmdText ' the returned Recordset has one row with one field reccount = rs(0) rs.Close ' then retrieve the actual rows rs.Open "SELECT * FROM Publishers", cn, , , adCmdText ' .... 'If you're working with SQL Server or another database engine 'that supports multiple SQL statements in a query, you can 'optimize your code by submitting one single query, as in: Dim rs As New ADODB.Recordset Dim reccount As Long, sql As String sql = "SELECT COUNT(*) FROM publishers;" & "SELECT * FROM publishers" rs.Open sql, "DNS=pubs", , , adCmdText ' the first returned Recordset contains the COUNT(*) value reccount = rs(0) ' the second Recordset contains the actual rows Set rs = rs.NextRecordset ' ... '---====[ pAssed by vbTips32 codeBook ]====--- Depending on the approximate number of expected records, you can use the GetRows method to retrieve the Recordset's entire contents, and then use the UBound() function to determine the number of returned rows: |