Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-19-2019, 06:27 AM   #1
Capesy
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Capesy is on a distinguished road
Post 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!

Capesy is offline   Reply With Quote
Old 08-19-2019, 06:31 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,098
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-19-2019, 06:44 AM   #3
Capesy
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Capesy is on a distinguished road
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.

Capesy is offline   Reply With Quote
Old 08-19-2019, 06:58 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,098
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
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?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-19-2019, 07:07 AM   #5
Capesy
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Capesy is on a distinguished road
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
Capesy is offline   Reply With Quote
Old 08-19-2019, 07:20 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,098
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
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).
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-19-2019, 08:25 AM   #7
Capesy
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Capesy is on a distinguished road
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?


Capesy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Opened exclusively error on split database? gmworrall Theory and practice of database design 3 09-19-2012 05:14 AM
form cannot open because table is opened exclusively... Lifeseeker Forms 4 04-04-2011 03:09 PM
Error: 'Training_List' already opened exclusively by another user scooteman General 5 12-14-2009 10:48 AM
Run time error - table opened by other user...?! Stretch617 Forms 2 07-24-2009 07:50 AM
Table is already opened exclusively by another user FairTradeBanana Modules & VBA 1 11-01-2007 12:26 PM




All times are GMT -8. The time now is 08:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World