Record count in VBA returns -1

[jbreen33]

Hello,

I am attempting to count the number of records returned by a query in VBA. When my query returns less than 100 records I get a correct record count. Anything over 99 and I get a -1 for my record count. Here is my code:

'start code
strConnect = "ODBC;DSN=nip_profile_fabrics_local;UID=;PWD=;Database=fabric_nip_profile.mdb" 'Connection String
strSQL = "SELECT * FROM [Compression Testing];"

Set wrkODBC = DBEngine.CreateWorkspace("NewODBCDirect", "", "", dbUseODBC) 'Create ODBCDirect Workspace
Set conFabric = wrkODBC.OpenConnection("test", dbDriverNoPrompt, True, strConnect) 'Open connection

Set rstFabric = conFabric.OpenRecordset(strSQL, dbOpenSnapshot) 'Open recordset

'need to run through the records to get a good count
rstFabric.MoveLast
rstFabric.MoveFirst

ScrollBar1.Min = 1
ScrollBar1.Max = rstFabric.RecordCount
ScrollBar1.Value = rstFabric.AbsolutePosition + 1

'end code

Help!
Thanks, John

[Dave Sell]

Not sure about VBA but the ASP Recordset objects do not report RecordCount correctly either. I just traverse the entire recordset and count the iterations.

[jbreen33]

Thanks Dave. I think that is what I may try, unless someone has some other advice.

[RobDog888]

Since you are using DAO, dbOpenSnapshot does support the
recordcount property. Just as you describe that it works up to 99.
If it didn't support the property then you would always get -1 no
matter how many records are returned. I think you need to
specify the rest of the parameters for OpenRecordset.

From MSDN:
For Connection and Database objects:

Set recordset = object.OpenRecordset (source, type, options, lockedits)

You may need to specify the options and lockedits.

You may also use the SQL Count function to determine the
number of records returning in the recordset.

Also, recordcount is not supported if the source of the query is a linked table.

[Dave Sell]

I should then clarify - I was speaking of ADO Recordsets.

[RobDog888]

Yes, if it was ADO then you would of been correct.

[techgnome]

I have never had an experience where the .RecordCount property wasn't supported.

Now.... depending on the cursor location (client vs. server) and the record type (keyset, dynaset, etc) you will get -1 for your recordcount.... typically that's because ADO doesn't know how many records there are - yet. If you get a client side cursor dataset, recordcount will be accurate. If using a server side, and your record type supports it (a forward only one won't be able to do this) you can "jiggle" it to get the recordset by doing a .MoveLast then a .MoveFirst. This will cause ADO to move to the last record, after which the .RecordCount will be filled right.

TG

[techgnome]

jbreen33 - to further expand on that....
The reason you get an accurate number below 99 may have to do with page caching. The records are small enough that 99 of them can fit on to a page in memory. So when it returns 10 records, they are all there on the same page, and ADO can see there are 10 and returns 10 as the record count. When the size of the records exceed the page size, then ADO doesn't know specificaly how many records there are, unless you tell it to send all pages (by using a client side cursor) or by "flipping" to the last page with a .MoveLast. At that point, it knows how many pages and records there are.

TG