database could not lock table because database is in use by another person or process

Question

I posted this back on 7/7 but no reply - anyone?????

I have a main form and a subform -  the subform record source is based off of a table created from a Make table query.  I have it set up that the user check off some Select field boxes in the Subfrom and I want the total price that shows up on the Main form - AFTER I run 3 make table queries - I base the subform off of One of the Maketables that I have to RERUN after the user SELECT some of the Check Boxes -I have tied all of the make table queries to a command button on the Main Form- I am getting an error - database could not Lock table because database is in use by another person or process- think it is because the subform is based off of the table created from the Make table- so opened???? Even tried to base the subform on a query based on the make table table but still get the same error.- All of the Make tables queries give Me all I want - just as long ast the Subform is not visible - but need the user to see this - more user friendly than having them navigating around Objects

Any ideas ???

thanks,

bkel

If I close the form and run - the selected  check boxes are not there when come back in?? even when i save the records

Answer 1

assuming you are suggesting do a delete query then an append query - in place of the make table query???

 

Unforutnately they are used to and set on doing the make table query - I tried to base the subform off of a query from the make-table query but still get same error - any other ideas????

thanks,

bkel

1. Use of make table queries cause greater bloat than just deleting and appending data (although both cause bloat).
2. If a table that is created by any make table query is in use at all (open in subform or row source of combo box, etc. it cannot be deleted and re-made.  That is the benefit of deleting data and appending (and then requerying any forms or controls that use it) because it can be done while the table is in use.
3. A table is also considered in use if it is open in design view at all.

Answer 2

thanks - I did just end up modifying the make table query to a delete and append query!!!! It worked!!

thanks,

bkel

Glad it workd for you.  You should mark the post that suggested that as the answer so that it gets classified for users searching for similar issues.

Answer 3

thanks - I did just end up modifying the make table query to a delete and append query!!!! It worked!!

thanks,

bkel

Answer 4

assuming you are suggesting do a delete query then an append query - in place of the make table query???

Unforutnately they are used to and set on doing the make table query - I tried to base the subform off of a query from the make-table query but still get same error - any other ideas????

thanks,

bkel

Answer 5

Hmmm, instead of MAKE TABLE queries, why not empty the tables and then refill them with the current information?  Then just have to REQUERY.