How to Verify SQL Update Succeeded

[Mike Thomas]

In Access 2000, we are having a problem with some updates apparently not
succeeding. I am not sure whether the records to be updated are locked,
somebody is overwritng the changes, or what, but intermittently users
discover that records they are sure they updated are not showing the
changes.

I want to put a check in so that after I run the update I can check the
number of records affected (should always be one), or find some other way to
check what has happened.

The update is simple and works as follows:

currentdb.execute("Update [invoice] set [paiddate] = '20040101' Where IDKey
= 100;")

Is there a way to show how many records were affected by this statement?

Many thanks,
Mike Thomas

[Allen Browne]

Use the dbFailOnError switch to stop if any problems occur, and test
RecordsAffected to see how many were written:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "UPDATE ...;", dbFailOnError
MsgBox "Records affected: " & db.RecordsAffected
Set db = Nothing

Note that dbFailOnError does not rollback after error. You can run a
transaction if you need that. Details and example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
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.

"Mike Thomas" <mi**@ease.com> wrote in message
news:N%**********************@newssvr28.news.prodi gy.com...

In Access 2000, we are having a problem with some updates apparently not
succeeding. I am not sure whether the records to be updated are locked,
somebody is overwritng the changes, or what, but intermittently users
discover that records they are sure they updated are not showing the
changes.

I want to put a check in so that after I run the update I can check the
number of records affected (should always be one), or find some other way to check what has happened.

The update is simple and works as follows:

currentdb.execute("Update [invoice] set [paiddate] = '20040101' Where IDKey = 100;")

Is there a way to show how many records were affected by this statement?

Many thanks,
Mike Thomas

[Mike Thomas]

Allen, Thanks again for your help.

Mike Thomas
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40**********************@per-qv1-newsreader-01.iinet.net.au...

Use the dbFailOnError switch to stop if any problems occur, and test
RecordsAffected to see how many were written:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "UPDATE ...;", dbFailOnError
MsgBox "Records affected: " & db.RecordsAffected
Set db = Nothing

Note that dbFailOnError does not rollback after error. You can run a
transaction if you need that. Details and example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
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.

"Mike Thomas" <mi**@ease.com> wrote in message
news:N%**********************@newssvr28.news.prodi gy.com...

In Access 2000, we are having a problem with some updates apparently not
succeeding. I am not sure whether the records to be updated are locked,
somebody is overwritng the changes, or what, but intermittently users
discover that records they are sure they updated are not showing the
changes.

I want to put a check in so that after I run the update I can check the
number of records affected (should always be one), or find some other

way to

check what has happened.

The update is simple and works as follows:

currentdb.execute("Update [invoice] set [paiddate] = '20040101' Where

IDKey

= 100;")

Is there a way to show how many records were affected by this statement?

Many thanks,
Mike Thomas

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40**********************@per-qv1-newsreader-01.iinet.net.au:

Note that dbFailOnError does not rollback after error. You can run
a transaction if you need that. Details and example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

Good lord -- how did I miss that one? I've always assumed that
dbFailOnError *does* entirely roll back the transaction.

Oy.

Well, looks like I need another alteration to my ExecuteSQL code, to
wrap the whole thing in a transaction.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40**********************@per-qv1-newsreader-01.iinet.net.au:

Note that dbFailOnError does not rollback after error. You can run
a transaction if you need that. Details and example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

Actually, rewriting my ExecuteSQL code, I'm wondering if:

DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
DBEngine.Workspaces(0).CommitTrans

where db is a database reference created with CurrentDB(), will
work? Or do I have to instantiate the db variable after the
beginning of the transaction, as a child of the workspace?

Or if I'm using a cached db variable or setting it to CurrentDB()
just before beginning the transaction, am I OK?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

How you missed it, David, was that it DID roll back in Access 95 and
earlier. Microsoft mulitated it in Access 97 without documenting the change
in the help file: you had to read the readme to find out!!!

To answer your other question, I imagine (without testing) that creating a
Currentdb() object after beginning the transaction would work, because it
creates a new object that points to dbEngine(0)(0) which is in the
transaction. You may want to test it and check.

--
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.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn*********************************@24.168.12 8.74...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40**********************@per-qv1-newsreader-01.iinet.net.au:

Note that dbFailOnError does not rollback after error. You can run
a transaction if you need that. Details and example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

Good lord -- how did I miss that one? I've always assumed that
dbFailOnError *does* entirely roll back the transaction.

Oy.

Well, looks like I need another alteration to my ExecuteSQL code, to
wrap the whole thing in a transaction.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc