how come ADODB recordset always return NOTHING 1

[jieyin]

Hi, I have a function associated with a form button (Access2000), part of it (see blow) is to find record count in a select query:


Private Sub Register_Click()
....

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "select branch_id from branches", CurrentProject.Connection

MsgBox rs.RecordCount, vbOKOnly
....

I always get -1 returned as rs.RecordCount. I suspect it is the CurrentProject.Connection, how should I set it to open my current database where the form and tables belong?

Thank you.

 

[dysphoric]

RecordCount will return -1 when ADO can't determine the number of records due to the provider or cursortype. If the tables you are selecting from reside in the same database as the form you're running the procedure from, CurrentProject.Connection should work. Try specifying the cursortype for the recordset as a static cursor.

For example:
rs.Open "select branch_id from branches", CurrentProject.Connection, adOpenStatic

 

[jieyin]

Thanks, it works!

I also found another way to do it: reference to DAO and use
currentDB.openReordSet, looks like this way the adOpenStatic is default.