2003: Any Rows In My ADODB Recordset?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ya'll,
Running a few thousand strSQL Recordsets, how can I see the ones that are returned with no records? Debug.Print just skips over them w/o error . . ..
 
how can I see the ones that are returned with no records?
Click to expand...


If Left(strSQL, 6) = "SELECT" Then
' it's a recordset

Else
' it's a executable INSERT or UPDATE

End If

HTH


Tim F
 
It might also be a parameter query, in which case you need to go a bit
deeper.

If Left(strSQL, 6) = "SELECT" Then
' it's a recordset
ElseIf Left(strSQL, 10) = "PARAMETERS" Then
If Left(Mid(strSQL, Instr(1, strSQL, ";") + 1), 6) = "SELECT" Then
' it's a recordset
Else
' it's a executable INSERT or UPDATE
End If
Else
' it's a executable INSERT or UPDATE
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
It might also be a parameter query, in which case you need to go a bit
deeper.
Click to expand...

Touche!

Actually, since a make-table query is strictly executable but still begins
with SELECT, then we should be testing for an INTO clause coming before the
FROM one. This is starting to sound like a full-blown LISP application!

All the best


Tim F
 
Jim Shores said:
Hi ya'll,
Running a few thousand strSQL Recordsets, how can I see the ones
that are returned with no records? Debug.Print just skips over them
w/o error . . ..
Click to expand...

Jim, I'm not sure that Tim and Graham have answered your question.
Assuming that your recordsets are being opened on SELECT queries, are
you wondering how to tell whether a recordset is empty? If so, you
should be able to check either ...

If rst.RecordCount = 0 Then
' the recordset is empty
End If

or ...

If (rst.BOF = True) And(rst.EOF = True) Then
' the recordset is empty
End If

I don't work with ADO much, so I'm not sure whether RecordCount will be
0 for an empty recordset regardless of cursor type.
 
You know, Dirk's right Tim. We were too concerned about the technicalities
that we forgot to explain why were doing so.

Jim, what Tom and I were trying to say was that only SELECT queries will
return records. So by checking the SQL statement itself, you can eliminate
those that cannot return records.

Also (I don't know if you want to go this way), another way is to execute
the queries against an ADO connection or command object. The ADO Execute
method returns a RecordsAffected parameter.
Dim RA As Long
Dim cmd As ADODB.Command
'- - -
cmd.Execute RA

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Jim, I'm not sure that Tim and Graham have answered your question.
Assuming that your recordsets are being opened on SELECT queries, are
you wondering how to tell whether a recordset is empty?
Click to expand...

Fair cop. Actually, my preference is for (a) getting the server to do as
much work as possible and (b) saving the network from traffic [1]. The
following seems to work albeit with limited testing:

strCount = "SELECT COUNT(*)=0 AS IsEmpty FROM (" & strSQL & ")"

Set rs = conn.Execute(strCount)
fRecordSetIsEmpty = rs.Fields("IsEmpty")
rs.Close

[1] Alright: I know that Access-Jet will pass whole pages up the network
regardless, but one day we will all be working with proper servers...


B Wishes


Tim F
 
Back
Top