How to trap/handle errors with CurrentDB.Execute?

Question

I have some unbound forms for data entry (discussed at length in my previous post), but I'm not entirely sure how to trap or handle potential errors. I've browsed through several pages of Microsoft's Access and VBA reference docs, but not sure which articles apply to what I'm trying to do. For starters, I don't really understand the difference between ADO and DAO, which one is default (assuming that's what I'm using), or how to pick which one to use. This would help to narrow down which reference material I should be looking at. Some of the articles I've looked at:

• Execute method (ADO connection)
• ExecuteComplete event (ADO)
• Connection.Execute method (DAO)
• Database.Execute method (DAO)

Basically, I just want to have a user-friendly error message in the event that a record isn't entered properly (say if their network connection gets interrupted). Also would be nice to check if the record is entered properly and display a status/confirmation message that the submission went through successfully. Also, if you have a link a to something explaining the differences between ADO and DAO, please share (preferably something that starts with a high-level introduction and then gets into lower-level details).

Answer 1

You can examine the RecordsAffected property of a DAO querydef object to determine how many rows have been affected by an 'action' query.  My public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

includes a Transaction.zip demo which rolls back a transaction if the second of two INSERT INTO statements fails.  If the code is amended as below to remove the transaction processing, but instead examines the RecordsAffected property and returns 'Yes' if this is greater than zero, 'No' otherwise:

Public Sub InsertRows(varFamily1 As Variant, varFamily2 As Variant, varFamilyMember As Variant)

    Dim strSQL As String

    Dim strCriteria As String

    Dim qdfTemp As DAO.QueryDef

    ' insert a new family row if does not already exist

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

        "VALUES(""" & varFamily1 & """)"

    strCriteria = "FamilyName = """ & varFamily1 & """"

    If Not IsNull(varFamily1) Then

        If IsNull(DLookup("FamilyName", "Families", strCriteria)) Then

            CurrentDb.Execute strSQL, dbFailOnError

        End If

    End If

    ' insert a new row into FamilyMembers if values entered in both controls

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

        "VALUES(""" & varFamily2 & """,""" & varFamilyMember & """)"

    If Not IsNull(varFamily2) And Not IsNull(varFamilyMember) Then

        Set qdfTemp = CurrentDb.CreateQueryDef("", strSQL)

        On Error Resume Next

        qdfTemp.Execute dbFailOnError

        On Error Goto 0

        ' confirm that new family member row was inserted

        If qdfTemp.RecordsAffected > 0 Then

            Forms("frmTransactionDemo").txtRowInserted = "Yes"

        Else

            Forms("frmTransactionDemo").txtRowInserted = "No"

        End If

    End If

End Sub

In this example we could of course simply examine the Err.Number property, but if for instance we wanted to determine if a known number of rows were inserted, examining the RecordsAffected property would enable us to do this.

Answer 2

What you seemed to have failed to grasp is how error handling works. Its rather commonplace. You start with an OnError GoTo statement at the top of your procedure. The Goto is a label at the end of your procedure. Within the Error handling label, you identify the error, and provide code to manage what to do when the error occurs. This usually takes the form of a SELECT CASE statement:

Select Case err.Number

Case x

   Your code

...

End Select

The dbFailOnError parameter of .Execute statement, tells Access that when an error occurs, to go to the label identified in the OnError Goto statement. From there your error handling takes over.

Answer 3

They are part of my global error handler.

The structure of any error handler is the same as illustrated.

Here's another example with the same overall structure.

Private Sub cmdYourControlNameGoesHere_Click()

Dim AValueDefinedIntheProcedure as String

On Error GoTo errHandler

Set db = CurrentDb

 AValueDefinedIntheProcedure  = Me.ControlNameGoesHere

CurrentDB.Execute "Update tblYourTableNameGoesHere SET YourFieldNameGoesHere=""" & AValueDefinedIntheProcedure   & """" , \_

    dbFailOnError + dbSeeChanges 

Cleanup:

On Error Resume Next 

Set db = Nothing

exitProc:

Exit Sub

errHandler:

 MsgBox Err.Number & " " & Err.Description, vbOkOnly, "An Error Occurred"

Resume Cleanup

 Resume

End Sub

Answer 4

I appreciate your attempt to be snarky, but my question is specifically about errors involving CurrentDB.execute. I can handle basic errors in VBA. Errors involving a SQL insert or update statement are more niche. I know how to trap errors with DoCmd.RunSQL and warning messages turned off, but my impression is that CurrentDB.Execute is the "better" method, so trying to figure out how to handle this properly. I've browsed some other forums via Google search and found recommendations to use the parameter constant dbFailOnError, but couldn't find how exactly to trap such error. Maybe I should've included this in my original post.

Regardless, if you don't have an answer or helpful reply, why bother commenting?

Answer 5

Hmm. You dived into the deep end, bypassing bound forms and opting for unbound forms. And now you find you have to swim in the deep end where you are in over your head, so to speak.

It's possible that you will need ADO or DAO, but it's also possible you'll use neither extensively. It depends a lot on how you implement your unbound form updates. There is more than one way to do it.

DAO is generally more commonly used. ADO has advantages, though, in specific situations, such as connecting to a SQL Server back end.

Rather than others searching for, and curating, links for you, you could search the internet yourself, using terms like "DAO vs ADO". You'll get a ton of links. Some will suit you better than others, i.e. they'll match your preferred method of learning better. Some will offer suggestions about when to use ADO and when to use DAO. Some will be more "how to implement".

And don't forget to search YouTube for videos.

Data validation is a whole topic in itself. Pre-save validation is one part, post-save confirmation another.

By avoiding one of the greatest strengths of Access--bound forms--you have assumed by default the role of advanced software developer. Unfortunately, that expertise comes at a cost. That cost is a considerable amount of study. Best of luck to you.