Waiting for Insert/Update to finish

[EddieN1]

I have an Access 2007 database where I'm auditing changes to a database table that are made from within a form. I'm using the following technique:

1. Before update, I write the current table entry (row) to a temp table.

2. After update, I call a function that (a) copies the row from the temp table to the audit table (this is the before image) then (b) copies the row from the database table to the audit table (this is the after image).

The problem I am having is that unless I wait for the update of the database table to complete, the step 2b copies the row before the update has taken effect... even though is contained within the After Update event.

If I put a "wait" of 2 seconds before step 2b, it works perfectly.

It's as if the form issues the insert or update event, then proceeds executing VB instructions without waiting for the I/O event to finish. Does this make sense? Is there a way to tell Access to wait for the I/O to finish?

By the way, the tables are linked and contained in a "backend" access database.

[SteveH2508]

I would look at wrapping the whole lot in a transaction. this means that access/JET caches all changes in memory and then writes them to disc in one hit if you decide to 'accept' the transaction.

Warning - Air code

Set wrk = DBEngine(0)(0)
Set db = wrk.OpenDatabase("mydb.mdb")
wrk.BeginTrans
    'DO YOUR STUFF HERE
wrk.CommitTrans
db.Close
wrk.Close
Set db = Nothing
Set wrk = Nothing

[EddieN1]

Steve,

I stated the one situation with the database table as an example. This occurs in about 40-50 different forms that serve to update tables. This is not a multi-transaction type update where if one update should fail, then they should all roll-back. This is a simple update the table... that occurs in 40-50 forms.

Is there another technique you could suggest that would let the After Update event to finish before going to the next line of code?

Eddie

[SteveH2508]

Why not write the 'old' record to variables and then write both old and new together to the audit table? I don't understand why you need the temp table.

[EddieN1]

I'm not really looking for a work-around to this extent. I'm trying to (a) determine if I'm correct in thinking that the form orders the insert/update then proceeds on executing more lines of code without waiting for the I/O to complete and (b) if so, find a parameter or option to set that says something to the effect of "wait right here till the I/O is handled".

[SteveH2508]

DoEvents in your code immediately after the first insert code might help (though I would suggest that that IS a work-around).

I still do not understand why you need the temp table. It may cause bloating and the more disk read/writes the slower your database.

[EddieN1]

Steve, sorry I haven't been back for awhile. I am still looking for a way to ensure one table is finished updating before starting another process that expects the first to be completed. The reason the temp table is used for deletes is to handle deleting multiple records at once. Once the delete has begun, they're no longer available in the table unless the operator cancels the update. Eddie