Archiving Records between Linked Tables

[BanditLV]

I have a front-end .MDE that is linked to several back-end databases (to minimize size and possibility of loss of data)... Everything revolves around the ClientID in Client...

In general, I would have tables such as this:
Client
Client Expenses
Client Creditors
Client Notes

The archive tables are located in a different database but named with an "A" in front of them (I know, not ingenious, but it still works):
AClient
AClient Expenses
AClient Creditors
AClient Notes

When the trigger is met, the specific records are Appended to the Archive tables but the problem exists when I want to delete the records from the original tables (I don't want them there anymore)... The error I get is looks like a MessageBox I could've programmed that says "Could Not Delete From Specified Tables" - the help menu is about as vague as stereo instructions... However, I eliminated the permission possibility and I believe I eliminated the Read-Only possibility... Believe it or not, this once worked, now... well, HELP!!!

Thanks in advance,

Roy aka BanditLV
Las Vegas, NV

 

[lameid]

If you post your delete query someone it will be easier for someone to help you. The error doesn't sound like an Access error... Try commenting out the error handling from your code and getting to the real access error message.

 

[BanditLV]

By request, this is one of the queries (SQL-based) that deletes the archived records... The appends work fine, of course...

DELETE Client.Status, Client.*
FROM Client
WHERE (((Client.Status)=83));

Roy aka BanditLV
Las Vegas, NV

 

[K9Boxer]

Do you have Referential Integrity in your table relationships? If so, and you do not have 'cascade delete' marked, the RI restricts you from deleting a 'master' records with dependent rows in other tables.

Just a thought....

 

[jacksonmacd]

Had a similar situation, and dennisebotts' solution about referential integrity was correct. If you don't have cascading deletes enabled, then you can start the delete process from the "leaf" tables, and work backwards to the "root" table.