ADO method for getting highest ID #

[Guest]

I'm using ADO to query an ms Access db. How do I get the highest number in
the first field: ID which is the primary key, and return it to Excel?

 

[Rob van Gelder]

The SQL is:
select max(id) from my_table;

 

[Guest]

I have an open connection and have dumped rs1 to excel. Now I want the max id

I'm getting and error with this

'''' GET THE MAX ID
Dim rs2 As New ADODB.Connection
sql = "Select Max(ID)" & sTable
Debug.Print sql
rs2.Open sql, cn
Sheets("ADO").Range("MaxID").CopyFromRecordset rs2


Here's the Immediate window results for both rs's

Select ID, Lname, Fname, Address, DOB, Street, City, State FROM EMPLOYEES
WHERE (((ID)>0) AND ((Lname)Like's%')) ORDER BY ID

Select Max(ID) FROM EMPLOYEES

The error occurs on the rs2.Open

Any suggestions?

 

[Guest]

sorry I got it:

Dim rs As New ADODB.Recordset

The SQL is:
select max(id) from my_table;