Force vba to wait for sql query to execute before continuing

Question

This seems to be a fairly common problem but none of the the solutions I've found seem to work.

I'm grabbing some data from SQL Server and copying it into a worksheet. Then I want to copy a range from the new data and do other stuff with it. All of this happens in a single vba function.

My problem is when the function is run from Excel it moves onto the second part of the function without waiting for the query to return the required data.

Of course the function works fine when I run it from the vba IDE.

Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset

a.Open (connStr)
Set r = a.Execute(sqlstr)

sht.Range("A2").CopyFromRecordset r

'please wait here until the proc has executed?

checkData = sht.Range("A2").Value

When I run the function from Excel checkData is always empty, when I run it with F5 it always has the required data.

Answer 1

Try using:

Application.CalculateUntilAsyncQueriesDone

after you execute the SQL, but before you copy the RecordSet

Set r = a.Execute(sqlstr)
    Application.CalculateUntilAsyncQueriesDone
sht.Range("A2").CopyFromRecordset r