Connection to Access Won't Wait

[doasidont]

I know many people have asked this question and I find different suggestions but am having no luck in finding a good solution. I have a connection query to an Access database that loads a query table on a spreadsheet. I've tried varous ways (Wait, Sleep, Do Untils, etc.) to delay subsequent VBA code from running until the query refresh completes, but can't find a way that works. Is there a way to tell when the query table has been fully refreshed so that code execution can continue?

The connection query executes automatically when its parameter changes. This parameter is changed by some code and then my code immediately does following:
With Sheets("Query Range")
Set Rcd = .Range("myRange").Find(myRcd)

(where myRcd is some value that is expected via the refresh and myRange is a column on the 'Query Range" sheet that contains the query table and should contain myRcd)

Rcd always returns 'Nothing'. Like I said, I've tried waiting, do loops with embedded 'sleep's, but can't find a way to delay the 'Set' execution until the query has been refreshed.

Thanks for your help.

[vbfbryce]

Could you have your code execute when the worksheet_change event fires for a specific cell?

[doasidont]

Do you mean a specific cell within the query range? Thanks.

[doasidont]

I tried to set a worksheet_change event on the sheet that receives the data from the query execution, meaning as soon as the query table was refreshed, the event would occur. The event simply does a msgbox 1. The query executed, the processing code that I want to delay executed, and then the msgbox fired. I needed the msgbox to fire after the processing code.

[westconn1]

I've tried varous ways (Wait, Sleep, Do Untils, etc.)

as you do not post any examples of what you have tried, this should work

do 
   Set Rcd = .Range("myRange").Find(myRcd)
   doevents
loop while Rcd is nothing

or if you want to use the worksheet change event, you should use a form level variable, again with doevents

do until myrefresh
  doevents
loop
Set Rcd = .Range("myRange").Find(myRcd)

in the worksheetchange myrefresh = true

[doasidont]

See my next reply.

[doasidont]

Thanks for your suggestions, but I stll can't get this to work. Here's what I have:
1) an MS Access Query that imports data onto a sheet called 'Web Data'. This query is allowed to run in background and fires whenever it's parameter changes.
2) a worksheet, called Param, that contains a cell that holds the query's parameter. When this cell changes, the query fires, pulling data onto the 'Web Data' sheet.
3) another worksheet that contains data. This worksheet is set up with a before double click event so that when a cell is double clicked, another cell on same row is copied to the 'parameter' cell (which fires the query).
4) At this point, I search the 'Web Data' sheet for a value, which I expect to be there when the query refresh completes.

No matter what I try, including what I think has been suggested, the search always fails because the code that searches the 'Web Data' sheet seems to execute immediately after the parameter change; i.e., the query hasn't yet been refreshed.

Maybe I am not implementing your suggestions correctly. Could you please elaborate? Which sheet has the event and what code should the event execute? Should the query be set to not run in background or should it not refresh automatically upon parameter change? Thanks.

[doasidont]

Thanks for your suggestions, but I stll can't get this to work. Here's what I have:
1) an MS Access Query that imports data onto a sheet called 'Web Data'. This query is allowed to run in background and fires whenever it's parameter changes.
2) a worksheet, called Param, that contains a cell that holds the query's parameter. When this cell changes, the query fires, pulling data onto the 'Web Data' sheet.
3) another worksheet that contains data. This worksheet is set up with a before double click event so that when a cell is double clicked, another cell on same row is copied to the 'parameter' cell (which fires the query).
4) At this point, I search the 'Web Data' sheet for a value, which I expect to be there when the query refresh completes.

No matter what I try, including what I think has been suggested, the search always fails because the code that searches the 'Web Data' sheet seems to execute immediately after the parameter change; i.e., the query hasn't yet been refreshed.

Maybe I am not implementing your suggestions correctly. Could you please elaborate? Which sheet has the event and what code should the event execute? Should the query be set to not run in background or should it not refresh automatically upon parameter change? Thanks.

[Zvoni]

I would try the following:
Create a Class in your Excel-Code, and move the query-relevant code there
Define an Event in this Class
Execute the Query inside this class, then populate your Web-Data-Sheet from this class.
After populating your Sheet is finished, fire your Event.
You can recieve this event inside your code of the Sheet, and from there you could try to use your Find-Method

