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

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.

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