Question about ...Execute "Select... 1

[Dmcniell]

I want to use the following SQL to find the maximum id in a table. This works fine, but how do I reference the resultset in my code? I want to be able to use max(uid) in other statements, but I don't know how to get to it...

CurrentProject.Connection.Execute "Select max(uid) from [Donor]"

Thanks for your help.........

 

[Dimandja]

Try:

CurrentProject.Connection.Execute "Select max(uid) into someInt from [Donor]"

Where someInt is a integer variable.

 

[Dmcniell]

Thanks for that tip, but it doesn't work.

When the 'into' variable is in the SQL statement, it doesn't populate a declared variable inthe sub.

Any other suggestions?

Thanks!

 

[cmmrfrds]

Normally, the execute method is for action queries, such as, Update, Insert, and Delete. Use the Open Method of the Recordset object for select queries.

Dim rs as new adodb.recordset
Dim mySQL as String

mySQL = "Select max(uid) from [Donor]"
rs.Open mySQL, CurrentProject.Connection, 3, 3

If rs.EOF then
msgbox " no records '
else
Debug.Print rs(0)
End IF

 

[PHV]

And what about this ?
MsgBox DMax("uid", "Donor")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244