469,301 Members | 2,278 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

How do you make a VBA wait for a query to finish?

At the risk of oversimplifying my situation, here are the basic facts:

* I'm writing an ACCESS 2002 VBA.

* My VBA automatically uses information in one table (Table A) to repeatedly query a second table (Table B) via SQL.

* Each record in Table A (which has about 800 records) represents a set of conditions by which to query Table B.

* The VBA analyzes the recordset returned by the query of Table B, and writes the results back into Table A before moving to the next record in Table A and querying Table B again.

* The table being queried (Table B) has about 65,000 records, so the query takes a few seconds.

* The problem is that my VBA code continues to execute before the query of Table B is finished. Consequently, the resulting recordset from Table B it is incomplete and the analysis of it produces inaccurate results.

How do I tell Access (or VB) to wait for the query of Table B to finish before continuing with the rest of the code?

Any assistance is greatly appreciated.
Scott
Mar 28 '07 #1
11 44753
Put this in declaration section:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  4.  
Then put this where you want to wait:
Expand|Select|Wrap|Line Numbers
  1. Sleep (10000)
  2.  
This waits 10 seconds before moving to the next line.

You can also using a custom made function that looks something like this, although the amount of sleep time is based on how fast your processor runs:

Expand|Select|Wrap|Line Numbers
  1. Private Sub wait(intLoops as Integer)
  2.     Dim i as Integer
  3.     While i < intLoops
  4.         i = i + 1
  5.     Wend
  6. End Sub
  7.  
Mar 28 '07 #2
Denburt
1,356 Expert 1GB
I know it's been a while yet I just ran across this article and thought I would post something that may be a little more appropriate for your circumstances.

DBEngine.Idle dbRefreshCache

An example copied from the help file:
Expand|Select|Wrap|Line Numbers
  1. Sub IdleX()
  2.  
  3.    Dim dbsNorthwind As Database
  4.    Dim strCountry As String
  5.    Dim strSQL As String
  6.    Dim rstOrders As Recordset
  7.  
  8.    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
  9.  
  10.    ' Get name of country from user and build SQL statement 
  11.    ' with it.
  12.    strCountry = Trim(InputBox("Enter country:"))
  13.    strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
  14.       strCountry & "' ORDER BY OrderID"
  15.  
  16.    ' Open Recordset object with SQL statement.
  17.    Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)
  18.  
  19.    ' Display contents of Recordset object.
  20.    IdleOutput rstOrders, strCountry
  21.  
  22.    rstOrders.Close
  23.    dbsNorthwind.Close
  24.  
  25. End Sub
  26.  
  27. Sub IdleOutput(rstTemp As Recordset, strTemp As String)
  28.  
  29.    ' Call the Idle method to release unneeded locks, force 
  30.    ' pending writes, and refresh the memory with the current 
  31.    ' data in the .mdb file.
  32.    DBEngine.Idle dbRefreshCache
  33.  
  34.    ' Enumerate the Recordset object.
  35.    With rstTemp
  36.       Debug.Print "Orders from " & strTemp & ":"
  37.       Debug.Print , "OrderID", "CustomerID", "OrderDate"
  38.       Do While Not .EOF
  39.          Debug.Print , !OrderID, !CustomerID, !OrderDate
  40.          .MoveNext
  41.       Loop
  42.    End With
  43.  
  44. End Sub
  45.  
Hope this helps in some way good luck.
Nov 27 '07 #3
ADezii
8,800 Expert 8TB
I know it's been a while yet I just ran across this article and thought I would post something that may be a little more appropriate for your circumstances.

DBEngine.Idle dbRefreshCache

An example copied from the help file:
Expand|Select|Wrap|Line Numbers
  1. Sub IdleX()
  2.  
  3.    Dim dbsNorthwind As Database
  4.    Dim strCountry As String
  5.    Dim strSQL As String
  6.    Dim rstOrders As Recordset
  7.  
  8.    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
  9.  
  10.    ' Get name of country from user and build SQL statement 
  11.    ' with it.
  12.    strCountry = Trim(InputBox("Enter country:"))
  13.    strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
  14.       strCountry & "' ORDER BY OrderID"
  15.  
  16.    ' Open Recordset object with SQL statement.
  17.    Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)
  18.  
  19.    ' Display contents of Recordset object.
  20.    IdleOutput rstOrders, strCountry
  21.  
  22.    rstOrders.Close
  23.    dbsNorthwind.Close
  24.  
  25. End Sub
  26.  
  27. Sub IdleOutput(rstTemp As Recordset, strTemp As String)
  28.  
  29.    ' Call the Idle method to release unneeded locks, force 
  30.    ' pending writes, and refresh the memory with the current 
  31.    ' data in the .mdb file.
  32.    DBEngine.Idle dbRefreshCache
  33.  
  34.    ' Enumerate the Recordset object.
  35.    With rstTemp
  36.       Debug.Print "Orders from " & strTemp & ":"
  37.       Debug.Print , "OrderID", "CustomerID", "OrderDate"
  38.       Do While Not .EOF
  39.          Debug.Print , !OrderID, !CustomerID, !OrderDate
  40.          .MoveNext
  41.       Loop
  42.    End With
  43.  
  44. End Sub
  45.  
Hope this helps in some way good luck.
Very interesting Reply, Denburt. Have you ever actually used the Idle Method, and if so, what were your results? It appears as though it is used primarily in a Multi-user Environment to force the Database Engine to write data to disk, releasing memory locks. Nice Article.
Nov 27 '07 #4
I know it's been a while yet I just ran across this article and thought I would post something that may be a little more appropriate for your circumstances.

DBEngine.Idle dbRefreshCache

An example copied from the help file:
Expand|Select|Wrap|Line Numbers
  1. Sub IdleX()
  2.  
  3.    Dim dbsNorthwind As Database
  4.    Dim strCountry As String
  5.    Dim strSQL As String
  6.    Dim rstOrders As Recordset
  7.  
  8.    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
  9.  
  10.    ' Get name of country from user and build SQL statement 
  11.    ' with it.
  12.    strCountry = Trim(InputBox("Enter country:"))
  13.    strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
  14.       strCountry & "' ORDER BY OrderID"
  15.  
  16.    ' Open Recordset object with SQL statement.
  17.    Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)
  18.  
  19.    ' Display contents of Recordset object.
  20.    IdleOutput rstOrders, strCountry
  21.  
  22.    rstOrders.Close
  23.    dbsNorthwind.Close
  24.  
  25. End Sub
  26.  
  27. Sub IdleOutput(rstTemp As Recordset, strTemp As String)
  28.  
  29.    ' Call the Idle method to release unneeded locks, force 
  30.    ' pending writes, and refresh the memory with the current 
  31.    ' data in the .mdb file.
  32.    DBEngine.Idle dbRefreshCache
  33.  
  34.    ' Enumerate the Recordset object.
  35.    With rstTemp
  36.       Debug.Print "Orders from " & strTemp & ":"
  37.       Debug.Print , "OrderID", "CustomerID", "OrderDate"
  38.       Do While Not .EOF
  39.          Debug.Print , !OrderID, !CustomerID, !OrderDate
  40.          .MoveNext
  41.       Loop
  42.    End With
  43.  
  44. End Sub
  45.  
Hope this helps in some way good luck.
Denburt,

Thanks for responding to another one of my posts, even if it is an older one.

Also, I did (kind of) figure out a work around for my problem in this post. When quering a recordset, after the OpenRecordset method, the next line of code is a MoveLast method. This forces the query to finish and move the cursor to the last record before continuing with the rest of the code. Using your example above:

Expand|Select|Wrap|Line Numbers
  1.    ' Open Recordset object with SQL statement.
  2.    Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)
  3.  
  4.    'This forces the query to finsh and moves the curson to the last record
  5.    retOrders.MoveLast
  6.  
  7.    'Now I can continue execution of my code
  8.    rstOrders.MoveFirst
  9.  
  10.    Do until rstOrders.EOF
  11.  
  12.       'Do something with the recordset ...
  13.  
  14.    Loop
Thanks again for your help.

Scott
Nov 27 '07 #5
Denburt
1,356 Expert 1GB
Thanks, ADezii I use this quite a bit and I my results have always been positive.
Sphinney you may find this a bit quicker than trying to fully populate a large recordset.
Nov 27 '07 #6
ADezii
8,800 Expert 8TB
Thanks, ADezii I use this quite a bit and I my results have always been positive.
Sphinney you may find this a bit quicker than trying to fully populate a large recordset.
Thanks for the feedback.
Nov 27 '07 #7
Zwoker
66
Hi all,

I had the same problem where my dynamically built SQL selection was producing odd results. It was telling me it had found only 1 record when I knew there were more.

I was going slowly crazy trying to work out what was wrong with the WHERE portion of my SQL string until I found this thread.

So I added the rs.MoveLast after the OpenRecordset, and that worked fine until the first time that the query genuinely returned no records. The MoveLast then caused an error to pop up.

I was thinking of putting the MoveLast inside a check for the rs.RecordCount >0, but I'm not sure I can rely on this for the same reason I needed the MoveLast.

Does anyone have any ideas on this? Can I use the RecordCount?


Thanks.
Jan 3 '08 #8
ADezii
8,800 Expert 8TB
Hi all,

I had the same problem where my dynamically built SQL selection was producing odd results. It was telling me it had found only 1 record when I knew there were more.

I was going slowly crazy trying to work out what was wrong with the WHERE portion of my SQL string until I found this thread.

So I added the rs.MoveLast after the OpenRecordset, and that worked fine until the first time that the query genuinely returned no records. The MoveLast then caused an error to pop up.

I was thinking of putting the MoveLast inside a check for the rs.RecordCount >0, but I'm not sure I can rely on this for the same reason I needed the MoveLast.

Does anyone have any ideas on this? Can I use the RecordCount?


Thanks.
For an accurate Record Count, and the elimination of the MoveLast Error, try:
Expand|Select|Wrap|Line Numbers
  1. If MyRecordset.RecordCount > 0 Then
  2.   MyRecordset.MoveLast: MyRecordset.MoveFirst     'Traverse the Recordset
  3.   Debug.Print MyRecordset.RecordCount
  4. End If
P.S. - Take a few minutes and read this Tip:
Testing for an Empty Recordset
Jan 3 '08 #9
Zwoker
66
Hi ADezii,

I had already done the same thing by wrapping the MoveLast in a condition like you showed, and it seems to work fine.

What makes me nervous is what happens if there is only a few records that match the WHERE criteria in the SQL, and those records are near the end of whatever large data source is being queried?

If we need the MoveLast to stop the code executing beyond the OpenRecordset code before the query is really finished, might not the RecordCount still be zero for a little while until the records that match the WHERE are found?

If so, the code would check the condition for the RecordCount, see it was zero, and carry on with whatever is next, without having done the MoveLast / MoveFirst portion.

Or are we looking at two different things, one where the timing is sensitive (needing the MoveLast) and one that is not time sensitive (the RecordCount)?


Thanks.
Jan 3 '08 #10
Zwoker
66
Ok, I think I can answer my own question I asked in my previous post.

The reason I was getting a RecordCount on 1 is answered by this thread:
http://www.thescripts.com/forum/thread739089.html

It seems that it isn't a timing issue - waiting for the query to finish, simply the nature of the RecordCount property being either 0 or 1 until a MoveLast is done, which explainds why I thought I was finding only one record when I first encountered the problem.

So the code to check a RecordCount >0 should work fine. And putting the MoveLast & MoveFirst inside that check will protect them from a query that returns no records.
Jan 3 '08 #11
ADezii
8,800 Expert 8TB
Hi ADezii,

I had already done the same thing by wrapping the MoveLast in a condition like you showed, and it seems to work fine.

What makes me nervous is what happens if there is only a few records that match the WHERE criteria in the SQL, and those records are near the end of whatever large data source is being queried?

If we need the MoveLast to stop the code executing beyond the OpenRecordset code before the query is really finished, might not the RecordCount still be zero for a little while until the records that match the WHERE are found?

If so, the code would check the condition for the RecordCount, see it was zero, and carry on with whatever is next, without having done the MoveLast / MoveFirst portion.

Or are we looking at two different things, one where the timing is sensitive (needing the MoveLast) and one that is not time sensitive (the RecordCount)?


Thanks.
If it is a timing issue, than I imagine that you could always create a Clone of the Recordset and perform a RecordCount on that. Since it contains exactly the same data, but completely independent, that may work.
Jan 3 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Diamondback | last post: by
1 post views Thread by David Sorber via AccessMonster.com | last post: by
1 post views Thread by yujohan | last post: by
18 posts views Thread by Coder | last post: by
1 post views Thread by TF | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.