[RESOLVED] Getting ODBC-linked RecordCount without using MoveLast

[klempie]

I'm opening an ODBC-linked recordset from an Access database. When it opens the recordset the RecordCount = 1. MoveLast obviously pulls the entire recordset over from the server into the database which resets RecordCount to the correct value. But is there a cleaner way of doing this? Are there any options that can be used in the OpenRecordset function that can do this when the recordset is opened?

[klempie]

Sorry can a mod please move this thread to the Database Development forum. I realise it's in the wrong place.

[sabbath69]

Generally, if you use either of:- Static or Dynamic Cursors then the actual recordcount will be returned.

[klempie]

I'm opening it as a snapshot and it's still only returning RecordCount = 1.

[leinad31]

Use an aggregate version of the query, e.g. COUNT, to have an idea how many records will be returned before executing query itself.

[klempie]

Thanks for the suggestion, but that seems to be even less clean and more inefficient than using MoveLast.

[si_the_geek]

The efficiency depends on several factors (usually in favour of a separate query), but it does take more code.

While it is for ADO rather than DAO, you might find something useful in the article Why does Recordcount sometimes equal -1? from our Database Development FAQs/Tutorials (at the top of this forum)

Sorry can a mod please move this thread to the Database Development forum. I realise it's in the wrong place.

Done.

[leinad31]

Thanks for the suggestion, but that seems to be even less clean and more inefficient than using MoveLast.

So your definition of efficiency has nothing to do with network usage ... Then why do you need recordcount in the first place?

[klempie]

The efficiency depends on several factors (usually in favour of a separate query), but it does take more code.

While it is for ADO rather than DAO, you might find something useful in the article Why does Recordcount sometimes equal -1? from our Database Development FAQs/Tutorials (at the top of this forum)

Done.

Thanks si. A useful link which I will bookmark for future use. Unfortunately JET returns 1 for RecordCount so it would appear that it only populates the recordset with the first record and you have to use a Move method to get the rest of them over. I don't know how the internals of JET work but that is what the behaviour is.

[klempie]

So your definition of efficiency has nothing to do with network usage ... Then why do you need recordcount in the first place?

Leinad...sorry. Once again I have not expressed myself clearly enough.

The code uses the recordset regardless of what the recordcount is BUT the value of RecordCount determines the execution path. It just seems to me that executing an aggregate statement as well as the query is a bit unnecessary, but I stand to be corrected.

In any event I have sorted out the issue by writing a wrapper which looks something like

Public Function OpenRecordset(query as String, [Options]) As Recordset

   Dim rs as Recordset
   Set rs  = db.OpenRecordset(query, [Options])
   rs.MoveLast
   rs.MoveFirst

   OpenRecordset = rs
End Function

Not ideal but at least it only appears in the code once this way.