Validating a table

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

Code:

---

if mytb.empty = true then
...

---

but this is not possible.

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...

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

Oops - I forgot the ADO connection; try this;

Code:

---

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

---

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

?

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...