How do I validate if a table named mytb is empty? I would like to decalare the code as
but this is not possible.Code:if mytb.empty = true then
...
Printable View
How do I validate if a table named mytb is empty? I would like to decalare the code as
but this is not possible.Code:if mytb.empty = true then
...
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...