Archive and Delete Queries

[Canadiangal]

I have created a query that goes out and finds all employees with the Archived Status of Yes. This query appends these employees with all of their data from all tables to a table called Archived.
I then created a query that will find all employees with the Archived Status of Yes and deletes these employees with all their related data. However, when I run the delete query I get the following message "Cannot delete from specific tables". Here is my SQL

DELETE ArchiveDeleteQuery.Attendance.CourseNumber, ArchiveDeleteQuery.Attendance.EmployeeID, ArchiveDeleteQuery.Attended, ArchiveDeleteQuery.DateAttended, ArchiveDeleteQuery.Reimburse.EmployeeID, ArchiveDeleteQuery.Eligible, ArchiveDeleteQuery.PayAmnt1, ArchiveDeleteQuery.PayDate1, ArchiveDeleteQuery.MeetingsClasses.CourseNumber, ArchiveDeleteQuery.MeetingsClasses.Title, ArchiveDeleteQuery.Comment, ArchiveDeleteQuery.Date, ArchiveDeleteQuery.Location, ArchiveDeleteQuery.Instructor, ArchiveDeleteQuery.NoteText, ArchiveDeleteQuery.HR.EmployeeID, ArchiveDeleteQuery.[Original Hire Date], ArchiveDeleteQuery.[Termination Date], ArchiveDeleteQuery.DisasterCall, ArchiveDeleteQuery.NoFlex, ArchiveDeleteQuery.[Appraisal Date], ArchiveDeleteQuery.[PA Sent Out], ArchiveDeleteQuery.[PA Received], ArchiveDeleteQuery.[License Number], ArchiveDeleteQuery.[Elligible for Reimbursement], ArchiveDeleteQuery.[Reimbursement Payment], ArchiveDeleteQuery.[Reimbursement Date], ArchiveDeleteQuery.SocialSecurityNumber, ArchiveDeleteQuery.FirstName, ArchiveDeleteQuery.LastName, ArchiveDeleteQuery.Employees.EmployeeID, ArchiveDeleteQuery.Address, ArchiveDeleteQuery.City, ArchiveDeleteQuery.State, ArchiveDeleteQuery.PostalCode, ArchiveDeleteQuery.HomePhone, ArchiveDeleteQuery.CellPhone, ArchiveDeleteQuery.Birthdate, ArchiveDeleteQuery.[Archive Status], ArchiveDeleteQuery.Badge.EmployeeID, ArchiveDeleteQuery.[Employee Number], ArchiveDeleteQuery.TagTitle, ArchiveDeleteQuery.TagCredentials, ArchiveDeleteQuery.TagNoLastName, ArchiveDeleteQuery.ProxChip, ArchiveDeleteQuery.[Badge Style], ArchiveDeleteQuery.DepartmentData.EmployeeID, ArchiveDeleteQuery.[Department ID], ArchiveDeleteQuery.DepartmentData.Title, ArchiveDeleteQuery.PatientTreatmentProgramme.Emplo yeeID, ArchiveDeleteQuery.ProcedureID, ArchiveDeleteQuery.DateAdministered, ArchiveDeleteQuery.Declined, ArchiveDeleteQuery.Result, ArchiveDeleteQuery.DateDue
FROM ArchiveDeleteQuery
WHERE (((ArchiveDeleteQuery.[Archive Status])=Yes));




Thank you in advanced for any help you can give.

[orange]

Why are you deleting via a query and not a table?

When you use a DELETE query, you are deleting Rows from a Table.
If you have referential integrity set and Cascade Deletes, when you delete a record from your table, any records in related tables with that same key will be deleted (automagically).

It is possible that some of the tables and joins in your query are causing the underlying record set to be Non-updateable.

Suppose you have a table, MyTable with a number of fields. One of those fields is ArchivedStatus (I really detest spaces and special characters in field names). Now suppose the table contains hundreds of records and suppose 123 of the records have ArchivedStatus = Yes (boolean)

Then, to delete those records your query becomes

DELETE FROM MyTable
WHERE ArchivedStatus = Yes

You should be aware that DeLETE and UPDATE queries are quite unforgiving. You may wish to back up your tables in advance, run any query and verify the proper action has occurred -- while you still have a backup to save you.

Good luck

see http://www.access-programmers.co.uk/...d.php?t=245147 for a recent post similar

[Canadiangal]

That is why I have the append query running first to append the records to another table. I am using a query for the delete because I am linking 8 tables together. No, I don't have cascading deletions set for each link, because there are times that I wouldn't want to delete out the linking data for historical purposes. Does that make sense?

[rpeare]

why does your archiving process delete records at all? If you are already marking records as 'archived' you can build reports/forms/queries that ignore those records altogether. Archiving in this manner is not likely to give you a huge performance increase so I'm a bit unsure why you'd want to remove records from one table only to append them to another, particularly when it makes querying old data harder than it needs to be.