The only means I know of to determine if the UPDATE succeeded is to evaluate the numer of records affected by executing your query. If 0 records are affected, the record doesn't exist. Your code can then branch to an INSERT statement.
Problem is, the RunSql method is pretty limited, and doesn't return the number of records affected. An ADO Command object, however, does. I put together a generic wrapper function, ExecuteDMLQuery, that accepts any Jet SQL data manipulation language statment. A boolean value is returned indicating success or failure of the action. You'll also need to set the return value to False in the error handler, should an error occur.
Hope it helps,
Bob
Sub Main()
Dim strSQLCommand As String
strSQLCommand = "UPDATE Orders Set Amount = 5 WHERE OrderID = 2"
' if Order# 2 doesn't exist, the INSERT statement gets processed.
If ExecuteDMLQuery(strSQLCommand) Then
MsgBox "UPDATE succeeded"
Else
' execute INSERT instead
strSQLCommand = "INSERT INTO Orders VALUES (2, 5);"
If ExecuteDMLQuery(strSQLCommand) Then
MsgBox "INSERT succeeded"
End If
End If
End Sub
Public Function ExecuteDMLQuery(strCommandText) As Boolean
On Error GoTo ErrorHandler
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim lngRecordsAffected As Long
On Error GoTo ErrorHandler
Set cnn = CurrentProject.Connection
' Create command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strCommandText
' If 0 records returned, the record does not exist.
cmd.Execute lngRecordsAffected
' set boolean flag to indicate update success.
If lngRecordsAffected <> 0 Then
ExecuteDMLQuery = True
Else
ExecuteDMLQuery = False
End If
' clean up
cnn.Close
Set cnn = Nothing
Exit Function
ErrorHandler:
' set boolean flag to false if error encountered.
ExecuteDMLQuery = False
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
Set cnn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Function
|
