Efficient: HasRecords?

[kunguito]

Hi everyone?

Does someone know an efficient way to find out if a table has records?

I don't like that one since it might run slowly!

[vba]qry= "SELECT Date_stock FROM KILN;"
Set RS = CurrentDb().OpenRecordset(consulta)
If RS.RecordCount > 0 Then
HasRecords= True
Else
HasRecords= False
End If[/vba]

[matthewspatrick]

[VBA]
Dim HasRecords As Boolean

HasRecords = (DCount("*", "[NameOfTable]") > 0)
[/VBA]

[kunguito]

Thanks, it's also possible in SQL,

[vba] qry="SELECT Count([NameOfTable].Field) AS CountElements FROM [KILN];"
Set RS = CurrentDb().OpenRecordset(qry)
HasRecords=RS.Fields("CountElements")>0
[/vba]

[matthewspatrick]

If you're going to do it with SQL, you can do it even more efficiently than that:

[VBA]
Dim rs As DAO.Recordset
Dim HasRecords As Boolean

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) AS NumRec FROM SomeTable")
HasRecords = (rs!NumRec > 0)
rs.Close
Set rs = Nothing
[/VBA]

[kunguito]

Cool!

But why is it more efficient?
Is it the use of * what makes it go faster?

Thanks

[stanl]

Thanks, it's also possible in SQL,

or

[vba]
Dim rs As DAO.Recordset
Dim HasRecords As Boolean

Set rs = CurrentDb.OpenRecordset("SELECT Date_stock FROM KILN;")
HasRecords = rs.eof
rs.Close
Set rs = Nothing
[/vba]

[matthewspatrick]

stanl,

Touch?. Of course, we can make that better still:

[VBA]
Dim rs As DAO.Recordset
Dim HasRecords As Boolean

Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 Date_stock FROM KILN;")
HasRecords = (Not rs.eof)
rs.Close
Set rs = Nothing
[/VBA]

No need to fetch all the records, or even aggregate them (like I did), if we use your elegant suggestion

[matthewspatrick]

Cool!

But why is it more efficient?
Is it the use of * what makes it go faster?

Thanks

My thought was that, if all we need is to see if there are any data at all, then there is no need to fetch all the records; one will do. My original SQL statement did a simple aggregation. My "SELECT TOP 1" suggestion should go even faster than that.

Of course, if you actually do need all the data for some other purpose, then neither of my suggestions are worthwhile...