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?
Call oAppend(Now, "tester", 2)
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"
.CommandText = oSQL
.CommandType = adCmdText
.ActiveConnection = con
'+++++ CHECK WHETHER ENTRY SUCCESSFUL ++++++++++++++++++++++++++++++++++++++
Set cmd = Nothing
Set con = Nothing