Excel VBA SQL statement to get last ID

Hi

In Excel VBA I am trying to get the last ID entered in a SQL table
How do I modify the following code to assign the
results of my SELECT statement to a string variable
called sMyLastID?
Dim oSQL As String
    
    On Error GoTo EH
    
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.237,1441;Network Library=DBMSSOCN;Initial Catalog=test;User ID=murbhhh;Password=chfff;"

    Set cmd = New ADODB.Command
    
    'Check last ID ---------------------------
    Dim oLastID As Variant
    oLastID = "SELECT MAX(ID) FROM Table1"
    With cmd
        .CommandText = oLastID
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
    '------------------------------------------

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AgeOfEmpiresConnect With a Mentor Commented:
I typically use a command object if executing a stored procedure or a query that doesn't return info.  Otherwise I use a recordset to access the data.  I've not included any error checking for failed connection, incorrect query statements, etc.

Sub test()
  Dim sMyLastID As String
  Dim con As ADODB.Connection
  Dim rs As ADODB.Recordset
  
  Set con = New ADODB.Connection
  con.Open   ----your connection string here----
  
  Set rs = New ADODB.Recordset
  
  rs.Open "Select MAX(ID) From Table1", con, adOpenForwardOnly, adLockReadOnly
  If Not rs.EOF() Then
    sMyLastID = CStr(rs.Fields(0).Value)
    MsgBox "Max ID = " & sMyLastID
  Else
    MsgBox "Error processing query"
  End If
  
  rs.Close
  con.Close
  Set rs = Nothing
  Set con = Nothing
  
End Sub

Open in new window

0
 
Friman001Commented:
I could help easier if this was C#, but from what I see, you should just be able to say oLastId.ToString() and that should be holding the last ID.
0
 
Kevin CrossChief Technology OfficerCommented:
You want to assign it to variable during the execute.
e.g.,
intID = .ExecuteScalar()

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
Sorry, it has been awhile since I messed with ADODB. You are using the Command object, so there is only Execute. Principle is the same, but looks more like this:
oLastID = "SELECT MAX(ID) AS ID FROM Table1"
With cmd
    .CommandText = oLastID
    .CommandType = adCmdText
    .ActiveConnection = con
    Set rs = .Execute
End With

Open in new window


Where rs is an ADODB.Recordset, so you can get at the ID (note: I added an alias to your SQL string) like rs("ID"). You could use the column index since there will be only one. The index array starts at 1 if I am not mistaken so rs(1) but can always try rs(0). *smile*
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
 
AgeOfEmpiresCommented:
Glad I could help
0
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.