Why is this record count returning 1?

Question

In my MS Access DB I'm running a query in VB that should return two records. When I run it in SQL I get two records but when ran from VBA I get two. Here is the code in the SQL view which gets two records:

SELECT *
FROM tblWebMeetingData
WHERE [Last Name] LIKE 'Marx';

And when I call this in VBA like so:

SQL = "SELECT * FROM tblWebMeetingData WHERE [Last Name] LIKE 'Marx';"
Set rst = CurrentDb.OpenRecordset(SQL)  
MsgBox ("Number of records: " & rst.RecordCount)

I get one record for number of records. Isn't record count suppose to count all the records returned from a SQL statement or table? What is it I'm doing wrong here?

Thanks

Mendicant

Is it possible you are querying two different databases? – Goodsell

Answer 1

DAO doesn't retrieve the entire result set at once for all but the simplest queries (performance optimisation). To force a complete retrieval and a valid recordcount use rst.MoveLast after opening the recordset and before retrieving rst.RecordCOunt.

Meingoldas

Answer 2

for recordcount property you need to set cursor type as

RS.Open SQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText

if it can not execute then you need to use ADOVBS.INC as include file in the top of your page you can download and use it from the internet

Subjunction

Answer 3

Above answers are all good. Microsoft's documentation on RecordCount has this:

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset–, snapshot–, or forward–only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Note

Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.

Sylas