Check if table in Oracle database is empty using VBA and SQL COUNT(*)-CodePudding

I need how to find if a given table is empty in an Oracle database (Oracle 11g) to be specific using VBA inside of PowerAdmin Server Monitor's "run script" feature.

SELECT COUNT(*) FROM table; correctly returns "COUNT(*)" as 0. img of result I need to find a way to check that result if it is 0 or not.

This is a redacted version of the script colleague uses to access the database for slightly different purposes, I prefer if we could continue from this

Dim strConnect
Dim strSQL
Dim adoConnection
Dim adoRecordset

strConnect = "Driver={Oracle in OraClient11g_home1_32bit};" & _
           "Dbq=database;" & _
           "Uid=user;" & _
           "Pwd=password"

strSQL = "SELECT COUNT(*) FROM table;;"

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Open strConnect

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
adoRecordset.Source = strSQL
adoRecordset.Open

[check if query result is the number 0 here]

adoRecordset.Close   
adoConnection.Close

I need something that would look like

If queryresult = 0 then
  SendNotification = True
  Details = "table is empty"
End If

Any help would be appreciated. The more ELI5 the better.

CodePudding user response：

After you execute a query in ADO, the recordset points to the first record, and you can access the fields of that first record per index (0-based).

The result of your count(*)-query is always one row with one column, holding the number of records. So you can access the number of rows with adoRecordset(0) (=first field of first record)

You could create a function to fetch the number of records:

Const strConnect = "..."

Function CountValues(tableName As String) As Long
    Dim strSQL As String
    strSQL = "SELECT COUNT(*) FROM " & tableName

    Dim adoConnection
    Dim adoRecordset
    
    On Error GoTo CountValues_ERROR
    Set adoConnection = CreateObject("ADODB.Connection")
    Set adoRecordset = CreateObject("ADODB.Recordset")
    
    adoConnection.Open strConnect
    adoRecordset.ActiveConnection = adoConnection
    adoRecordset.Source = strSQL
    adoRecordset.Open
    Dim res
    res = adoRecordset(0)
    CountValues = CLng(res)
    GoTo CountValues_EXIT
    
CountValues_ERROR:
    MsgBox "An error occurred fetching data: " & Err.Number & " " & Err.Description

CountValues_EXIT:
    
    If adoRecordset.State <> 0 Then adoRecordset.Close
    If adoConnection.State <> 0 Then adoConnection.Close
End Function

N.B.: If I where you, I would switch to early binding. Add a reference to the ADODB library and use

    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.RecordSet
    
    Set adoConnection = new ADODB.Connection 
    Set adoRecordset = new ADODB.RecordSet