By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,060 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,060 IT Pros & Developers. It's quick & easy.

Overcoming Error 3218 Couldn't update; currently locked.

P: 2
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 :)
Sep 15 '08 #1
Share this Question
Share on Google+
3 Replies

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

other options are still welcome!
Sep 16 '08 #2

Expert Mod 15k+
P: 31,761
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.
Sep 16 '08 #3

Expert Mod 15k+
P: 31,761
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.
Sep 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.