Updating table data while in use

[crispy-bacon]

On one of my forms there is currently a listbox that is populated by a Query that uses multiple other tables/queries.

I have been running into a problem where I try to update one of the tables (using a button on the form) by running a "Make table" query that will delete the table and repopulate it with the new values. This does not work as i get the message:

"The database engine could not lick table 'tableName' because it is already in use by another person or process."

I figure this is because the listbox/query is using the data in the table. So my question is will an "Update" query return the same problem and if so is there a way around this error (to edit the data in the table while the listbox is using it)?



Thanks

[June7]

UPDATE or INSERT should not be issue. If you want the listbox to show the new values then requery the listbox.

[crispy-bacon]

Okay thanks for the quick response, so from that in theory the following should work:

Update all fields/records to 0 (clearing the table) then INSERT INTO from a query to fill my table with values, then requery the listbox.

I feel like there is probably a better way to do this but oh well.

[June7]

Actually, if what you want is an empty table then DELETE FROM tablename should work. UPDATE will leave a bunch of records with 0 in every field. Then the INSERT will append more records an addition to all those 0 records.

[crispy-bacon]

Perfect thanks, yeah didn't think of that. I'll try it out and post the results.

Thanks for your help

[crispy-bacon]

Yep everything worked perfectly, thanks a lot.