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

[sphinney]

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

[fauxanadu]

Put this in declaration section:

Option Compare Database
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Then put this where you want to wait:

Sleep (10000)

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:

Private Sub wait(intLoops as Integer)
    Dim i as Integer
    While i < intLoops
        i = i + 1
    Wend
End Sub

[Denburt]

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:

Sub IdleX()

   Dim dbsNorthwind As Database
   Dim strCountry As String
   Dim strSQL As String
   Dim rstOrders As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Get name of country from user and build SQL statement 
   ' with it.
   strCountry = Trim(InputBox("Enter country:"))
   strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
      strCountry & "' ORDER BY OrderID"

   ' Open Recordset object with SQL statement.
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)

   ' Display contents of Recordset object.
   IdleOutput rstOrders, strCountry

   rstOrders.Close
   dbsNorthwind.Close

End Sub

Sub IdleOutput(rstTemp As Recordset, strTemp As String)

   ' Call the Idle method to release unneeded locks, force 
   ' pending writes, and refresh the memory with the current 
   ' data in the .mdb file.
   DBEngine.Idle dbRefreshCache

   ' Enumerate the Recordset object.
   With rstTemp
      Debug.Print "Orders from " & strTemp & ":"
      Debug.Print , "OrderID", "CustomerID", "OrderDate"
      Do While Not .EOF
         Debug.Print , !OrderID, !CustomerID, !OrderDate
         .MoveNext
      Loop
   End With

End Sub

Hope this helps in some way good luck.

[ADezii]

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:

Sub IdleX()

   Dim dbsNorthwind As Database
   Dim strCountry As String
   Dim strSQL As String
   Dim rstOrders As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Get name of country from user and build SQL statement 
   ' with it.
   strCountry = Trim(InputBox("Enter country:"))
   strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
      strCountry & "' ORDER BY OrderID"

   ' Open Recordset object with SQL statement.
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)

   ' Display contents of Recordset object.
   IdleOutput rstOrders, strCountry

   rstOrders.Close
   dbsNorthwind.Close

End Sub

Sub IdleOutput(rstTemp As Recordset, strTemp As String)

   ' Call the Idle method to release unneeded locks, force 
   ' pending writes, and refresh the memory with the current 
   ' data in the .mdb file.
   DBEngine.Idle dbRefreshCache

   ' Enumerate the Recordset object.
   With rstTemp
      Debug.Print "Orders from " & strTemp & ":"
      Debug.Print , "OrderID", "CustomerID", "OrderDate"
      Do While Not .EOF
         Debug.Print , !OrderID, !CustomerID, !OrderDate
         .MoveNext
      Loop
   End With

End Sub

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.

[sphinney]

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:

Sub IdleX()

   Dim dbsNorthwind As Database
   Dim strCountry As String
   Dim strSQL As String
   Dim rstOrders As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Get name of country from user and build SQL statement 
   ' with it.
   strCountry = Trim(InputBox("Enter country:"))
   strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
      strCountry & "' ORDER BY OrderID"

   ' Open Recordset object with SQL statement.
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)

   ' Display contents of Recordset object.
   IdleOutput rstOrders, strCountry

   rstOrders.Close
   dbsNorthwind.Close

End Sub

Sub IdleOutput(rstTemp As Recordset, strTemp As String)

   ' Call the Idle method to release unneeded locks, force 
   ' pending writes, and refresh the memory with the current 
   ' data in the .mdb file.
   DBEngine.Idle dbRefreshCache

   ' Enumerate the Recordset object.
   With rstTemp
      Debug.Print "Orders from " & strTemp & ":"
      Debug.Print , "OrderID", "CustomerID", "OrderDate"
      Do While Not .EOF
         Debug.Print , !OrderID, !CustomerID, !OrderDate
         .MoveNext
      Loop
   End With

End Sub

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:

   ' Open Recordset object with SQL statement.
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL)

   'This forces the query to finsh and moves the curson to the last record
   retOrders.MoveLast

   'Now I can continue execution of my code
   rstOrders.MoveFirst

   Do until rstOrders.EOF

      'Do something with the recordset ...

   Loop

Thanks again for your help.

Scott

[Denburt]

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.

[ADezii]

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.

[Zwoker]

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.

[ADezii]

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:
[CODE=vb]
If MyRecordset.Rec ordCount > 0 Then
MyRecordset.Mov eLast: MyRecordset.Mov eFirst 'Traverse the Recordset
Debug.Print MyRecordset.Rec ordCount
End If[/CODE]
P.S. - Take a few minutes and read this Tip:
Testing for an Empty Recordset

[Zwoker]

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.

[Zwoker]

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.

[ADezii]

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.