See if a table contains anything without opening a recordset

[jbradley]

Is there any way to determine if an Access or SQL Server table contains any records without opening a recordset? Currently I open a recordset using "TOP 1" to restrict the number of rows returned and test for BOF/EOF but it seems like there ought to be a less clunky way to do it.

 

[TomKane]

You could do a COUNT - doesn't really help as you'll probably have to open a recordset to return the results of the count.

 

[pokermat]

How about using a stored procedure in SQL that returns a boolean indicating records or not. You would probably still have to open a recordset but doing it in SQL would be faster than VB.

Just a thought...

 

[CCLINT]

Yes, COUNT is the best way. Do it against the connection object. This should be fastest:

Dim rs As ADODB.Recordset

Set rs = conn.Execute ("SELECT COUNT(*) As TotalRecords FROM PERS", Options:=adCmdText)
Debug.Print rs.Fields(0).Value

In order to get the results fastest, use COUNT(*) and not COUNT(SomeField), and, use the absolute field position rs.Fields(0).Value, and not an Alias as in rs.Fields("TotalRecords&quot.Value.

 

[jbradley]

Thank you all. That will do the trick.