Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    26

    Unanswered: execute multiple SQL queries

    Hi -

    I am using Access 2000, and I have multiple update statements to execute. I have only executed single queries before. Is there a way of doing this??

    Thanks in advance!

    Elizabeth

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What method do you use to execute single queries?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2005
    Posts
    26
    I have just modified the SQL behind the Query Builder by going to View...SQL View

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How do you EXECUTE the query, not write it...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by ebostwick
    I am using Access 2000, and I have multiple update statements to execute. I have only executed single queries before. Is there a way of doing this??
    If you mean you want to execute a series of independant queries then write a macro (they are dead easy to create) and list the queries as seperate lines in the macro. They will then get executed one by one.
    hth
    Chris

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not a constructive comment i'm afraid:

    async update queries are a fun way to trash a database.

    ?? is your schema is the way it should be - it is relatively unusual for a single user action to cause updates to multiple tables (at least it is unusual the way i write stuff!)

    if you really do have an event that needs to trigger multiple updates, issue a series of atomic updates and let each complete before the next starts - much safer than letting the engine sort out the execution order itself. a line of code is not that expensive!

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by izyrider
    not a constructive comment i'm afraid:
    Which comment ?

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Izy,

    Quote Originally Posted by izyrider
    async update queries are a fun way to trash a database.
    Sure, but who mentioned async queries ?

    Quote Originally Posted by izyrider
    ?? is your schema is the way it should be - it is relatively unusual for a single user action to cause updates to multiple tables (at least it is unusual the way i write stuff!)
    How about Data Warehouse updates - calulated the different time periods after the latest data has been added

    How about temporary tables created in order to run reports (I know this isn't the case in the perfect world but we're talking about access - complicted queries like cross-tabs can get alfully slow to manage in one chunk).

    Serial queries are fine for the above. If they fail, just run them again from the start.

    Quote Originally Posted by izyrider
    if you really do have an event that needs to trigger multiple updates, issue a series of atomic updates and let each complete before the next starts - much safer than letting the engine sort out the execution order itself. a line of code is not that expensive!
    I agree this is a specific case. e.g. moving funds from one account to another - both the credit and debit must either happen or not happen (I use the example so that other readers understand the distinction of atomic updates).

    I guess the point is we're both making assumptions about the type of update Elizabeth is performing although I suspect you are nearer the mark. I agree atomic updates are necessary in certain instances but for others serial queries via macros or code are sufficient.

    Chris

Posting Permissions

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