One way to test for an empty recordset is to see if the EOF and BOF properties are both True. If not the recordset contains at least one record.
Code:
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSP As String
Set cnn = "{connection string}" ' You must define the connection string yourself for SQL Server data
Set rst = New ADODB.Recordset
strSP = "{name of the stored procedure}"
With rst
.Open strSP, cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProcedure
If .BOF And .EOF Then
MsgBox "Recordset contains no data"
Else
While Not .EOF
{whatever else you want to do if records are present}
Wend
EndIf
.Close
End With
Set rst = Nothing
Set cnn = Nothing
You may have to use different parameters for the .Open method. adOpenForwardOnly is for a single pass recordset and adLockReadOnly is for a read-only recordset.
Rand
Trap for empty recordset in a SP
