Return values/codes from DoCmd.RunSQL

Question

Hello, all. I have a VBA script that runs when a user presses a button on a form. Part of that script executes a DoCmd.RunSQL to insert a new record into one of my tables. In the next part of the script, I will be executing a similar command to insert a record into a different table, but I don't want to proceed with the 2nd insert if the first one failed for some reason. Does DoCmd.RunSQL return anything that would let me know if it succeeded or failed? If not, how should I go about checking for success?

Thanks,

-Chris

Answer 1

Yes DoCmd.RunSQL should throw an error if the SQL Statement doesn't go through. If you use a DoCmd.SetWarning False prior to running the RunSQL it will suppress error messages.

Alternative you can use:

CurrentDB.Execute strSQL, dbFailOnError

It will also throw an error. You can use Error Trapping to trap the error and display it, rather than run the second SQL statement.

Answer 2

>>If not, how should I go about checking for success? <<

The usual approach would be to execute both SQL statements within a Transaction.  The following is a simple little procedure to illustrate this:

Public Sub TranactionDemo()

    ' this demo procedure uses two empty tables, Families and FamilyMembers

    ' related on the FamilyName columns.

    ' the second SQL statement attempts to insert a row for family name 'Black'

    ' into FamilyMembers for which there is no row in Families, violating

    ' referential integrity and causing the transaction to be rolled back, so

    ' no row is inserted into either table

    On Error GoTo Err_Handler

    Dim strSQL As String

    Dim strCriteria As String

    DBEngine.BeginTrans

    strSQL = "INSERT INTO Families(FamilyName) " & _

        "VALUES(""White"")"

    CurrentDb.Execute strSQL, dbFailOnError

    strSQL = "INSERT INTO FamilyMembers(FamilyName, Firstname)  " & _

        "VALUES(""Black"",""John"")"

    CurrentDb.Execute strSQL, dbFailOnError

    DBEngine.CommitTrans

    ' after the transaction has been rolled back change the second SQL statement to insert

    ' a row into FamilyMembers for John White, and run the procedure again.

    ' the transaction should now be committed and rows inserted into both tables

Exit_Here:

    Exit Sub

Err_Handler:

    MsgBox Err.Description

    DBEngine.Rollback

    Resume Exit_Here

End Sub

Answer 3

Thanks, Ken. What does strCriteria do? I don't see it initialized or used anywhere other than its declaration in the Dim statement.

I think it's left over from an earlier version of the code, and got accidentally left in place.  It's so many years since I originally wrote it, that I can't remember what it was for.

Answer 4

Thanks, Ken. What does strCriteria do? I don't see it initialized or used anywhere other than its declaration in the Dim statement.

Thanks,

-Chris