Overcoming Error 3218 Couldn't update; currently locked.

[kasinisak]

I have created a Sub to export a query to a text file in the following steps:
1. Before the text file can be generated, certain checks must be made to ensure the correct result.
2. Generate a text file from a pre-built query
3. Update 'status' fields in three tables to reflect the change made by the code

The problem I now encounter is that sometimes error 3218 occurs when the user left a table opened with some record changed (a.k.a. made some record 'dirty').

If the said record happens to be the same one to be updated by an SQL statement from VBA code, the error will cause the 'status' field to be inaccurate (even though the text file has been successfully generated earlier before the updateStatus() sub is called).

I tried to check if the error is Err.Number 3218 then DoSomethingOrStopIt() but the problem is this check happens too late in the program (actually this is the last Sub to be called). I wonder if I can write some 'dummy data' to these tables to see if I can update it? I tried something like this:

UPDATE myTable set myField = myField

But this didn't work, probably because the transaction didn't happen at all because this transaction didn't generate any errors but when the last Sub tried to update the same myTable, the 3218 error occured.

Please advise :)

[kasinisak]

currently my workaround solution is to DoCmd.close all related tables :P

other options are still welcome!

[NeoPa]

Interesting question.

I would expect the UPDATE process to work (IE throw an error) if any of the records were in use.

Another technique (not 100% but close) would be to open a recordset of the data with a full lock on the table before you do the update, then either close the recordset before running the UPDATE SQL, or even effecting the update from within the recordset.

[NeoPa]

Thinking about this further, it may be worth opening a recordset (full, exclude read/write, lock) of just the single record that you intend to update. That way the locking will be less intrusive on other operations. Another option, on the full table recordset, would be to call the .Edit on the relevant record before the export is done.

Last thought. Transactions may also work here to manage a consistent update. This may even be the best solution, but I have little experience there.