Re: Check on empty record set in VBA

Yes, I find no reason to use BOF.

But RecordCount is returned from Static as well as KeySet Cursor
types.

Complicating this, at least here in Canader, is that no matter to what
value we set the CursorType, when we open a recordset using
CurrentProject.Connection or CurrentProject.AccessConnection it is
always opened as adOpenStatic (3), thus always allowing for RecordCount
to be returned.

Perhaps others can confirm this, so that we will not attribute it to
some error here.

(with something like this air code):

Sub test()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open CurrentProject.BaseConnectionString & ";USER
ID=USERID;PASSWORD=PASSWORD"
Set r = New ADODB.Recordset
With r
..CursorType = adOpenForwardOnly '0
..Open "SELECT * FROM FFDBATransactions", CurrentProject.Connection
MsgBox .CursorType
MsgBox .RecordCount
..Close

..CursorType = adOpenForwardOnly '0
..Open "SELECT * FROM FFDBATransactions", c
MsgBox .CursorType
MsgBox .RecordCount
..Close
End With
End Sub

.

Relevant Pages

  • Re: VB6 - ADODB - Strange behavior between IDE & EXE
    ... it mentions that RecordCount may return -1 for unsupported CursorType. ... However, this CursorType usage had been working for years, particularly with Access and Sybase ASE, on Windows 2000 and XP. ... It appears that the rs object itself is now reflecting some other object than that I set in the given LostFocus event. ... If iAns = vbYes Then ...
    (microsoft.public.vb.database.ado)
  • Re: rs.PageCount cannot function
    ... Before open, using stored procedure executed via dynamic sql, cursortype = 3 ... Before open, using stored procedure executed via Command object, cursortype ... So, even though it's giving us the "right" cursortype, the recordcount is ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Embedded SQL within VBA?
    ... If you can't MoveLast, then the provider you are using probably defaults to using an "adOpenForwardOnly" cursortype (you can use the CursorType parameter of the Open method to try to specify the CursorType, but the provider may change the type if it doesn't support the one you asked for. ... If you have an adOpenForwardONly cursor type, try changing it to any of the others - if you need to be able to update the recordset, use adOpenDynamic or adOpenKeyset, otherwise you may be able to use adOpenStatic. ... figure out why .RecordCount is returning -1 here? ...
    (microsoft.public.word.vba.general)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... Microsoft MVP Scripting and ADSI ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)