ADO - Do not continue until update SQL statement completes

[cravincreeks]

Hello all,

I'm executing an update SQL statement on an Access database with an Oracle table that is linked in the .mdb file. Then I need to programatically execute a select query on updated Access table. However, the update isn't completing before the subsequent Select query is run.

'condb is the connection object referencing the 
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & strGdbPath & ";"
Set condb = New ADODB.Connection
condb.Open strConn
    
condb.BeginTrans

strSQL = "UPDATE AccessTbl INNER JOIN OracleTbl On AccessTbl.ParcelID = OracleTbl.ID SET AccessTbl.X = OracleTbl.X, AccessTbl.Y = OracleTbl.Y;"

condb.Execute strSQL, i
debug.print i & "records affected"
condb.CommitTrans

'(The subsequent select statments are executed in ArcGIS using their geoprocessing object which, to my knowledge, do not support transactions.  Hence, I can't place it in the same transaction as the update query)

'I was thinking about doing something like this, which, admittedly, is very crude

Do Until condb.State <> 4 '4 = adStateExecuting 

Loop

'call geoprocessing select statements here

I'v also thought about trying to use the ExecuteComplete event on the ADOConnection object. I'm not too familiar with coding events....but it seems like trapping the 'status' argument in the event would still require some sort of while loop to prevent the following lines of code from executing until the update is complete and written to the Access table.

Thanks for any help you can provide! I'd like to know, both now and in the future, what the 'best practice' approach is in this situation.

Thanks

Alex

 

[PHV]

Anyway, inside the Loop I'd add this:
DoEvents

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

How about DoEvents?

Ken S.