ADO: Connection.Execute(ActionQuery). Will RecordsAffected ensure...?

[cravincreeks]

....that the action is completed before the next line of code executes?

I HAVE been reading the ADO documentation about this, but am having trouble following the example because I don't understand C++ very well.....hence, I'm asking here.

As a part of a long series of VBA/ADO/Access code, I'm updating values in an Access table with values in a linked Oracle table (using Microsoft ODBC for Oracle). The problem is, the update doesn't seem to finish before subsequent code executes. So if I pass a long integer as the records affected parameter in the execute method and then print the value of the long integer, will this GUARANTEE that the update completes before the next line of code is called, such as:

    'update tblTemp
    strSQL = "UPDATE TempForParcMatch INNER JOIN PARCEL ON TempForParcMatch.ParcelID = PARCEL.ID " & _
        "SET TempForParcMatch.X = [PARCEL].[X], TempForParcMatch.Y = [PARCEL].[Y];"
    'condb is my open connection
    condb.Execute strSQL, i
    Debug.Print i & " record(s) were affected by the Oracle Query"

Opitionally, feel free to provide a VB/VBA example of how to trap and use the ExecuteComplete event to guarantee the update is finished before the next line executes.

Thanks!

AZ

 

[JerryKlmns]

cravincreeks,

you could check the state of the connection

if condb.State = 4 'adStateExecuting

to make sure that it is done.

Where ever I have used bulk transactions like yours (all my dbs execute more than 300 transaction during updates), I haven't come up with an issue of a not completed statement.
Even those, concerning updating a table in database B from a linked txt file in that database, using a connection to database B opened from a database A on a network (a LAN though, not a WAN)

If you 'd like, count the records to be updated by the same select query, and then check to see if equals RecordsAffected of the executed bulk update. But this doubles traffic!

 

[cravincreeks]

Thanks for your reply. I thought about similar approach - checking the state using the ExecuteComplete event, but what do you do to make sure that then subsequent line(s) of code don't execute until the updates complete? Use a "Do While" loop until condb.State <> 4? That would seem to tie up a lot of system resources. However, I'd be surprised if this update ever takes place on more than 75 records and we only run this routine 3 time/mo - network traffic is not a major concern.

Thanks again! I'm open to all suggestions!

AZ