PDA

View Full Version : Efficient: HasRecords?



kunguito
04-24-2008, 02:40 AM
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!

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

matthewspatrick
04-24-2008, 03:33 AM
Dim HasRecords As Boolean

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

kunguito
04-24-2008, 04:02 AM
Thanks, it's also possible in SQL,

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

matthewspatrick
04-24-2008, 04:05 AM
If you're going to do it with SQL, you can do it even more efficiently than that:


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

kunguito
04-24-2008, 04:09 AM
Cool!

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

Thanks

stanl
04-24-2008, 04:40 AM
Thanks, it's also possible in SQL,


or


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

matthewspatrick
04-24-2008, 04:45 AM
stanl,

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


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


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

matthewspatrick
04-24-2008, 04:48 AM
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...