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:
- begin transaction
- insert publication record
- get new publication_id
- insert publication_status record
- if fails, rollback transaction
- 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
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.