Access Linked Tabled Treated Differently?

[Frank Johnson]

I am pretty new to VBA and I have a quick question. When linking and
external table into access from another database, I cannot seem to get
counts to work on it. I have 1500 rows in that table, but when I print a
count it only shows 1. Why? I do the same thing on a local table and
everything counts up fine?

By the way, the tables are linked using file -> external data -> link tabes

Anyone happen to know the answer to this?

 

[Cheryl Fischer]

If you are getting your counts of records in your linked tables by using the
Recordset object's RecordCount property, you will need to do a
Recordset.MoveLast to get the count.

 

[Dirk Goldgar]

I am pretty new to VBA and I have a quick question. When linking and
external table into access from another database, I cannot seem to get
counts to work on it. I have 1500 rows in that table, but when I
print a count it only shows 1. Why? I do the same thing on a local
table and everything counts up fine?

By the way, the tables are linked using file -> external data -> link
tabes

Anyone happen to know the answer to this?

Expanding on Cheryl's answer: if you're getting your counts by opening
a recordset on the table -- e.g.,

Set rs = CurrentDb.OpenRecordset("TableName")
Debug.Print rs.RecordCount

-- then the difference is that by default you get a table-type recordset
for local tables, but a dynaset-type recordset for linked tables. In a
table-type recordset, the RecordCount property is accurate as soon as
the recordset is open, but in any other type of recordset the
RecordCount property is not accurate until the last record in the
recordset has been visited. Essentially, the RecordCount property in
such cases represents a count of "the records we've seen so far", so on
open it only represents the first record in the recordset.

You can open a dynaset-type recordset on a local table by specifying the
RecordsetType option --

Set rs = CurrentDb.OpenRecordset("TableName", dbOpenDynaset)

-- but you can't open a table-type recordset on a linked table, except
by opening the database containing the table and opening the recordset
from that database. And of course, you can't open a table-type
recordset on any query.