DAO Recordset Type

[Pasquale]

Hello,

In Access 2000 using DAO, I have two mdb files: one containing the tables
only and another one with VBA modules and query and so on.
I've noticed that when I link to tables from one mdb to another, Access
2000 will treat successive Recordsets instances in VBA as Dynasets
"automatically", that is, if I use Recordset.Type the value 2 is
returned, which stands for Dynaset.
I've noticed this because I had data and code all in one mdb file and
used Recordset.Recordcount, which returned the correct number of records
in a table, while, after separating data and code (thus linking the
tables to the code mdb file), the same code would return 1.
In the meantime I have found out that with Dynaset I must use
Recordset.MoveLast before using Recordset.Recordcout - Dynaset doesn't
fetch the entire Recordset by default.
I was wondering however, since this behavior is "automatic" is there no
way to tell Access 2000 to treat a table as "Table type" if it isn't
stored in the same mdb file?


TIA
p

 

[Douglas J. Steele]

Once you've split the database into a front-end and a back-end, your
recordsets are no longer Table type. Table type, as you've found, only works
when everything's in a single MDB file (which, of course, isn't
recommended).

 

[Dirk Goldgar]

In

I was wondering however, since this behavior is "automatic" is there
no way to tell Access 2000 to treat a table as "Table type" if it
isn't stored in the same mdb file?

Not without going around the barn a bit. You can't open a table-type
recordset on a linked table. If you really need to, you can open a
separate Database object on the database that contains the linked table,
then open a table-type recordset from that database object. That way,
you wouldn't be using a linked table. People do this sometimes when
they want to use the Seek method on a foreign table. However, if all
you want to know is the number of records in the table, I think it would
be preferable to just use a linked table and use MoveLast before
checking RecordCount, or else open a recordset on a SELECT COUNT(*)
query.

 

[Marshall Barton]

Hello,

In Access 2000 using DAO, I have two mdb files: one containing the tables
only and another one with VBA modules and query and so on.
I've noticed that when I link to tables from one mdb to another, Access
2000 will treat successive Recordsets instances in VBA as Dynasets
"automatically", that is, if I use Recordset.Type the value 2 is
returned, which stands for Dynaset.
I've noticed this because I had data and code all in one mdb file and
used Recordset.Recordcount, which returned the correct number of records
in a table, while, after separating data and code (thus linking the
tables to the code mdb file), the same code would return 1.
In the meantime I have found out that with Dynaset I must use
Recordset.MoveLast before using Recordset.Recordcout - Dynaset doesn't
fetch the entire Recordset by default.
I was wondering however, since this behavior is "automatic" is there no
way to tell Access 2000 to treat a table as "Table type" if it isn't
stored in the same mdb file?

Linked tables can not be accessed as a table type recordset.

You could open the backend mdb using OpenDatabase and then
use that database object to open a table type recordset, BUT
doing that just to avoid a MoveNext is not worth it. About
the only justification I am aware of is when you have an
overwhelming reason to use the Seek method.