Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Trouble running multiple queries

    I'm having trouble with the code below. I have a block of code running a series of queries against the access db. The code builds the query, executes, calls a function to parse the results from the result set, closes the results set, then repeats the process again. When I try to run the code, the first statement goes through the process successfully and closes the record set. The second set errors out when I try to open execute the query saying "Invalid Argument"

    Any help would be appreciated. I'm new to Access/VB so I'm hoping its something simple I'm missing.

    Code:
    Dim DBRecordset As New ADODB.Recordset
    Dim SQLStr As String: SQLStr = ""
    
    On Error GoTo ERR_Handle
    With DBRecordset
    
    	SQLStr = "SELECT DISTINCT [ID] FROM TestDB WHERE Status=" & VBFalse
    	.Open SQLStr, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    	'Call function to parse the results list
    	.Close
    
    	SQLStr = "SELECT DISTINCT [ID] FROM TestDB WHERE Status=" & VBFalse
    	.Open SQLStr, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    	' Call function to parse the results list
    	.Close
    
    End With
    Set DBRecordset = Nothing
    Exit Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I suspect that's because the seconnd Query is running before the first Query has completed running.

    Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause timing problems.

    If you’re running a series of Queries where all but the first Query is dependent upon the previous Query being completed before it starts to execute. The following VBA code
    Code:
    DoCmd.OpenQuery "QueryA"
    DoCmd.OpenQuery "QueryB"
    DoCmd.OpenQuery "QueryC"
    will immediately run all three, not waiting for one to finish executing before starting the next one. The answer to halting the code in this type of situation is to use DoEvents.

    Code:
    DoCmd.OpenQuery "QueryA"
    DoEvents
    DoCmd.OpenQuery "QueryB"
    DoEvents
    DoCmd.OpenQuery "QueryC"
    DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC.

    DoEvents is an easy, safe bet when encountering what seems to be timing issues.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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