Hello,

I have a query to delete records that match from the 2 tables contained.
When I view the result of the query before running, it looks like it is going
to work however when I actually come to run it, I get the error 'Could Not
Delete From Specified Tables'. After reading the Help pages, I unchecked the
box 'Open Databases using record-level locking' from the Advanced Options tab
and this hasn't helped. Is it the case that I will need to close Access for
the tables to update before the query will run properly? Here is the SQL:
DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
Expr1
FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
Retention.POLICY;

Many thanks.
Chris

Perhaps a subquery would do the trick:

DELETE FROM [Daily Check]
WHERE EXISTS
(SELECT policy
FROM Retention
WHERE Retention.Policy = [Daily Check].policy);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris Stammers" <(E-Mail Removed)> wrote in message
news:F33C1A17-71B2-4C80-B216-(E-Mail Removed)...
> Hello,
>
> I have a query to delete records that match from the 2 tables contained.
> When I view the result of the query before running, it looks like it is
> going
> to work however when I actually come to run it, I get the error 'Could Not
> Delete From Specified Tables'. After reading the Help pages, I unchecked
> the
> box 'Open Databases using record-level locking' from the Advanced Options
> tab
> and this hasn't helped. Is it the case that I will need to close Access
> for
> the tables to update before the query will run properly? Here is the SQL:
> DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
> Expr1
> FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
> Retention.POLICY;
>
> Many thanks.
> Chris

YOu can only delete records from ONE table at a time.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

To delete records from Daily Check the query might look like
DELETE
FROM [Daily Check]
WHERE policy in (SELECT Policy From Retention)

To delete records from Retention the query would look like
DELETE
FROM Retention
WHERE policy in (SELECT Policy From [Daily Check])

You *MIGHT* be able to get this to work. Note that only one table is
mentioned in the DELETE clause.
DELETE DISTINCTROW Retention.*
FROM [Daily Check] INNER JOIN Retention
ON [Daily Check].policy = Retention.POLICY;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Chris Stammers wrote:
> Hello,
>
> I have a query to delete records that match from the 2 tables contained.
> When I view the result of the query before running, it looks like it is going
> to work however when I actually come to run it, I get the error 'Could Not
> Delete From Specified Tables'. After reading the Help pages, I unchecked the
> box 'Open Databases using record-level locking' from the Advanced Options tab
> and this hasn't helped. Is it the case that I will need to close Access for
> the tables to update before the query will run properly? Here is the SQL:
> DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
> Expr1
> FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
> Retention.POLICY;
>
> Many thanks.
> Chris

Many thanks for your help. Your last option worked fine.

Regards,
Chris

"John Spencer (MVP)" wrote:

> YOu can only delete records from ONE table at a time.
>
> STEP 1: BACKUP your data before attempting the following.
> STEP 2: BACKUP your data before attempting the following.
>
> Without a backup you cannot restore the data if this does not work the way you
> expect.
>
> To delete records from Daily Check the query might look like
> DELETE
> FROM [Daily Check]
> WHERE policy in (SELECT Policy From Retention)
>
> To delete records from Retention the query would look like
> DELETE
> FROM Retention
> WHERE policy in (SELECT Policy From [Daily Check])
>
> You *MIGHT* be able to get this to work. Note that only one table is
> mentioned in the DELETE clause.
> DELETE DISTINCTROW Retention.*
> FROM [Daily Check] INNER JOIN Retention
> ON [Daily Check].policy = Retention.POLICY;
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Chris Stammers wrote:
> > Hello,
> >
> > I have a query to delete records that match from the 2 tables contained.
> > When I view the result of the query before running, it looks like it is going
> > to work however when I actually come to run it, I get the error 'Could Not
> > Delete From Specified Tables'. After reading the Help pages, I unchecked the
> > box 'Open Databases using record-level locking' from the Advanced Options tab
> > and this hasn't helped. Is it the case that I will need to close Access for
> > the tables to update before the query will run properly? Here is the SQL:
> > DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
> > Expr1
> > FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
> > Retention.POLICY;
> >
> > Many thanks.
> > Chris
>

Thanks for your help with this.

Regards,
Chris

"Allen Browne" wrote:

> Perhaps a subquery would do the trick:
>
> DELETE FROM [Daily Check]
> WHERE EXISTS
> (SELECT policy
> FROM Retention
> WHERE Retention.Policy = [Daily Check].policy);
>
> If subqueries are new, see:
> http://allenbrowne.com/subquery-01.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris Stammers" <(E-Mail Removed)> wrote in message
> news:F33C1A17-71B2-4C80-B216-(E-Mail Removed)...
> > Hello,
> >
> > I have a query to delete records that match from the 2 tables contained.
> > When I view the result of the query before running, it looks like it is
> > going
> > to work however when I actually come to run it, I get the error 'Could Not
> > Delete From Specified Tables'. After reading the Help pages, I unchecked
> > the
> > box 'Open Databases using record-level locking' from the Advanced Options
> > tab
> > and this hasn't helped. Is it the case that I will need to close Access
> > for
> > the tables to update before the query will run properly? Here is the SQL:
> > DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
> > Expr1
> > FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
> > Retention.POLICY;
> >
> > Many thanks.
> > Chris
>
>