SQL Update and error capture?

[thebatfink]

Hi, I am using a button to trigger an Update SQL statement. I'm worried about situations such as, it gets half way through and encouters an error updating a record - how I could recover from that.

I'm assuming that it attempt to perform the update looking for records that may cause an error 'before' actually performing the update on the records and that is the prompt saying 'you are about to update x records' etc. Is that correct?

If so, is it possible to supress this prompt and handle it in the VBA, ie, if no errors, just perform the update with no prompt, if there are errors then cancel the update for all records.

I want to avoid a user selecting to perform the run but not all the data being updated in one go. What I have so far is below (incidently, I inherited the database, I DID NOT name tables and fields with spaces and such in them!! )..

Private Sub ButtonUpRevision_Click()
Dim iReply As Integer
Dim dateString As String
dateString = DATE
iReply = MsgBox(Prompt:="Are you SURE you want to up revision and date" _
& vbCr & vbCr & "THIS CAN NOT BE UNDONE!!!", Buttons:=vbYesNo, title:="UP REVISION")
If iReply = vbYes Then

strSQL = _
"UPDATE [PRODUCT DATA] " & _
"SET [DATE] = """ + dateString + """, [ISSUEREVISION] = IIF([ISSUEREVISION] = ""N/A"",1,[ISSUEREVISION] + 1) " & _
"WHERE [R NAME] = """ + Me.R_NAME + """ AND MID([ITEM NO],1,1) <> ""O"" "

DoCmd.RunSQL strSQL

strSQL = _
"UPDATE [F2 PRODUCT DATA] " & _
"SET [DATE] = """ + dateString + """, [ISSUEREVISION] = IIF([ISSUEREVISION] = ""N/A"",1,[ISSUEREVISION] + 1) " & _
"WHERE [R NAME] = """ + Me.R_NAME + """ AND MID([ITEM NO],1,1) <> ""O"" "

DoCmd.RunSQL strSQL

MsgBox "Up revision and dating completed", vbInformation, title:="COMPLETE"

Else
MsgBox "Canceling", vbInformation, title:="CANCELLED"
Exit Sub
End If
End Sub

 

[PeterF]

By replacing the DoCmd.RunSQL by CurrentDB.Execute the prompt for executing the query is gone.
This can also be done by using docmd.SetWarnings false/true but you need to make sure that it's set back on if your code fails by using error trapping.

To prevent the query from partly running use the dbFailonError constant like:

CurrentDb.Execute strsql, DbFailOnError

Again some error trapping is needed when using this approach, a very basic way would be:

Private Sub ButtonUpRevision_Click()
On error goto ErrorButtonUpRevision
[COLOR="Red"]... 
Your code here
...[/COLOR]
ExitButtonUpRevision:
  Exit Sub

ErrorButtonUpRevision:
      MsgBox "Error " & Err.Number & " " & Err.Description 
      Resume ExitButtonUpRevision [COLOR="Lime"]'in this case not needed as there's no cleaning up in the exit.[/COLOR]
End Sub

 

[thebatfink]

Hi Peter,

Thanks very much for your reply. That makes total sense and I will work that into my solution for sure.

I do have another query though.. With the way I have built the sql queries, I assume I will need 'CurrentDb.Execute strsql, DbFailOnError' after each UPDATE query which could mean the first statement executes ok, so it then moves onto the next, which errors and so doesn't commit the update, but the previous update is still commited.
I'd like to prevent ALL the queries from executing if any of them fail. Not just each one individually.

Does that make sense? Is there a way to do this. From what I had researched, it wasn't possible to group the UPDATE SQL's unless the fields where all matching (I only posted partial code, I actually have 5 UPDATE statements and one of them has a different field name for [ISSUEREVISION]).

Thanks for the help!

 

[PeterF]

Did mis the multiple query's
With the BeginTrans, CommitTrans and Rollback methods you should be able to achieve this.
I did not test the following but it wil give you a direction.

[COLOR="SeaGreen"]'before your first query[/COLOR]
dbengine.BeginTrans 

[COLOR="seagreen"]'after the last query[/COLOR]
dbengine.CommitTrans

[COLOR="seagreen"]'on error (in the error handler)[/COLOR]
dbengine.Rollback

 

[thebatfink]

You are a star

So I'm guessing those three lines are in addition to all the other code, so it would look like this..

Private Sub ButtonUpRevision_Click()
Dim iReply As Integer
Dim dateString As String
 
On Error GoTo ErrButtonUpRevision
 
dateString = DATE
 
iReply = MsgBox(Prompt:="Are you SURE you want to up revision and date" _
& vbCr & vbCr & "THIS CAN NOT BE UNDONE!!!", Buttons:=vbYesNo, title:="UP REVISION")
If iReply = vbYes Then
 
dbengine.BeginTrans
 
strSQL = _
"UPDATE [PRODUCT DATA] " & _
"SET [DATE] = """ + dateString + """, [ISSUEREVISION] = IIF([ISSUEREVISION] = ""N/A"",1,[ISSUEREVISION] + 1) " & _
"WHERE [R NAME] = """ + Me.R_NAME + """ AND MID([ITEM NO],1,1) <> ""O"" "
 
CurrentDb.Execute strsql, DbFailOnError
 
strSQL = _
"UPDATE [F2 PRODUCT DATA] " & _
"SET [DATE] = """ + dateString + """, [ISSUEREVISION] = IIF([ISSUEREVISION] = ""N/A"",1,[ISSUEREVISION] + 1) " & _
"WHERE [R NAME] = """ + Me.R_NAME + """ AND MID([ITEM NO],1,1) <> ""O"" "
 
CurrentDb.Execute strsql, DbFailOnError
 
dbengine.CommitTrans
 
MsgBox "Up revision and dating completed", vbInformation, title:="COMPLETE"
 
Else
 
    MsgBox "Canceling", vbInformation, title:="CANCELLED"
    Exit Sub
 
End If
 
Exit Sub
 
ErrButtonUpRevision:
 
dbengine.Rollback
MsgBox "Error " & Err.Number & " " & Err.Description 
 
End Sub

 

[PeterF]

As I said, I didn't test it but you got the idea.

You left out the Exit label bevor the ErrButtonUpRevision: label, this means that the last label will run always after the update. After CommitTrans Rollback will throw an error.

 

[thebatfink]

OK thanks Peter I will have a play with what I have and see how it goes. Incidently, I removed the exit label as it seemed redundant code as I have nothing to clean after the error handler. I have an 'Exit Sub' before the error handler label so it should terminate before that.

I noticed this is how Access creates its subs by default, but not I how would manually form them having come over from Excel etc.

Anyhow, thanks very much for your help and time on the subject, much appreciated.