Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Problem edit/update within a transaction

    Can't believe I am asking this...
    Company I am working for have an old system written in Access 97 VBA (DAO).
    In it they start a transaction, modify some fields (an edit and update within a loop) and end the transaction, we have within this transaction now also directly modified some of the same fields on the same table but with a new/different recordset (using edit and update) and when run it gets 3003 or 3246 transaction errors.
    Does anyone know why we would get these errors?
    We have a solution (move the edit/update to after the transaction has finished) which is tested and works, but my boss will not let me use it as she considers it a workaround and would prefer to know what the actual problem is.
    I have not used DAO in a long time and rarely use transactions, but is it possible, that you cannot have 2 recordsets pointing to/manipulating the same bit of data within a transaction???


  2. #2
    Join Date
    Feb 2004
    As I remember, If you open recordsets on the same workspace, you shouldn't have problems like that.
    But there was a one thing that may help you, as I remember, you shoud open all recordsets supposed to work within transaction, befor transaction starts. Try this
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Binary Universe
    This is from Access help files:

    A series of changes made to a database's data and schema. Mark the beginning of a transaction with the BeginTrans statement, commit the transaction by using the CommitTrans statement, and undo all your changes since BeginTrans by using the Rollback statement.

    Transactions are optional and can be nested up to five levels. Transactions increase the speed of operations that change data and enable you to reverse changes easily.

    Transactions are global to the referenced database object's Workspace.

    Could not start transaction; too many transactions already nested. (Error 3003)
    You tried to nest a transaction more than five levels deep. Use the CommitTrans or Rollback method to save or roll back the current before you begin another one.

    Operation not supported in transactions. (Error 3246)
    You tried to perform an operation that is not valid within a transaction. A transaction can be one you create using a BeginTrans statements, or an internal transaction created by the Microsoft Jet database engine, such as when you delete multiple records in a single operation.

    I hope this helps.
    I won't byte... hard!

Posting Permissions

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