Excel VBA SQL - getting last ID


I am trying to get the last ID entered using the code below
and at the line   varID = .Execute()
the variable varID seems to be blank even though there is no error
How do I get the integer value of the last ID ?

Public Sub oAppend(ByVal oDate As Date, ByVal oText As String, ByVal oNumber As Single)
    Dim oSQL As String
    On Error GoTo EH
    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=,1444;Network Library=DBMSSOCN;Initial Catalog=test;User ID=mu;Password=ct3;"

    Set cmd = New ADODB.Command

    'Check last ID ---------------------------
    Dim oSQL_lastID As String
    oSQL_lastID = "SELECT MAX(ID) FROM Table1"

    Dim varID As Variant
    Dim intID As String
    With cmd
        .CommandText = oSQL_lastID
        .CommandType = adCmdText
        .ActiveConnection = con
        varID = .Execute() 'varID is blank
    End With
    intID = CStr(varID) 'Error here

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
As I said http:Q_27301456.html#a36516694 the .Execute() is returning a Recordset; therefore, you have to use it as such.

i.e., try: Set rs = .Execute()
varID = rs(0)
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.