Using "OpenRecordset" on Linked Table Issue

[Gary]

I am using Access 2000-2003 on XP Pro OS.

I have a linked table, and there are 3 records in that linked table.

When I try to check on the number of records it only show 1.

This is my VBA Code:

Dim db As Database
Dim T As DAO.Recordset
Dim x As Integer

Set db = CurrentDb()
Set T = db.OpenRecordset("Missing Formations", dbOpenDynaset)

x = T.RecordCount


I have tried dbOpenTable, dbOpenSnapshot, etc and they all show 1 record.
If I change the table from link to a regular table, I see 3 records.

When I open the linked tabe, I see 3 records. I reference are set correct
for DAO. (i.e. DAO 3.6)

Can someone please help.

Thanks You,


Gary

 

[Dirk Goldgar]

I am using Access 2000-2003 on XP Pro OS.

I have a linked table, and there are 3 records in that linked table.

When I try to check on the number of records it only show 1.

This is my VBA Code:

Dim db As Database
Dim T As DAO.Recordset
Dim x As Integer

Set db = CurrentDb()
Set T = db.OpenRecordset("Missing Formations", dbOpenDynaset)

x = T.RecordCount


I have tried dbOpenTable, dbOpenSnapshot, etc and they all show 1 record.
If I change the table from link to a regular table, I see 3 records.

When I open the linked tabe, I see 3 records. I reference are set
correct
for DAO. (i.e. DAO 3.6)

For a dynaset-type recordset, the RecordCount property is only reliable when
you have moved to the end of the recordset. Effectively, it's a count of
the records accessed so far, not necessarily the total number of records in
the recordset. If you need to know how many records are in the recordset,
try this:

Set T = db.OpenRecordset("Missing Formations", dbOpenDynaset)
With T
If Not .EOF Then
.MoveLast
.MoveFirst
End If
x = .RecordCount
End With

 

[Douglas J. Steele]

RecordCount usually isn't accurate until you've gone to the end of the
recordset.

Dim db As Database
Dim T As DAO.Recordset
Dim x As Integer

Set db = CurrentDb()
Set T = db.OpenRecordset("Missing Formations", dbOpenDynaset)
T.MoveLast
x = T.RecordCount

 

[Banana]

Be aware that a .Movelast can be particularly expensive, especially if
we're opening a large recordset.

If all you want to verify there is at least a record in there, then you
only need to test for .BOF and .EOF (or just .EOF, but I'm a
belt-and-suspend kind of guy):

If Not(T.BOF And T.EOF) Then
... <there's a record in there...>
Else
<It's empty>
End If

OTOH, if you want to get a count of records, it may be faster to open a
COUNT(*) query against the same table with same criteria as the recordset.

 

[Douglas J. Steele]

Actually, I believe RecordCount will always be non-zero if there's any data
in the recordset, and zero if there isn't.