RecordCount property not work when using ODBC tables in Access 200

[Guest]

I wrote code in a Module to retrive the data from Access 2003 in which the
tables are linked through ODBC. Before I get the data, I need to know the
total number of records. The snapet of my code is like this:

set myDB =CurrentDB
SQLString="select * from Employer"
set RecordSetDailyUpload = myDB.OpenRecordset(SQLstring, dbOpenDynaset)
RecdCounter=RecordSetDailyUpload.RecordCount
debug.print RecdCounter
RecordSetDailyUpload.MoveFirst
Do While Not RecordSetDailyUpload.EOF
EmployerName=RecordSetDailyUpload.fields(0)
debug.print EmployerName
....
.....
RecordSetDailyUpload.movenext
Loop
RecordSetDailyUpload.close

The problem is that it prints out 1 in immediate windows no matter how many
rows output. However, the while loop will go until the end of the record
sets(i.e. I can obtain many employerNames).

You are so appeciated if you could suggest any clues and solutions. Please
note the tables in database are linked tables through ODBC.

Jinshui

 

[George Nicholson]

An excerpt from Help on the DAO Recordcount property (OpenRecordset returns
a DAO recordset so that's what you're working with):
*********************
The RecordCount property doesn't indicate how many records are contained in
a dynaset-, snapshot-, or forward-only-type Recordset object until all
records have been accessed. Once the last record has been accessed, the
RecordCount property indicates the total number of undeleted records in the
Recordset or TableDef object. To force the last record to be accessed, use
the MoveLast method on the Recordset object. You can also use an SQL Count
function to determine the approximate number of records your query will
return.

Note Using the MoveLast method to populate a newly opened Recordset
negatively impacts performance.
********************

HTH,