RecordSet to a Linked Table 1

[VC2002]

Hi!

I'm attempting to write some simple RecordSet code to identify the latest record of a table (determined by a Date field).

The table is a linked table to an Excel worksheet that contains hundreds of records.

However, everytime I use a RecordCount, it returns "1".

Does it have something to do with ODBC? I'm not too sure.

I'm using Access 2K.

Thank you!

 

[cmmrfrds]

Where and how do you check the record count? Show the vba code that derived the record count.

 

[VC2002]

The VBA code used to derive the reocrd count is as follows:

PublicSub ExecutingARecordSetCount()

Dim db As Database
Dim rec As Recordset
Dim intRecords As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblPrices&quot

intRecords = rec.RecordCount
MsgBox "Number of Records = " & intRecords
rec.Close

End Sub

 

[cmmrfrds]

I believe with a DAO recordset that you need to move to the last record to activate the record count.

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblPrices&quot

rec.movefirst
rec.movelast
intRecords = rec.RecordCount
MsgBox "Number of Records = " & intRecords

 

[VC2002]

Cant thank you enough, thats been a pain in my arse all week!

 

[DonQuichote]

If you need only the last added record, you can specifically ask for it in an SQL subquery:

SELECT * FROM tblPrices WHERE yourDateField=(SELECT Max(yourDateField) FROM tblPrices);

Best regards