By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

Could not delete from specified tables. (Error 3086)

P: 3
Hi
Have looked through many forums but cannot find a solution for this.

I have a table where i store certain dates [DATES] and i want to use this table in several queries to delete records from multiple tables by using a macro to run the queries.
I can use DISTINCTROW to delete rows that are equal to a date but cannot figure out how to delete rows that are between a range of dates (stored in [DATES].
Example of my query :

DELETE ALLAccounts18MONTHS.*, ALLAccounts18MONTHS.Date
FROM ALLAccounts18MONTHS, DATES
WHERE (((ALLAccounts18MONTHS.Date) Between [DATES]![CurrWeek78StartDate] And [DATES]![CurrWeek78EndDate]));

ALLAccounts18MONTHS is one of the tables i want to delete rows from
DATES is the master file holding dates

Can anyone advise.
Thanks
Martin
Jul 27 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What do you mean by file?
Jul 27 '07 #2

P: 3
Sorry
DATES is a master table within the same database.

Thanks
Martin
Jul 31 '07 #3

JKing
Expert 100+
P: 1,206
Hi, I believe the problem is that you've included the table DATES in your FROM clause. I understand the logic behind placing it there however Access doesn't like it because it's trying to delete from it as well. To get the values you'll need to use subqueries.

Give this a try:
Expand|Select|Wrap|Line Numbers
  1. DELETE ALLAccounts18MONTHS.*, ALLAccounts18MONTHS.Date
  2. FROM ALLAccounts18MONTHS
  3. WHERE ALLAccounts18MONTHS.Date 
  4. BETWEEN (SELECT [CurrWeek78StartDate] FROM DATES) 
  5. AND (SELECT [CurrWeek78EndDate] FROM DATES);
  6.  
Jul 31 '07 #4

P: 3
JKing

Thank you that works a treat. I am a newbie with SQL, so on a learning curve at the moment.
I can now save myself from editing about 20 queries everyday.
Aug 2 '07 #5

JKing
Expert 100+
P: 1,206
JKing

Thank you that works a treat. I am a newbie with SQL, so on a learning curve at the moment.
I can now save myself from editing about 20 queries everyday.
It can take a while to learn. Somethings which seem totally logical just aren't always the case. It's good that you are learning your SQL though it's very important. Too many people simply use the design view not knowing about the SQL behind it and run into problems when they need to do complex things.

Good luck and keep at it!

Jking
Aug 2 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.