Need to overwrite a table that is already being used. Is this possible?

[Colleyville Alan]

I have an app in which a ListBox is using a table called "Unique_Pla ns" as
its RowSource. On another form, I create some changes to one table and then
use a make-table query on the changed table to re-create the "Unique_Pla ns"
table. I get the following error message when I try this:

Run-time error '3211': The database engine could not lock table
"Unique_Pla ns" because it is already in use by another person or process.

I have tried setting the ListBox RowSource to "" first and then running the
query, thinking that this would release the table locks (since it is no
longer in use), but that does not work. Perhaps my whole approach is wrong.

Questions:
1 - Is there a way to overwrite the existing table without exiting the form?
2 - Is there a better way to accomplish what I want, i.e. to update the
table that is the RowSource of a ListBox and refresh the display with the
new records?

Thanks
Alan

[Bob Quintal]

"Colleyvill e Alan" <ae***********@ nospam.comcast. net> wrote in
news:OTYAc.6903 4$eu.19708@attb i_s02:

I have an app in which a ListBox is using a table called
"Unique_Pla ns" as its RowSource. On another form, I create
some changes to one table and then use a make-table query on
the changed table to re-create the "Unique_Pla ns" table. I
get the following error message when I try this:

Run-time error '3211': The database engine could not lock
table "Unique_Pla ns" because it is already in use by another
person or process.

I have tried setting the ListBox RowSource to "" first and
then running the query, thinking that this would release the
table locks (since it is no longer in use), but that does not
work. Perhaps my whole approach is wrong.

Questions:
1 - Is there a way to overwrite the existing table without
exiting the form? 2 - Is there a better way to accomplish what
I want, i.e. to update the table that is the RowSource of a
ListBox and refresh the display with the new records?

Thanks
Alan

The first approach would be to change the maketable query to an
update query, and create a delete rows query to run first. This
is also faster than deleting the table itself.

A better approach would be to simply update the changed rows.

The best solution, hoiwever, imo is to take your make tablequery,
change it to a select query and use that as the rowsource to your
combobox.

Bob Quintal

[Colleyville Alan]

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:f5******** *************** *******@news.te ranews.com...

"Colleyvill e Alan" <ae***********@ nospam.comcast. net> wrote in
news:OTYAc.6903 4$eu.19708@attb i_s02:

I have an app in which a ListBox is using a table called
"Unique_Pla ns" as its RowSource. On another form, I create
some changes to one table and then use a make-table query on
the changed table to re-create the "Unique_Pla ns" table. I
get the following error message when I try this:

Run-time error '3211': The database engine could not lock
table "Unique_Pla ns" because it is already in use by another
person or process.

I have tried setting the ListBox RowSource to "" first and
then running the query, thinking that this would release the
table locks (since it is no longer in use), but that does not
work. Perhaps my whole approach is wrong.

Questions:
1 - Is there a way to overwrite the existing table without
exiting the form? 2 - Is there a better way to accomplish what
I want, i.e. to update the table that is the RowSource of a
ListBox and refresh the display with the new records?

Thanks
Alan

The first approach would be to change the maketable query to an
update query, and create a delete rows query to run first. This
is also faster than deleting the table itself.

A better approach would be to simply update the changed rows.

The best solution, hoiwever, imo is to take your make tablequery,
change it to a select query and use that as the rowsource to your
combobox.

Bob Quintal

Thanks