Wait for query to complete (1 Viewer)

[liddlem]

I am trying to update my address table using four separate queries.
I call each query from a docmd......thus

DoCmd.OpenQuery "QRY_UPDATE_POSSIBLE_CMTY_001"
DoCmd.OpenQuery "QRY_UPDATE_POSSIBLE_CMTY_002"
DoCmd.OpenQuery "QRY_UPDATE_POSSIBLE_CMTY_003"
DoCmd.OpenQuery "QRY_UPDATE_POSSIBLE_CMTY_004"

The problem is that although the first query does EXACTLY , It takes about 4 minutes to run. My query is .....

UPDATE Address, dbo_ADM_Postcode SET Address.ADDR6 = Trim([dbo_ADM_Postcode].[CMTY_Name])
WHERE (((Address.ADDR6)="") AND ((Address.ADDR2) Like ("*" & Trim([dbo_ADM_Postcode].[CMTY_Name]) & "*")) AND ((Address.ADRCITY) Not Like ("*" & Trim([dbo_ADM_Postcode].[CMTY_Name]) & "*")));

My concern is that the subsequent queries run before the first one is complete.
Is there any way (using vb) to check if/when query_001 is complete before issuing the subsequent statements?

 

[Galaxiom]

Some wild guesses.

Maybe you could do something with the RecordsAffected property of the querydef.

Another one to try would be to set up a Stored Procedure on the server and Execute it via ADO. Then test the State property.

 

[arnelgp]

your query will run in the order you put them in the code.

 

[Galaxiom]

your query will run in the order you put them in the code.

The table names in the original post suggest an SQL Server origin. While Access might wait to complete queries against Jet/ACE, it doesn't seem to wait for action queries to SQL Server to complete before continuing to execute code.

 

[isladogs]

Several suggestions

Try running a procedure where each query is run as a SQL statement in sequence
After each statement, add the line DoEvents

If possible, find ways of optimising the first query
Filtering with 4 wildcards will be slow - can you simplify?
Suggest you create a query to do the filtering first then run your update based on that

 

[arnelgp]

ok, if that so, create a Dummy function and pass any field name to it. therefore it will wait till all records in each queries are processed sequencially:

Public function fnVoid(fieldNameHere As Variant) As Variant
fnVoid=[FieldNameHere]
End Function


fieldname to pass must not be the one being updated in your query. say you update Field1=Field2*100, then you have another field Field3 which is not to be updated, you pass field3 to the function:

Update table1 Set Field1=Field2*100, Field3=fnDummy([Field3]);

 

[Pat Hartman]

Are you saying that query 2 starts before query 1 finishes?