[vbfbryce]

What Zvoni suggests makes sense. If you still want to pursue your original direction, I think a worksheet_change event in the "web data" sheet is what could work.

[Zvoni]

Bryce, the problem with this would be, that the event fires everytime a cell in this sheet is populated.
Or am i missing something?

[vbfbryce]

He could look for a specific cell to be populated maybe? Hard to tell without seeing more of what he's got going on so far.

If I were starting from scratch, I'd definitely go the route you suggest, though.

[Zvoni]

hmmm, so since he knows what the query consists of, he would know how many Columns his recordset is going to have (Field-Count?).
With checking the RecordCount (Rows) before populating his sheet he would also know the number of rows.

In this case he could set an If-Clause in the Worksheet_Change-Event checking the Target-Argument if his most bottom right cell has been populated (last column of last recordset), assuming he is populating from left to right and from top to bottom.

[doasidont]

Thanks. I'm not understanding the suggestion by Zvoni. I've tried setting a change event on the 'Web Data' sheet. I've also first cleared the Web Data sheet and then checked to see of any rows were populated with the query before continuing with code, but these don't work either. I have found something, though, that appears to work and it's quite simple. I've change the query to disallow background (asynch) refresh. With this, the next line of code after my ActiveWorkbook.RefreshAll does not execute until the 'Web Data' page is fully refreshed. I appreciate all the suggestions, though.

Before I close this, I have used a 'Shell and Wait' routine before to make sure processing was done in a single thread mode. What would my DoCmd look like if I were to use 'Shell and Wait'? By the way, if I loop through all the worksheets, my query table count is always zero. Why is that?

[vbfbryce]

This is an example along the lines of what Zvoni suggests (I think...chime in if I'm wrong, Z!). The code runs an SQL select statement against an Access DB (reference to Microsoft Access Object Library is required) and populates the results into the active worksheet, then gives you a message indicating how many records were returned.

Sub runQuery()
    Dim varConn As String
    Dim varSQL As String

    Range("A1").CurrentRegion.ClearContents
    
    varConn = "ODBC;DBQ=vbmain.mdb;Driver={Driver do Microsoft Access (*.mdb)}"     'connect to my Access DB
    varSQL = "SELECT tblnames.f1 FROM tblNames"     'simple SQL select statement, selects F1 (field 1) from tblNames

    With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))     'place SQL results in A1 (active sheet)
             .CommandText = varSQL
             .Name = "Query-39008"
             .Refresh BackgroundQuery:=False
    End With
    MsgBox "Rows in Excel: " & Range("a1").End(xlDown).Row - 1      'show how many records got populated
End Sub

[Zvoni]

Bryce,

i was more thinking along something like this

Dim MyColumns As Long
Dim MyRows As Long

Private Sub DoQuery()

    'Do your Query here
    MyColumns=MyRecordset.Fields.Count
    MyRows=MyRecordset.RecordCount
    'I'm using the DAO-Descriptions. In ADO they might have different names

   'Do your WebData-Sheet-Population here

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

     'He'd have to fetch from his Query the Number of Columns (=Fields) and Rows (=RecordCount)
     If Target.Column=MyColumns And Target.Row=MyRows then
     
           DoMyFindRoutine

     End If

End Sub

[doasidont]

Thanks. I really appreciate your help with this, but I'm afraid I still don't understand. I have a Data Connection on my Excel workbook. The connection can be started by either changing its parameter (if I set the parameter option to start the connection if it is changed), or by issuing a ActiveWorkbook.RefreshAll. In both cases, when the query is started and it is allowed to run in background (i.e., asynchronously), then the instruction immediately following the one that changes the parameter (if the parameter change option is set) or the one immediately following the ActiveWorkbook.RefreshAll instruction, is executed. I believe I've tried the test where I compare the Web Data row counts before (row count = 0) and after the connection query runs, expecting the count to be > 0. But it does not work. When the test is made, the record count is as if the query has not yet completed. I've even tried a sleep loop in my 'find' routine, waiting for the query to complete. It just hangs in the loop.

My solution to disallow query background execution seems to work. I just wish there was a way to test when the query has completed.

But then maybe I don't understand how to implement your last suggestion. What's the difference between 'do web query here' and 'do webdata populating here? Aren't they the same?