Validating a table

[hpl]

How do I validate if a table named mytb is empty? I would like to decalare the code as

if mytb.empty = true then
...

but this is not possible.

[Buzby]

You would have to either a) open a recordset of the contents of the table and check the .RecordCount property or b) run a

SELECT COUNT(*) AS RecordCount FROM MyTB

into a recordset and check the RecordCount field

Why not make a quick function;

Function EmptyTable(TblName As String) As Boolean
Dim tmpRS As New ADODB.Recordset
TmpRs.Open "SELECT COUNT(*) AS RecordCount FROM "+TblName
EmptyTable = Tmprs.Fields("RecordCount")=0
Tmprs.Close
End Function

Then you can use

If EmptyTable("MyTB") Then...

[hpl]

OK, but an error occurs running your code. It's an RTE 3709.

[Buzby]

Oops - I forgot the ADO connection; try this;

Function EmptyTable(TblName As String, ADOConn as ADODB.Connection) As Boolean 
Dim tmpRS As New ADODB.Recordset 
TmpRs.Open "SELECT COUNT(*) AS RecordCount FROM "+TblName, AdoConn, AdOpenStatic, AdLockReadOnly
EmptyTable = Tmprs.Fields("RecordCount")=0 
Tmprs.Close 
End Function

[hpl]

Ok, what is then the statement for
If EmptyTable("MyTB", ???) Then...

?

[Buzby]

You need to pass it a valid ADO connection;

so if you had done this

Dim MyConn AS New ADODB.Connection
MyConn.Open ".........."

You would use

If EmptyTable("MyTB", MyConn) Then...