Access 2000: Run time error 3211


I'm having a problem similar to Q.10164777.  I've followed all the techniques described in that question to try and solve my problem, but it persists.

Here's the deal:  I have a make table query that creates a table called tblMusiclst.  It specifies recordlocks - edited record.  I have a form whose record source is that same table.  There's a button on the form that closes the form, then executes a the above mentioned make table query for tblMusiclst.  When the make table query runs I get the run time error 3211.  

It acts like the tblMusiclst is not being fully closed when I execute the close acform for that form.  Any ideas as to what's going on here or how I can get around it?
Who is Participating?
TrygveConnect With a Mentor Commented:
Happy that it worked out OK!

I think the reason that you could not get this working is the fact that as long as you are still in the code from the form, the form remains active.

Another possible cause is the fact that the code is executing much faster than the form can close, so if we don't test for the forms status, it may be still closing while we try to make changes to the table.
Don't use make table, but use an append query instead. Here is a list of things to do.

I think that your code will not be able to execute a delete of the table from its own code since the form is based on the table.

0) Don't close the form from your code
1) Run a delete query that removes all records
2) Run an append query to refill the table
3) Use me.requery to get the latest set of data into the form.
Alternative. Not so good.

1) From your form call a function or sub that is located in another code module. This has to loop till the form is really closed.
2) Close the form.
3) Now the other code can continue running the make table
4) Then reopen the form.

Another thing to consider is whether you have more than one user on the MDB. If you have, you can get problems if another user has the form open. These problems will be there regardless of which method you choose, but the first one will be most likely to give fewer crashes for the user that runs the code.

You should also consider if it is possible to do what you are trying to do using simple update and/or append queries instead of exchanging the whole dataset.
perstamAuthor Commented:

Your first solution worked just fine.  Many thanks!

Was just wondering if this is a design flaw in Access or does it work this way for a reason?

At any rate, please reply so I can allocate your points.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.