Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    7

    Question Unanswered: pause execution between queries

    I have an access routine that runes a series of append queries. Due to several keys that are in place, they need to run in order and each needs to finish before the next runs. right now, while the first is executing, the next starts, causing it not to append all the records due to key violations. How can i make access 97 stop and wait for the first to finish before the next is executed (and so on.....)

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    will a transaction do it?

    izy

  3. #3
    Join Date
    Jan 2003
    Posts
    7
    i don't understand

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmmm, yes! i just tried a2k help on transactions and ...most of it's dead and the rest is crap.

    maybe you need to find a decent resource on transactions (my bedside reading is Alison Balter, but that's back in a95), but briefly, before some significant mods to a table it is nice to make sure that they all happened ok or they all got cancelled so at least your db is in a known state: transactions do that.

    ...imagine a price list in foreign currency and you (foolish design) decide to multiply all the prices by 1.3 to reflect a new forex rate - if the machine crashes half way you have a total mess with some prices changed, others not. transaction processing allows you to make all changes or roll back to the pre-change situation.

    so, with a suitable .beginTrans {do stuff} .commitTrans structure you can know that the whole update took place or got cancelled.

    for your problem i imagine a situation like: Step1isDoneBool and some wise exit/onError stuff in a transaction to make sure everything happened ok before you go to the next step.

    izy

  5. #5
    Join Date
    Jul 2001
    Location
    Estonia
    Posts
    33
    Izy,
    that`s the n-th time I hear reference to Alison Balter - I checked Amazon.com and they only carry versions for a2k and later - I use a97 and want to stick with it for now.
    What you put after and before .beginTrans ? will it work at all in a97?
    On original matter: To ensure simple query execution it is possible:
    DoCmd.OpenQuery "qryQuery1"
    DoEvents
    DoCmd.OpenQuery "qryQuery2"
    DoEvents
    . . .
    right?

    Regards,
    Alec

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    pandora's box!!!
    yes transactions work fine in a97

    basic transaction structure in old-access-speak is this:

    function myStuff() as boolean
    on error goto bad_mystuff
    dim wsp as workspace
    dim dab as database
    dim rex as recordset
    set wsp=dbengine(0) 'i told you this was antique code!
    set db=currentdb
    set rex=dab.openrecordset(...with a query or sql or....
    wsp.beginTrans 'TRANSACTION STARTS HERE
    'mess around with your recordset here
    'here
    'and here
    wsp.commitTrans 'TRANSACTION COMMITS HERE
    myStuff=true
    exit_myStuff:
    exit function
    bad_myStuff:
    'maybe include an error message if you think the user is smart
    myStuff=false
    wsp.rollback 'mods to wsp are REVERSED to where they were before beginTrans
    resume exit_myStuff
    end function

    izy

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and i'm not sure about DoEvents

    ...what "event" is pending if there is a succession of queries updating (possibly remote) tables?

    izy

  8. #8
    Join Date
    Apr 2004
    Posts
    1

    Re: pause execution between queries

    Originally posted by jramsey
    I have an access routine that runes a series of append queries. Due to several keys that are in place, they need to run in order and each needs to finish before the next runs. right now, while the first is executing, the next starts, causing it not to append all the records due to key violations. How can i make access 97 stop and wait for the first to finish before the next is executed (and so on.....)

    Hello,
    I was actually going to log the same problem as yours when I came across it. I was wondering if you ever got a solution to this and if you wouldn't mind sharing it. I have the same problem and can't find any way around this.

    Thanks! Mfg2529

Posting Permissions

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