Re: How to code transactions

Tech-Archive recommends: Speed Up your PC by fixing your registry

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)




"rich" <rich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:124CCA6B-7B77-4E2E-91E9-26F55892D08F@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Stock Transaction Query
    ... I’m trying to design a query for the cost of employee stock ... transactions. ... STOCKNUMBER, and the DATE that the stock was issued. ...
    (microsoft.public.access.queries)
  • Re: Error Handling
    ... If you're using explicit transactions in your stored procedures (BEGIN ... >I have problem of using transaction on store procedure ...
    (microsoft.public.access.adp.sqlserver)
  • Distributed Transaction VS Local Transactions?
    ... Distributed Transactions VS Local Transactions ... Explicit Transactions VS Implicit Transactions ... Distributed Transactions span two or more SQL Servers (2 or more Databases ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: performance on SET TRANSACTION ISOLATION LEVEL
    ... > I rarely use transactions in this instance. ... > page might run a report from a view ... > explicit transactions. ... implicitly increase the isolation level during the time that it runs. ...
    (microsoft.public.sqlserver.programming)
  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)