transaction not working

  • Thread starter Thread starter Ahmed
  • Start date Start date
A

Ahmed

thank you for your help: i need to solve this.

i try to make that code work:

dim wks as workspace

set wks = dbengine.workspaces(0)
wks.BeginTrans

db.execute "insert into CLIENTS (ID, NOM) values (33, 'TEST')", dbseechanges

wks.CommitTrans

debug.print dlookup("nom","client","id=33") 'at this point, the program no
longer responds


(db variable is global and set at the database launch)
if i do the same thing without using a transaction, it works.
the code do as if i did not do the CommitTrans action.

Thank you.
 
Suggestions:
1. Set a Database variable *after* you open the transaction in your
workspace.

2. Use dbFailOnError so you hear about any issues.

3. Test the RecordsAffected so you know the record was written.

Result:
wks.CommitTrans
Set db = wks(0)
db.Execute "INSERT INTO ...", dbFailOnError
Debug.Print db.RecordsAffected & " record(s) written"
wks.CommmitTrans

More information on traps with transactions:
http://allenbrowne.com/ser-37.html
The warning about how you clean up is particularly relevant when you were
trying to keep a global db variable open.
 
db.execute "insert into CLIENTS (ID, NOM) values (33, 'TEST')",
dbseechanges
debug.print dlookup("nom","client","id=33") 'at this point, the program no
Click to expand...

In addition to Allen's recommendations, you have a typo here (CLIENTS and
client), no "s" on the second one.
 
you're right....

At the launch of my database, i define wksLocal (workspace), and db
(database) as global variables. they are set also at the very beginning
(first wksLocal and then db !).
So i don't have to define another workspace in a specific sub i think. I
tried this way and it works...

Thank you M. Browne ;-)
 
Back
Top