Could not delete from specified tables. (Error 3086)

[MRounds]

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

[Rabbit]

What do you mean by file?

[MRounds]

Sorry
DATES is a master table within the same database.

Thanks
Martin

[JKing]

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:

DELETE ALLAccounts18MONTHS.*, ALLAccounts18MONTHS.Date
FROM ALLAccounts18MONTHS
WHERE ALLAccounts18MONTHS.Date 
BETWEEN (SELECT [CurrWeek78StartDate] FROM DATES) 
AND (SELECT [CurrWeek78EndDate] FROM DATES);
 

[MRounds]

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.

[JKing]

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