Could not delete from specified tables error (1 Viewer)

VygonDBA

Registered User.
Local time
Today, 05:03
Joined
Oct 24, 2012
Messages
12
Hi all,
I'm trying to delete data (no archiving required) from 5 tables that all have a one-to-many relationship between them and keep getting the following error: "Could not delete from specified tables".
I've tried everything I can think of including the following:
* Set unique records property to 'Yes'
* Enable referential integrity and cascade delete records in the relationship diagram
* Checked the DB isn't read only
* Have correct permissions to delete records

I'm the only person in the database but I'm at a loss and short of me actually deleting the 23891 records manually I can't think of what else to do!

Can anyone please advise?

Many thanks in advance.
 

pr2-eugin

Super Moderator
Local time
Today, 05:03
Joined
Nov 30, 2011
Messages
8,494
Have you enforced Referential Integrity between the tables that have One-Many relationship? If yes, you only need to delete the Parent. The child will be deleted automatically.
 

VygonDBA

Registered User.
Local time
Today, 05:03
Joined
Oct 24, 2012
Messages
12
Thanks for your quick reply.
Yes, I've enabled referential integrity and cascade delete between the relevant tables.

Something I forgot to mention(!) is that the delete needs to be based on an inner join between two tables, one that holds patient details and the other that holds delivery details and dates. The delete statement should remove all non-active patients ('status' field in Patient Details table) that have not had a delivery since 31/10/2011 ('date' field in Deliveries table).

I can do this inn SQL Server but am having a hell of a time with Access!
 

pr2-eugin

Super Moderator
Local time
Today, 05:03
Joined
Nov 30, 2011
Messages
8,494
Then try using SubQueries (not my expertise) but what you need to do is SELECT the child table unique ID, based on the inner join and your condition and use this recordset as the main DELETE query's subQuery.. http://allenbrowne.com/subquery-01.html
 

VygonDBA

Registered User.
Local time
Today, 05:03
Joined
Oct 24, 2012
Messages
12
Sounds good, I'll give it a go, hopefully it'll get around the problem.

Many thanks for your help again.
 

Users who are viewing this thread

Top Bottom