Excel VBA - checking whether SQL append successful


I am using the following Excel VBA code to append a record to a SQL database
I want to check whether the entry was successful by using a Select statement at
the point in the code where it says   '+++++ CHECK WHETHER ENTRY SUCCESSFUL
I am a little confused how to do this. There is an ID column which is automatically generated so I can't use this. Is there a way of selecting the last append to the database? Or should I do a selcting that includes all three fields entered?
Sub A()
    Call oAppend(Now, "tester", 2)
End Sub

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=196.2550.423.227,1444;Network Library=DBMSSOCN;Initial Catalog=test;User ID=murbroscave;Password=yabbadabbaidiot;"

    Set cmd = New ADODB.Command
    oSQL = "Insert Into Table1 ([oDate],[oText], [oNumber]) Select '" & oDate & "' as Expr1, '" & oText & "' as Expr2, " & oNumber & " as Expr3"
    With cmd
        .CommandText = oSQL
        .CommandType = adCmdText
        .ActiveConnection = con
    End With
    '+++++ CHECK WHETHER ENTRY SUCCESSFUL ++++++++++++++++++++++++++++++++++++++
    Set cmd = Nothing
    Set con = Nothing
    Exit Sub
    MsgBox Err.Description
End Sub

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
PS You can also, I think do a SELECT @@IDENTITY query to get the last added ID.
Rory ArchibaldCommented:
If you are only doing one record, why not open a recordset, add a record, populate it then Update the rs?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks for the help
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.