Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transaction/Rollback problem (Access 2000)

    I have a problem with transaction processing as follows:

    I am working with an application that has a function that writes error messages and associated data to a message log table in the database. Typically, if processing within a transaction fails, the error handler rolls back the transaction then calls the message log function. The data that caused the problem can be checked and corrected later.

    However...

    One of my procedures calls a function from within the transaction, so what has happened is the function has failed, the error handler has called the message log function then returned control to the calling procedure, which then rolls everything back, including the message log call.

    I think the way to deal with this is to process the message log within a 'Workspace', but my attempt was unsuccessful. This problem is likely to re-occur in other areas of the database so I would like to find a versatile way of dealing with it.

    Can anyone help?

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transaction/Rollback problem (Access 2000)

    Is your function called from outside transactions as well? I don't see how you could use a workspace when you're calling the function from within a transaction. The nature of transactions is that they are self-contained.

    One possibility might be to use Err.Raise in the function to pass the error generated back to the calling routine rather than handling it directly in the function. However, without knowing more about the way your error handlers are written it's hard to be more specific.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transaction/Rollback problem (Access 2000)

    The message log function is called from several procs and functions, from the error handler section. So typically the transaction is rolled back and then the message log function is called. By writing the error information (including the data involved) to a table, it gives the users a reasonable chance of fixing the data that caused the problem.

    My problem is that the function where the error occured is called from another procedure that has the BeginTrans, CommitTrans. So after the function returns, the Rollback occurs.

    I haven't used 'Workspace' previously so that was a bit of a guess, and I hadn't thought about using Err.Raise. I'll look into that. Another thought I had recently is to write the error information to a .csv file. It can then be linked to give the users easy access to the information.

    What do you think?

    Thanks

    Dirk.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transaction/Rollback problem (Access 2000)

    Actually, I prefer the text file approach. The Total SourceBook from FMS, Inc used to have (and probably still does) code for error handling that used a test file stack and pushed the current procedure name into a text file, popping it out of the stack/file when the procedure completed successfully. That allowed tracing of a path to the error. The help files mention that a child transaction that has been committed in a separate workspace will not be rolled back but I haven't a clue as to exactly what that means since they don't clearly define a "child" transaction or how to run it in a separate workspace. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •