By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,316 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

How to Verify SQL Update Succeeded

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
"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
Nov 13 '05 #5

P: n/a
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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.