Update query no longer runs as transaction.

[DataMiner]

Hi,
I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.

UPDATE TST3 SET [date] = Mid([timedate],8,2) & '/' & Mid([timedate],10,2) & '/' & Right([timedate],4), [time] = Left([timedate],2) & ':' & Mid([timedate],3,2) & ':' & Mid([timedate],5,2), Serial = [serial] & '3';

One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing
35
56
1
13
and I'm trying to update them to
353
563
13
133
But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.

Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with:
353
563
1
13
(and yes, I DO have dbfailonerror set)

So, it looks to me as if update queries are no longer running as transactions.
I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?

I recently ran microsoft update and am now running
Access 2002 (10.6771.6830) SP3.

 

[ByteMyzer]

You will need to wrap your action query in a BeginTrans/CommitTrans/Rollback handler. For example:

Dim dbe As DAO.DBEngine
Dim db As DAO.Database

Set dbe = DBEngine  ' Current Database Engine
Set db = dbe(0)(0)  ' Current Database

dbe.BeginTrans      ' Start the transaction
On Error Resume Next
db.Execute "UPDATE TST3" _
    & " SET [date] = Mid([timedate],8,2) & '/'" _
    & " & Mid([timedate],10,2) & '/' & Right([timedate],4)," _
    & " [time] = Left([timedate],2) & ':' & Mid([timedate],3,2) & ':'" _
    & " & Mid([timedate],5,2)," _
    & " Serial = [serial] & '3';", dbFailOnError
On Error GoTo 0
If Err.Number <> 0 Then
    dbe.Rollback    ' Cancel any partial updates
Else
    dbe.CommitTrans ' Commit the complete transaction
End If

db.Close
Set db = Nothing
Set dbe = Nothing

 

[DataMiner]

Thanks, yes, there are many places I use transactions. But unless I am truly losing my mind (which is certainly possible) action queries have always AUTOMATICALLY run as transactions in the past. I've been programming Access for 10+ years and this is the first I've EVER seen an action query run "partially". Either the whole thing runs or doesn't run at all. Have I, indeed, lost my mind????

 

[gemma-the-husky]

the error looks to be in line

Serial = [serial] & '3';

if serial is a number then you cant add a 3 to it like that
perhaps you can do val(cstr(serial)&"3")

if serial is a string then it needs to be wrapped in quotestrings

-------
actually the same thing applies to your dates - if they really ARE dates, why arent they wrapped in # characters

 

[DataMiner]

OK, I've solved the problem.

The problem has nothing to do with the [serial] & '3' thing or dates. That works fine. [serial] is a text field. The problem is that when the query tries to change the value 1 to value 13, it fails becuase value 13 is already there in a later record. Even though by the time the query completes, that record would be changed to 133.

I even understand why this would fail. I was just surprised to find out the whole update did not fail. If you refer to my original post... the first two records updated, then it got to the offending record and didn't update after that.

Turns out, Microsoft changed this behavior in going to Jet version 3.5 (not sure when that was). Used to be if you used dbfailonerror, a failure meant the whole update got rolled back. Not any more. See
http://msdn2.microsoft.com/en-us/library/bb243015.aspx

 

[ByteMyzer]

gemma-the-husky,

The query was failing because the update would have resulted in a duplicate Serial number, not because of an incompatible field type. Reread the original post.


DataMiner,

I've been programming Access since A97 came out, and have always needed to employ the BeginTrans-Rollback-CommitTrans scenario to successfully cancel any partial transaction when executing an action query in VBA.

 

[DataMiner]

ByteMizer, You may be right. But maybe we're both right, as it looks like MS did change the behavior at some point. I started in Access 2.0 and this code has been around for about that long.
Another thing I didn't realize is that this stuff is handled differently if you actually create and store

 

[DataMiner]

ByteMizer, you may be right. Maybe we're both right, as it looks as if MS has changed the behaviour somewhere along the way. I started in Access 2.0 and this code has been around for about that long.

The other wrinkle is that I did not realize the behaviour is different depending on whether you run a stored querydef or SQL from code. Stored querydefs have property UseTransaction turned on by default. I recenlty changed this procedure from stored querydefs to SQL in code, thus causeing part of this problem.

I would still swear (though I can't prove it) that I've run update queries like this in the past without getting KV errors. I thought the behaviour was:
1. Run the whole thing as a transaction
2. Then check for KV errors (and there wouldn't be any, in this case)
Seems like that would be more desirable....

Anyway, Ive' now changed from appending numbers to appending alpha characters. Problem solved. Thanks for your help.