implementing transactions in microsoft access (for the first time)

here’s the simplified scenario: i’ve got a publication table and a publication_status table. when creating a new publication, a record needs to be inserted into both tables. when updating the status of an existing publication, a record only needs to be inserted into the publication_status table. thus, there is a one to many relationship between publication and publication_status.

what if during the process of creating a new publication, inserting a record into the publication_status unexpectedly fails. i want the database to automatically rollback the inserted publication record.

here’s the pseudo-code outline:

  1. begin transaction
  2. insert publication record
  3. get new publication_id
  4. insert publication_status record
    1. if fails, rollback transaction
    2. if succeeds, commit transaction

here’s the problem: when you query the database for the max publication_id (which is bad in practice i know) you get the max id prior to the beginning of the transaction. agh!

so how do you get the id for the recently inserted record in microsoft access? apparently you query the table for @@IDENTITY. uhh…ok.

here’s what the actual vba code looks like:

' begin transaction
ws.BeginTrans

' insert publication record
db.Execute ("INSERT INTO publication (publication_number) VALUES (" & Me.txt_publication_number & ")")

' get new publication_id
Set rst = db.OpenRecordset("SELECT @@IDENTITY AS insert_id FROM publication")
int_publication_id = rst.Fields("insert_id")

' create publication_status
db.Execute ("INSERT INTO publication_status (publication_id, publication_status_type_id) VALUES (" & int_publication_id & "," & int_publication_status_type_id & ")")

' rollback transaction if there was a problem, else commit
If db.RecordsAffected <> 1 Then
   MsgBox "Error adding publication status"
   ws.Rollback
Else
   ws.CommitTrans
End If

7 Comments

New comments are no longer accepted, so if you want to get in touch, send me an email.

Tim

This is the main reason I dislike auto-increment columns…if they already exist and you can’t change them, so be it, but I prefer to have my application populate that ID value itself so that it knows exactly what it put in and no subsequent querying is necessary. In non-Oracle DBs where no SEQUENCE is available, you can create your own ‘SEQUENCE’ table to manually keep track of the sequence IDs for each table….and then the querying/incrementing of that table gets wrapped up in the same transaction. Sort of a pain (substitute one pain for another!), but I think it’s nice to have that total control in your app as long as no other app or person will be updating the database.

justin

so it sounds like in advance (of the transaction) i’d have to get the new id and increment it (so the update is immediate). technically that should work in a multiuser user system–because the subsequent user should get the next id.

Koray

I use the same script. But there is a problem whar is WS. I have another problem also when there is a problem in transaction it continues to insert i mean it doesn’t rollback.

justin

Koray, ws is the workspace object. I no longer use Microsoft Access actively, so I can’t really offer any productive help. Best of luck.

Hemal

The example is very good but i have a question , to use the control transaction with MS Access in VB.net , if possible post the example for that one. Thanks.

Jaara

ohhhh, why so complicated? get new id??? try to put @@Identity into secont Insert ;-) No need for selecting @@Identity.

Mitchell

I would suggest using a recordset instead. This will allow you to get the actual primary key of the inserted record…
Dim rs as recordset
Dim newPubID as long
Dim newPubStatusID as long

‘open the recordset and add new Publication
set rs = currentdb.openrecordset (“publication”,, dbAppendOnly)
rs.AddNew
rs!fldName = myValue
newPubID = rs!PrimaryKey
rs.update
rs.close

‘Now we use our newly found newPubID to add to the next table

set rs = currentDb (“Publication Status”,,dbAppendOnly)
rs.AddNew
rs!fldName = newPubID
newPubStatusID = rs!PrimaryKey
rs.update
rs.close

‘if either update fails, you will get error 3314

if -problem- then
‘you now have both inserted primary keys so you can reopen a recordset, go to the faulty entry, and use the rs.delete to remove it
end if

you get the idea. Cheers