Most efficient ADO way to detirmine if an Access table is empty?

[mdlueck]

I bumped into a need to query the FE DB to find out if an Access table is empty.

I came up with the following query which appears to work as desired:

  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  'Define a query to test if the table is empty
  strSQL = "SELECT DISTINCT 1 " & _
           "FROM " & strTableName & ";"

  'Define attachment to database table specifics and execute commands via With block
  Set adoRS = New ADODB.Recordset
  With adoRS
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open strSQL

    'Was no record found?
    If .BOF Or .EOF Then
      dbutils_ISFETableEmpty = True
    Else
      dbutils_ISFETableEmpty = False
    End If

    'Close the database table
    .Close
  End With

  'Clean up the connection to the database
  Set adoRS = Nothing

When not empty this at least puts only one record in the adoRS object. Suggestions I found do to "SELECT * / SELECT 1" filled the record set with as many records as is in the table, so I deem those suggestions as less efficient.

Any suggestions as to a better way to optimize this IsEmpty check?

 

[nanscombe]

How about using the DCount()function?

DCount([Fieldname],[Tablename],[Criteria])

Fieldname usually takes the field name as a string but it looks like you could use a column number instead, that way you wouldn't have to specify a proper field name.

So you could create a function to see if a table is empty

Public Function isTableEmpty(byval TableName as string)
isTableEmpty = (DCount(0 ,TableName) < 1)
end Function

This would return TRUE if there are 0 records or FALSE if there are 1 or more records.

 

[mdlueck]

How about using the DCount()function?

I would prefer to stick with SQL solutions and not dip into Access unique solutions. Such skills are more portable.

Perhaps I should have asked "SQL" instead of "ADO".

So I was specifically asking if there is a more efficient SQL query to use to determine if a table is empty or not. Various suggestions on the Internet did not seem efficient to me.

 

[Galaxiom]

BTW You could replace this

If .BOF Or .EOF Then
      dbutils_ISFETableEmpty = True
Else
      dbutils_ISFETableEmpty = False
End If

with one line

dbutils_ISFETableEmpty = .BOF Or .EOF

 

[nanscombe]

There appears to be a MaxRecords property for an ADO recordset (it works in Access as well) which limits the number of rows returned where 0, the default, returns the whole recordset.

I guess as you are only interested whether any records exists you could try adding .MaxRecords = 1, before the .Open strSQL statement, to limit it to pulling back the first record.

 

[mdlueck]

with one line

dbutils_ISFETableEmpty = .BOF Or .EOF

(chuckle...) Perhaps soon I will be able to see that as a valid way of setting a Boolean value.

 

[mdlueck]

I guess as you are only interested whether any records exists you could try adding .MaxRecords = 1

I would think that not necessary as I used the SQL keyword DISTINCT.

Clearly the SELECT * / SELECT 1 will return as many records in the RecordSet object that are in the table.

Would it be to much to hope that by adding the DISTINCT would cause the query not to scan the entire table since after the first record the question the query asked had been already satisfied?

I guess one way to test is to load up the table with LOTS of records, and time the results with / without the .MaxRecords = 1 being set.

 

[Galaxiom]

(chuckle...) Perhaps soon I will be able to see that as a valid way of setting a Boolean value.

It is absolutely just as valid in VBA as any other way but some developers do seem to have trouble accepting it, preferring to stick with clumsy If constructions.

BOF is Boolean, EOF is Boolean.
The result of the logical operation is a Boolean.

 

[vbaInet]

BTW You could replace this

If .BOF Or .EOF Then
      dbutils_ISFETableEmpty = True
Else
      dbutils_ISFETableEmpty = False
End If

with one line

dbutils_ISFETableEmpty = .BOF Or .EOF

I would go with Galaxiom's method above or use the RecordCount property of the recordset. The RecordCount property in ADO returns -1 whereas DAO returns 0.

NB: You don't need DISTINCT if you're using any of the suggestions. The DISTINCT clause is slow.

 

[Galaxiom]

The RecordCount property in ADO returns -1 whereas DAO returns 0.

??????

ADO RecordCount returns 0 for an empty recordset.

DAO will return -1 when it can't determine the number of records such as on a ForwardOnly recordset.

 

[vbaInet]

??????

ADO RecordCount returns 0 for an empty recordset.

DAO will return -1 when it can't determine the number of records such as on a ForwardOnly recordset.

Sorry GalaxiomAtHome, I should have clarified my point. I was referring to the use of adOpenDynamic in the OP. If you use that cursortype it could result a record count of -1 in ADO. Also, like you mentioned, a forward only recordset will also yield a -1 record count. Cursor types like adOpenStatic and adOpenKeyset don't "normally" have this problem.

 

[mdlueck]

