Re: duplicates query help & strategy for update queries with SetWarnings = False

Perhaps the behavior I am referring to applies *only* to Access 97?

The Access 2003 help on Execute (DAO) states:
<quote>
In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

In earlier versions of the Microsoft Jet Database Engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.
</quote>

The Access 2007 help on Database.Execute (HV10085237) states effectively the same thing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:46f50ec8$0$28195$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Rick download the document in this kb:
http://support.microsoft.com/kb/151193/en-us

Acc97cnv.doc contains a section titled:
dbFailOnError Behavior in Microsoft Access 97

<quote>
Using the dbFailOnError option in Microsoft Access 95 (or DB_FAILONERROR in version 2.0) with the Execute method of a QueryDef object automatically places an action query inside a transaction. This causes the entire operation to rollback if any errors occur during the processing of the query.

In Microsoft Access 97, the dbFailOnError option does not automatically place the action query in a transaction, so all changes up to the failed record will not rollback automatically if the query fails. You must review all code that uses the Execute method with the dbFailOnError option in Microsoft Access 97, and use explicit transactions if necessary. More information is available in the Acread80.wri file; search the Microsoft Access 97 Help index for "readme, Microsoft Access."
</quote>

"Rick Brandt" <rickbrandt2@xxxxxxxxxxx> wrote in message
news:rY6Ji.3705$ZA5.956@xxxxxxxxxxxxxxxxxxxxxxx
Allen Browne wrote:
No, dbFailOnError does not rollback, David.

It used to before Access 97. The A97 help file wrongly states that it
rolls back. You had to read the ReadMe installed with A97 to discover
that MS had changed this. So now dbFailOnError quits when an error
occurs, so leaves you with partial updates - probably the worst
possible result for many situations.

Hmm, in a simple test I just did dbFailOnError did roll back the entire transaction. I tried an Append query of ten rows where the first two rows being inserted are valid and all others produce duplicate key errors.

When I run the code and get the Duplicate Key error nothing is inserted. When I removed dbFailOnError then the valid rows were inserted and the duplicate rows were not (no error of course).

This was Access 97 SR2.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

.

Relevant Pages

  • Notes on MS02-068, extensive downplaying of severity
    ... Microsoft has released yet another cumulative patch for Internet ... The sole vulnerability that MS02-068 patches is the "external object ... "Exploiting the vulnerability could enable an attacker to read, ... Since we can already create and execute arbitrary command scripts on the ...
    (NT-Bugtraq)
  • Notes on MS02-068, extensive downplaying of severity
    ... Microsoft has released yet another cumulative patch for Internet ... The sole vulnerability that MS02-068 patches is the "external object ... "Exploiting the vulnerability could enable an attacker to read, ... Since we can already create and execute arbitrary command scripts on the ...
    (Bugtraq)
  • Re: Wheres the Bock?
    ... they attempt to make that distinction using Microsoft software. ... it means to execute a different ... terminals have only a limited number of things they can do, ... useful piece of code has had those bugs even after Bill Gates was born. ...
    (rec.arts.sf.fandom)
  • Re: Important...Interesting...Danger behind some file types ??
    ... and .URL files I can´t understand what malicious code they can carry. ... case of the URL the maximum they could do is execute a local file via 'file:' ... this issue on some security forums but no one was able to reply. ... no good documentation the only things Microsoft provide on SCF files are IE ...
    (microsoft.public.security)
  • CERT Advisory CA-2001-36 Microsoft Internet Explorer Does Not Respect Content-Disposition and Conten
    ... CERT Advisory CA-2001-36 Microsoft Internet Explorer Does Not Respect ... vulnerability may allow an attacker to execute arbitrary code on the ... web page or email message. ...
    (Cert)
Loading