Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Question Unanswered: 2 SQLs in the same transaction with RunSQL

    This may be very easy, but it is the first time I am trying to do this, so I don't have any experience.

    How can I run 2 SQLs in the same transaction with the RunSQL action. I need it cause these 2 operations must be atomic, or both of them or neither of them.

    Thank you for your replies.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can execute exactly one statement at a time. Run one after the other.

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Originally posted by Teddy
    You can execute exactly one statement at a time. Run one after the other.
    But, is this automatic like this:??

    RunSQL(SQL1)
    RunSQL(SQL2)

    I think that every RunSQL is a transaction by itself, and I want only one transaction for both of the SQLs

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    RunSQL is simply a function that runs A sql statement.

    If you would like them to be bundled together, you could create a public sub containing both statements, then call the sub when you would like to fire your sequence of sql statements.

  5. #5
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Arrow

    Originally posted by Teddy
    RunSQL is simply a function that runs A sql statement.

    If you would like them to be bundled together, you could create a public sub containing both statements, then call the sub when you would like to fire your sequence of sql statements.
    Sorry, I made a mistake in the last post. I mean if they are atomic, not automatic.

    So, maybe I don't explain myself well. There is the concept of transaction, which means that every SQL stament in the same transaction is going to be executed, if there is a problem, no statement is stored in the database. I want this. If there is a problem between these 2 RunSQLs, like the PC crashes, I want that the first SQL is cancelled, so no changes are made in the database. The 2 SQLs or none.

    Thank you for your interest.

  6. #6
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    RunSQl

    Might I suggest a slight variation to a previous solution.
    Put each SQL statement into a function. If trhe first function hits a problem force it to return an error valur (False or a numeric value whatever).

    A simple If then statement can e used to test whether the second function should run ...


  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    As far as I know access does not support rollback transactions.. I could be wrong, but I've never seen such a function.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    A does fully support explicit transactions (and recent As also use implicit transactions, but that wont help your twin SQLs)

    sorry that i can only do this with DAO - the general outline is:

    dim wsp as dao.workspace
    on error goto err_yourSub
    'set up your 2 SQL strings
    wsp = dbengine(0)
    wsp.begintrans
    'run your sql
    wsp.committrans

    err_yourSub:
    wsp.rollback


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    like the PC crashes
    might be a problem tho if the crash happens during the commit process

    (you could be lucky: maybe the commit is protected by an implicit transaction, but this is beyond my knowledge)

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Question

    Hi izyrider,

    I tried your solution, and it almost work. Look what I am doing:

    On Error GoTo err_commit
    Set wsp = DBEngine(0)
    wsp.BeginTrans
    With DoCmd
    .SetWarnings False
    .RunSQL SQL, No
    .RunSQL SQL2, No
    .SetWarnings True
    End With
    wsp.CommitTrans
    On Error GoTo 0

    err_commit:
    wsp.Rollback

    This works, the 2 SQLs are done. The problem is that the rollback is not done when I raise an error between the 2 RunSQLs. Like that:

    .RunSQL SQL, No
    Err.Raise (4) 'for example 4
    .RunSQL SQL2, No

    Then when the error is raised, the code jumps to the tag err_commit, the rollback is execute but the first SQL is not rollback, the changes done by the first SQL remain in the database.

    Do you have any idea? Please.
    Maybe the RunSQL is not good to work with the way of transaction you told me.

    thanks a lot for your help

  11. #11
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Unhappy Please, help.

    I still haven't found a solution for this problem. Anyone can help me, please. I need it to do this 2 SQLs like that, in the same transaction.

    Thank you in advance.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    A's transactions do not support several flavours of linked tables (e.g. foxpro, dbase) and they only work via ODBC if the underlying data source supports transactions. i also see a note in A2k help, that transactions are not supported on linked tables (but this is contradicted in dozens of places on msdn.com). there is also a lot of other contradictory stuff on transactions in msdn.com
    i have a lot of dox on transactions, but it all dates back to A95. i've got what i hoped was a pair of SQL wrapped in a transaction on my current project... after your experience i guess i should check it will rollback, but the app will soon be strong enough to move to sql server so maybe i wont bother

    it's a confusing world!

    last thought: there is a "transactions" property of a recordset
    if rst.transactions = TRUE then 'it supports transactions

    izy
    Last edited by izyrider; 04-28-04 at 10:21.
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh yes - the A95 examples i have are using db.execute not docmd.runsql

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Thanks izyrider!!!!

    I will try. Fuc... access ... :-D

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    go for it

    also, i saw that there are ADO equivalents to the DAO begintrans, committrans, & rollback: from a brief look it seems that there is a space tween begin & transaction in the ADO flavour but i didn't find complete example code.

    interesting topic - if you find more info: please post it here

    good luck, izy
    currently using SS 2008R2

Posting Permissions

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