How to code transactions

[rich]

Hello

I am trying to get an understanding of how Access handles transactions.

In my application, I have a couple table for "tracking" transactions. For
instance, consider you are adding a new employee, updating employee record,
or terminating an employee. Adding a new employee involves creating the
initial record. Along with that, I need to track this as a transaction so it
can be audited/reported on later.

Given that example, how do you perform the insert query to create the
initial employee record (query #1) AND perform an insert to the
tracking/auditing table (query #2) so they are treated as 1 transaction (that
is if either fails, they are both rolled back)?

I think in this situation, I should be using ADO and coding the actual SQL
statements myself, rather than relying on this via the forms. Please advise
if this is the correct line of thought, especially since little or nothing on
the employee forms would go to the auditing/tracking table.

Hopefully, I provided sufficient details for you. Please let me know if
more details are needed. Any references to sample code or documentation
would be greatly appreciated.

TIA,

Rich

 

[KARL DEWEY]

Access does not have any tracking built in except a little on when an object
was created or modified. You have to build any tracking of record addition
or change of data in a field that you may desire.

 

[Klatuu]

One of the best has this one his web site:
http://allenbrowne.com/AppAudit.html

 

[Guest]

You are correct. If you need transactions, explicit transactions
are the way to go. (In Access 2.x, you could include your
forms inside your explicit transactions, but like many other
fundamental design features, that has been optimised away).

Complex query-based or sub-form based solutions are
really fragile.

If you are starting from scratch, consider using SQL Server.
It's really easy to add after update procedures inside SQL
Server, much easier than the alternatives you are considering.

(david)

 

[Jamie Collins]

In my application, I have a couple table for "tracking" transactions. For
instance, consider you are adding a new employee, updating employee record,
or terminating an employee. Adding a new employee involves creating the
initial record. Along with that, I need to track this as a transaction so it
can be audited/reported on later.

Given that example, how do you perform the insert query to create the
initial employee record (query #1) AND perform an insert to the
tracking/auditing table (query #2) so they are treated as 1 transaction (that
is if either fails, they are both rolled back)?

The closest you can get to simultaneously updating your two tables is
to create a VIEW (stored Query object defined by a non-parameterized
SELECT query that returns a resultset) which with an INNER JOIN
between monitored table and monitoring table. The resulting VIEW
should be updateable, so an INSERT into the VIEW will notionally be
one operation. Note, however, that in practice it is two operations,
being an insert into each individual table on a left-to-right basis in
using JOIN clause. If you have DRI between the tables then you can use
an ON DELETE CADCADE referential action to delete from both tables,
again as one notional operation (again two in practice). UPDATE sql
statements I'm not so sure about -- always worth testing, even when
the INFORMATION SCHEMA indicates the VIEW is not updatable.

Jamie.

--