Why does a task in VBA get executed before an earlier one has finished?

Question

As VBA-novice I'm having trouble understanding some VBA behaviour.

I got the following code, which reads rows from a db into an activex control, and thereafter tries to put these values into an array. the vxdata1 activex is proprietary, no insight there - but basicly it cares for the ADO connection, executing of an SQL command and putting the resultset into an control element.

//put all records from the db into the vxData1 datagrid
vxData1.SQLCommand = "SELECT x,y,z FROM t ORDER BY z"

//put the datagrid values into an array
Dim Array_Werte(500) As String
vxData1.MoveFirst
For i = 0 To 500 Step 1
   Array_Werte(i) = vxData1.Column1 & ";" & vxData1.Column2 & ";" & vxData1.Column3
   vxData1.MoveNext
Next i

Following problem arises: The array doesn't always contain all the data it should have (very unpredictable).

When I debug the code and go through the loop, everything works like a charm. So I concluded that the time consuming database query (large dataset) hasn't finished yet when I enter the loop.

Now I wonder: is that a problem with the code or is it a 'feature' of vba or scripting languages in general? I've seen stuff in the net like putting a User.wait there - but I can never know how long my query will take - and that seems like really bad style.

Answer 1

Not sure, but here's a few things to check:

• Is the ActiveX component an .exe or a .dll? I seem to remember in VB6 that dlls run 'in-process' and exe's run 'out-of-process', i.e. asynchronously. I'm not sure how this applies to VBA.
• Do you know whether your ActiveX component raise an event to say that it has completed its query? You could listen for that event to fire before moving into the loop.

Regards,

Chris