dao in access

[Thai]

Hello, I have a question about dao recordsets in MS Access's VBA. Here is my code:

Dim db As DAO.Database
dim rs as DAO.Recordset

Set db = DBEngine.Workspaces (0) (0)
set rs = db.OpenRecordset("SELECT * FROM Table1")

Debug.Print rs.RecordCount

rs.RecordCount ALWAYS returns 1 no matter how many records there are. I've tried to open it in SnapShot, Dynamic, Dynaset but I still get the same problem. I tried to open it in dbOpenTable mode but I get an error and it won't open the recordset. Any solutions?

Thanks,
Thai

[David Laplante]

.Recordcount works strangely. If you just populated your recordset, you are on the first record and it will return 1. Before looking the .recordcount, you should do .movelast and then .recordcount.

This should work.

(strange because I THINK that if you do .movelast, then .movefirst, then .recordcount, it'll give you the good number of records you look for)

[HeSaidJoe]

Any move (first,last)before the
debug.print rs.recordset
should give recordcount


[Edited by HeSaidJoe on 06-29-2000 at 12:33 PM]

[BruceG]

I beleieve the rules for DAO recordsets are as follows:
If you open the recordset as table, as in:
set rs = db.OpenRecordset("Table1", dbOpenTable)
Then rs.RecordCount will return the actual number of records in the table.

But -- if you open the recordset as a dynaset or snapshot, as in:
set rs = db.OpenRecordset("Select * From Table1", dbOpenDynaset)
Then rs.RecordCount will return the number of records visited! (And when you first open a dynaset, only one record has been visited, so RecordCount will always be 1.) So as the others mentioned, you must do a MoveLast to force Jet to "look at" or "visit" all the records and return an accurate Recordcount.

P.S. - If you omit "dbOpenTable" or "dbOpenDynaset", Jet will make certain assumptions: If you give a table name, it will assume "dbOpenTable"; if you give a SQL statement, it will assume "dbOpenDynaset".

[Thai]

Thanks everyone for the help..

BruceG: When I use an SQL statement and specify dbOpenTable, it gives me an error, why is that?

Thanks,
Thai

[BruceG]

When you open a recordset with "dbOpenTable", Jet expects the first argument to be the name of a single object (i.e. a table in the database). You can open a single table with dbOpenDynaset, but as you've seen, the reverse is not true (you can't use a SQL statement with "dbOpenTable").

As you may or may not know, there are advantages and disadvantages with each type. (Example: with tables you can set the Index property and use the Seek method for faster searching; with dynasets, you can use SQL to perform joins and you can use the Find methods).