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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.