count records with ado

[Jason]

Hi,

How can i count the records of an access table with ado?

Is it something like this? I know i'm missing something.

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * From mytable"

rst.open strsql
rst.recordcount
debug.print rst

rst.Close

Set rst = Nothing

 

[Ron Weiner]

Jason

Here is a function that returns the number of records in a table.

Public Function GetRecordCount(strTableName) As Long
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "Select Count(*) as TotRec from " & _
strTableName, CurrentProject.Connection
GetRecordCount = rs("TotRec")
rs.Close
Set rs = Nothing
End Function

Put the function in a module and then you can call it from anywhere like:

YourVariable = GetRecordCount("MyTable")

or from the immediate window

? GetRecordCount("MyTable")


Ron W