NB: You don't need DISTINCT if you're using any of the suggestions. The DISTINCT clause is slow.

I headed off in that direction since one record would be downloaded from SQL Server, thus saving bandwidth, FE DB size, etc...

But yes, this Q was FE DB / Access specific.

I am leery to go off setting custom ADO attributes to tweak how the query works suspecting that once again I am relying on "SQL + ADO" and not straight SQL... thus less portable.

I know, I know... my use of ADO Command / Parameters objects to run Stored Procedures is very ADO specific. ( http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120 ) In the back of my mind, thinking how to do XYZ without ADO available in the future. Already I ended up coding a non-GUI data replication capability in Access to be able to leverage ADO Command / Parameters objects as no comparable solution existed in another database driver for another programming language I prefer over VBA.

I think perhaps I will read through the various suggestions and distill down the correct Rx in order to remove DISTINCT. (gulp)

 

[mdlueck]

I would go with Galaxiom's method above or use the RecordCount property of the recordset. The RecordCount property in ADO returns -1 whereas DAO returns 0.

NB: You don't need DISTINCT if you're using any of the suggestions. The DISTINCT clause is slow.

All right vbaInet, clear head with a couple of problems solved this morning and a walk at lunch. I decided to revisit this thread.

I have implemented Galaxiom's suggestion as follows:

    'Was no record found?
    dbutils_ISFETableEmpty = .BOF Or .EOF

I do not see where that alone would be enough to warrant removal of DISTINCT.

This morning I thought you meant I needed to set an ADO property some other way than I was setting it in order not to download as many "1" records as are in the table.

I would like to end up with only one record in the RecordSet object. If there is someway to maintain that, then I would consider dropping DISTINCT from the query. Suggestions?

 

[vbaInet]

I was just saying that the each cursor type may behave differently when used with an empty table.

The purpose of DISTINCT is not for returning x number of records. What you need is TOP or a more effecient query to do this, for example using Min().

 

[mdlueck]

What you need is TOP or...

Thanks! So I modified the query to be:

  'Define a query to test if the table is empty
  strSQL = "SELECT TOP 1 * " & _
           "FROM " & strTableName & ";"

Which works in both SQL Server and Access.

That appears to work for a couple of Access FE temp tables already.

 

[MarkK]

I would do ...

SELECT Count(*) FROM tTestData

... with ADO and DAO examples ...

Sub Testing123()
[COLOR="Green"]   'using an ADO connection[/COLOR]
   Debug.Print CurrentProject.AccessConnection.Execute("SELECT Count(*) FROM tTestData").Fields(0)
[COLOR="Green"]   'using a DAO database[/COLOR]
   Debug.Print CurrentDb.OpenRecordset("SELECT Count(*) FROM tTestData").Fields(0)
End Sub

 

[jdraw]

Did you consider ?

strSql = "SELECT Count(*) FROM YourTable"


oops I see lagbolt has posted

 

[mdlueck]

I was specifically avoiding SELECT COUNT(*) as I am not interested in the exact number, merely if the table has records or not. I would thinking taking the time to calculate the total would be more expensive than something that does not need to scan the entire table.

 

[MarkK]

Here's a module to time a few different approaches. DCount() is the slowest and all others are about the same on my machine with linked and local tables...

[SIZE="1"]Private Const TN As String = "YourTableName"
Private Const MAX As Long = 100

Sub Test1234328i45y()
   Dim clock As Single
   Dim i As Long
   Dim j As Integer
   
   Debug.Print MAX & " iterations......."
   
   For i = 1 To 4
      clock = Timer
      For j = 1 To MAX
         GetTest i
      Next
      Beep
      Debug.Print "Test " & i & vbCrLf & Format(Timer - clock, "0.000")
   Next
   
End Sub

Function GetTest(TestNumber As Long) As Long
   Dim rst As DAO.Recordset
   
   Select Case TestNumber
      Case 1
         GetTest = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TN, dbOpenSnapshot).Fields(0)
      Case 2
         GetTest = DCount("*", TN)
      Case 3
         Set rst = CurrentDb.OpenRecordset( _
            "SELECT TOP 1 * " & _
            "FROM " & TN)
         With rst
            GetTest = .EOF Or .BOF
            .Close
         End With
      Case 4
         GetTest = CurrentDb.OpenRecordset(TN, dbOpenSnapshot).RecordCount
   
   End Select

End Function
[/SIZE]

Opening a Snapshot type seems to make more difference in speed that TOP 1 or Count(*).
Cheers,
Mark

 

[Galaxiom]

An index on a field makes a big difference. Aggregates like Max and Count can be retrieved from the index and are orders of magnitude faster than the same functions applied to non-indexed fields. Even the Domain functions are much faster on an indexed field.