Test Table for 0 Records

[gnosis13]

I am looking for a simple way to test a table to see if it contains records. I have a user that I support who has some VBA code he has written to update records in a table and he sometimes gets debugged because the table can be empty. The code needs to be relatively reusable and simple. The current solution uses error trapping that is confusing (goto).

Suggestions?

A+, N+, MCP

 

[mndrlion]

Function IsTableEmpty(strTableName As String) As Boolean

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strTableName)

IsTableEmpty = rst.BOF And rst.EOF

rst.Close

End Function

 

[chris153]

Hi,

You can use ADO :

Set rs = New ADODB.Recordset
rs.Open "Table_Name", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
MsgBox (rs.RecordCount)
rs.close

Make sure you've referenced the ADO Library

 

[glalsop]

You could use the DCount function on the table's primary key field:

DCount("PrimaryKeyField","TableName")

-Gary

 

[gnosis13]

Thanks, I'm really liking that function...

A+, N+, MCP