Fastest way to count records in table

[Guest]

I know you can open a table, move last and get the recordcount.

But, is there any way to find out how many records are in a table without
opening it? Some sort of property?

 

[Arvin Meyer]

In order to look into it, you must open it, or a recordset based on it. What
exactly are you trying to do?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Mark via AccessMonster.com]

You could use a totals query and grab the count. Write a little function
and then pass the tablename and the fieldname to the function and use it to
get the recordcount for any table you want:

Function RecordsInTable(Tablename As String, Fieldname As String) As Long
Dim strSQL As String, strTableField As String
Dim rst As DAO.Recordset
strTableField = Tablename & "." & Fieldname
strSQL = "SELECT Count(" & strTableField & ") AS [Count] FROM " &
Tablename & ";"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
RecordsInTable = rst!Count
Set rst = Nothing
End Function


Here's how you call the function:

Sub test()
MsgBox "Records in table: " & RecordsInTable("Table1", "ID")
End Sub

This will work for tables with no records as well, just make sure the table
name and field name exist; you can (and should) add some error-checking.
If you use a totals query, you don't have to bother with checking for EOF
and moving to last record before you can get the record count.

 

[Marshall Barton]

I know you can open a table, move last and get the recordcount.

But, is there any way to find out how many records are in a table without
opening it? Some sort of property?

That's true of a recordset on a table or query. However, if
you are opening a recorset just for this purpose, it would
be much faster to use a query like:

Set rs = CurrentDb.OpenRecordset("Select Count(*) " _
& "From table/query")
numrecords = rs(0)
rs.Close : Set rs = Nothing

You could also use DCount("*", "table/query"), which will
hide the logic from your code.

For a local table, not a query or a linked table, you could
also get the record count directly from the TableDef object:

numrecords = CurrentDb.TableDefs!tablename.RecordCount