Delete Query Error - The Table " is already opened exclusively by another user... (1 Viewer)

Capesy · Aug 19, 2019

Delete Query Error - The Table " is already opened exclusively by another user...

Hi,

So I have this sales database that I am trying to archive orders in. I have created an append query and and update query to get my desired rows into the archive table but when I try and run the delete query (with everything closed) I get the error: The table is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

Does anyone have any way of solving the error above or giving me another way to delete the rows in the table?

Thanks!

pbaldy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

Is there a form open bound to the table, or could other users? Most of us would recommend not archiving data to another table, just using a field in the table to flag them as archived.

Capesy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

No there is nothing open, everything is closed. And I am archiving to another table because I want to keep a record of the order as it was when it was closed so when the product price changes it does not affect the old orders.

pbaldy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

I think most of us store the order price with the order, which avoids that problem. If you archive the orders, you can't run queries as easily (you'd have to union together the archived and current orders).

What steps are you taking to do this? If there's code, what is it?

Capesy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

I was not aware that you could store the order price as it is based off a calculated field. How do you do that?

Currently I am appending the child tables, then the parent table. Then running an update query to mark them to be deleted and then running the delete query first on the child tables then on the parent table which gives the error

pbaldy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

Typically you'd have fields for the product ID, quantity and price (usually in a "details" table, with customer, date etc in a "header" table). When the user enters/selects the product, you go get the current price and drop it into the price field. Quantity times price is typically calculated on the fly, or perhaps with the new calculated field data type. One way to grab the price when they select product:

http://www.baldyweb.com/Autofill.htm

This is one of those times when you'd use the second method, as storing the price is appropriate here (it's an attribute of the sale as well as an attribute of the product).

Capesy · Aug 19, 2019

Re: Delete Query Error - The Table " is already opened exclusively by another user...

Actually my set up is a little more complicated than that with a few more necessary variables in the price calculation. The price for each customer is calculated with a discount and the discount is the thing that would be changing not the price of the item. That said the discount is not suppose to change often. The discount is not saved at any point in the order table and the total price is calculated with a number of querys (which add in shipping fees etc). Is there any way to save the final result of the total price query in a field in the table?

Delete Query Error - The Table " is already opened exclusively by another user... (1 Viewer)

Capesy

New member

pbaldy

Wino Moderator

Capesy

New member

pbaldy

Wino Moderator

Capesy

New member

pbaldy

Wino Moderator

Capesy

New member

Similar threads

Users who are viewing this thread