Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access VBA: Running queries synchronously

    Apparently in Access VBA, if you're running a query in a line of code, control may be passed to the next line of code before the query finishes. If the next line of code starts another query that depends on the result of the first, then you could end up with bad data. Is there a way to ensure the query in the first line of code finishes before the second starts? (That is, the queries run synchronously instead of asynchronously?)

    Thanks.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,208
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    Quote Originally Posted by bjulien View Post
    Apparently in Access VBA, if you're running a query in a line of code, control may be passed to the next line of code before the query finishes. If the next line of code starts another query that depends on the result of the first, then you could end up with bad data. Is there a way to ensure the query in the first line of code finishes before the second starts? (That is, the queries run synchronously instead of asynchronously?)

    Thanks.
    To the best of my knowledge that statement is not correct. I have never found a situation where that has occurred. Can you provide an example of that?
    Rui
    -------
    R4

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was asking on behalf of somebody else. (That's why I said "Apparently...", since it didn't actually happen to me.) I'll see if I can get more details as to how they think this is happening.

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,208
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    I would say the impression is,most likely, incorrect, but please post proving any further info that may be of help to clarify the situation.
    Rui
    -------
    R4

  6. #5
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by bjulien View Post
    Apparently in Access VBA, if you're running a query in a line of code, control may be passed to the next line of code before the query finishes. If the next line of code starts another query that depends on the result of the first, then you could end up with bad data. Is there a way to ensure the query in the first line of code finishes before the second starts? (That is, the queries run synchronously instead of asynchronously?)

    Thanks.
    This is definitely possible. I have experienced this over the years. I would say the impression may be correct.

    It would help to see the exact code that is being used.

    I have experienced queries running synchronously instead of asynchronously when using the following code:

    Here is code that can run synchronously

    Code:
    ' this will not always run  asynchronously 
    CurrentDB.Execute "<query 1>"
    CurrentDB.Execute "<query2>"
    CurrentDB.Execute "<query 3>"
    Using the the following will correct the issue:

    Code:
    ' this works 
    Docmd.SetWarnings False
    Docmd.RunSQL "<query 1>"
    Docmd.RunSQL "<query2>"
    Docmd.RunSQL "<query 3>"
    Docmd.SetWarnings True
    or

    Best Practices for using multiple .Execute statements

    Code:
    ' this works by using a single CurrentDB instance
    With CurrentDB
         .Execute "<query 1>"
         .Execute "<query2>"
         .Execute "<query 3>"
    End With
    Last edited by HiTechCoach; 2013-11-12 at 10:20.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  7. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,208
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    I confess to basically running all my SQL using ADO command objects - the cross app nature of ADO makes it easier for me. I have never seen it happen when doing so.
    Rui
    -------
    R4

  8. #7
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by ruirib View Post
    I confess to basically running all my SQL using ADO command objects - the cross app nature of ADO makes it easier for me. I have never seen it happen when doing so.
    Using ADO probably helps solve the issue since you are probably using a single connection.

    Note: the JET/ACE database engine driver converts the ADO back to DAO before it is executed.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  9. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    BC
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The person in question has told me she was using DoCmd (presumably DoCmd.OpenQuery). Searching on the web does suggest a series of these can run asynchronously. (Presumably because each opens a separate connection?) She's solved the problem for now with DoEvents. I'll pass along the bit about enclosing a bunch of Executes within a "With CurrentDB". Thanks.

Posting Permissions

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