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?

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"gocush" /delete wrote in message
...
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?

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?


"Rob van Gelder" wrote:

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"gocush" /delete wrote in message
...
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?

sorry I got it:

Dim rs As New ADODB.Recordset

"Rob van Gelder" wrote:

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"gocush" /delete wrote in message
...
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?