Delete Query Doesn't Redux

[Ron Le Blanc]

Attached are the DB table relationships.

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

I wish to delete all clients that have not been seen for two years or more.
A "meal" table contains the last visit date.

The idea is to find all clients who have not been seen in two or more years.
The data sheet view shows that the correct clients are selected. However,
when I try to run the delete query it says it cannot delete the records. The
"client" table is the table to which all other tables are related to and
have the cascade delete option on.

I don't care if I loose all the data for a client. If the client has not
been in
in two years a new record would have to be created anyway.

The SQL generated is:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));

The error message says: "Cannot delete from table".

Does anyone have an idea as to why this does not work and what might be done
to make it work??

Thanks!

 

[Marshall Barton]

Attached are the DB table relationships.

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

I wish to delete all clients that have not been seen for two years or more.
A "meal" table contains the last visit date.

The idea is to find all clients who have not been seen in two or more years.
The data sheet view shows that the correct clients are selected. However,
when I try to run the delete query it says it cannot delete the records. The
"client" table is the table to which all other tables are related to and
have the cascade delete option on.

I don't care if I loose all the data for a client. If the client has not
been in
in two years a new record would have to be created anyway.

The SQL generated is:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));

The error message says: "Cannot delete from table".

Does anyone have an idea as to why this does not work and what might be done
to make it work??

I think it's because you have multiple tables/fields in the
query's field list, For a DELETE query, you only want to
specify the table with the records to delete:

DELETE client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE meals.LastVisit < (Date()-730)

 

[Ron Le Blanc]

The SQL code below generates a "Cannot delete from specified table" error.

I have double checked and all the tables are linked to the client table and
the cascade delete is on.

I can also go to the client table, select a test victim and delete the
record and all associated records in the other tables. In short, it works
just fine manually.

Unfortunately, I need to be able to delete the records from a query
automagically.

 

[Marshall Barton]

I can not explain that.

John is better at this kind of thing than I am, maybe he can
spot the problem?

 

[Chris2]

Attached are the DB table relationships.

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

Thanks!

Ron Le Blanc,

May I ask what did not work for you with the solution that I presented
earlier?


Sincerely,

Chris O.

PS I reproduce it below.
-----------------------------------------------------------

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

I wish to delete all clients that have not been seen for two years or more.
A "meal" table contains the last visit date.

Thanks!

Ron Le Blanc,

Please forgive the dates appended to the table names.

MasterID looks like the Primary Key of client, so I'm using it that
way.

CREATE TABLE client_10242005_1
(MasterID AUTOINCREMENT
,f_name TEXT(10)
,l_name TEXT(10)
,CONSTRAINT pk_client_10242005_1 PRIMARY KEY (MasterID)
)

CREATE TABLE meals_10242005_1
(mealsID AUTOINCREMENT
,MasterID LONG
,LastVisit DATETIME
,CONSTRAINT pk_meals_10242005_1 PRIMARY KEY (mealsID)
,CONSTRAINT fk_meals_10242005_1
FOREIGN KEY (MasterID)
REFERENCES client_10242005_1 (MasterID)
)

Open the relationships window and show both tables. Right-click on
the relationship link. Check both boxes for cascading on DELETES and
UPDATES.


SampleData

client_10242005_1
1, Jay, Smith
2, Dee, Smith
3, Ray, Smith


meals_10242005_1
1, 1, 01/01/2001
2, 1, 01/01/2002
3, 1, 01/01/2003
4, 2, 01/01/2001
5, 2, 01/01/2002
6, 2, 01/01/2004
7, 3, 01/01/2004
8, 3, 01/01/2005
9, 3, 01/10/2005

Expectations:

By dates, client 1 will need to be deleted.

The original query:

I've added table aliases to make my lengthy date-appended table names
more manageable.

(I got rid of "*" from the DELETE clause.)

DELETE C1.MasterID
,M1.LastVisit
,C1.f_name
,C1.l_name
FROM client_10242005_1 AS C1
INNER JOIN
meals_10242005_1 AS M1
ON C1.MasterID = M1.MasterID
WHERE (((m1.LastVisit)<Date()-730))

You're correct, the error message: "Could not delete from specified
table." was returned.


Well, let's try something else.

DELETE C1.*
FROM client_10242005_1 AS C1
WHERE C1.MasterID =
(SELECT DISTINCT
M1.MasterID
FROM meals_10242005_1 AS M1
WHERE M1.MasterID = C1.MasterID
AND (Date() - 730) > ALL
(SELECT M01.LastVisit
FROM meals_10242005_1 AS M01
WHERE M01.MasterID = C1.MasterID
)
)


This appears to have deleted Mr. Jay Smith, MasterID 1, from client.

Because we've got cascading deletes going, the meals table got it's
rows for Mr. Jay Smith deleted, as well.

Let me know what happens, and back up your data before trying it.


Sincerely,

Chris O.
-----------------------------------------------------------