Delete Query with multiple relationships 2

[TB0NE]

I am trying to run a delete query in a database with 34 tables. 33 tables relate back to the main table which also contains the only primary key in the database.

I created a query to delete records from the main table (Let's call it 'ComputerInfo') but I get the error "Could not delete from specified tables".

I think my problem is due to the one-to-many relationship between the tblComputerInfo and the rest of the tables.

I cannot manually delete a single record from tblComputerInfo but I can manually delete records from all other tables.

What is the best way to delete old records from this database? Can a delete query be used in this scenario?
Also, how do I insure that the old records are deleted from the other tables in the DB?

Any and all suggestions are welcome.

Thank you!
TB0NE

 

[PHV]

Take a look at something like cascading in the Relations windows.
The spelling is probably different, but I'm not using an english version of office.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

Can you go into datasheet view for the main table and delete the record you are trying to delete with your query?

 

[TB0NE]

PHV and BNPMike,

I turned on cascading deletes and can now delete records from the datasheet view. My delete query still doesn't work though.

It's strange, because I made a copy of the ComputerInfo table and was able to successfully run the delete query against it.

I don't know squat about SQl, but maybe it will help you to see it. Here is my delete query in SQL view:

DELETE computers.*, Q_SearchExpiredScanDates.computer_name
FROM computers INNER JOIN Q_SearchExpiredScanDates ON computers.computer_name = Q_SearchExpiredScanDates.computer_name;

 

[scriverb]

Try this SQL for your delete query:

DELETE C.* 
FROM computers as C 
WHERE C.computer_name IN (Select Q.computer_name from Q_SearchExpiredScanDates as Q);

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TB0NE]

Thanks scriverb! That worked perfectly.

Can you tell me what was wrong with my query? What is your query doing that mine was not?

Thanks again!

TB0NE

 

[scriverb]

Your query had multiple tables to deal with with the joins. ACCESS can't determine which table to delete in that situation. By using a subquery in the WHERE statement only the primary row of records is only related to one table. Thus ACCESS can interpret this correctly.

I have used another method to delete specific records from a table while using all of the joins. I select a field that can be updated with a special value so as to flag the records to delete. This field cannot have an index that does not allow duplicates. I run an initial update query to flag the records with let's say "9999". Make sure the value is not a legitimate value for the field. Then just run a delete query against the table and delete all reads with "9999" in that field. Two step operation but it works and is easy to setup.

Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TB0NE]

Thanks again scriverb.

It's guys like you that make my job a whole lot easier.

You get a star.

 

[scriverb]

Thanks. Glad to be of help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cariengon]

Yea! Fixed my problem too!

Carie