Storage of deleted data in another database?

[winkmich]

Hi there,

I have the following problem:

I have a database and from the “Main Table” I have to delete some records from time to time. The records of the Main Table are linked to other tables.

I would like to have another Database like “Archive” where all deleted records are stored in a table then, including all the linked data (tables).
So the structure of the database must be the same.

It there an easy way to do that?

Thanks in advance,

Michael.

 

[wh00t]

yes

create a query

then right click and select Append Query from Query Type, you will then be prompted for a table to append the data to.

so, set up a Archive database, and create tables in it for the tables that you wish to archive and set the tables up exactly the same. Then when your setting up your append query select external database and choose the archive database.

that should do it

EDIT - you'll need to create a query for each table you archive, you can then create a button on a form to run all the queries with SetWarnings = False if you don't want the warning messages to popup

 

[winkmich]

Thanks - I think I got the point.

I included a Command Button on the form from where I want to delete the record.

This runs three queries - one after the other.

stDocName = "Append to Archive Contacts"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "Append to Archive Donations"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "Append to Archive Cov"
DoCmd.OpenQuery stDocName, acNormal, acEdit

The thing is that I have to specify this query to that SPECIFIC RECORD. Means: I would like to copy (move) the record of the Contacts table including its links to the Donations and Cov table.
(so far it copies all the contacts into the new database).

After that copying I would like to delete that record from the original Table.

Usually you can't delete a record which is connected to other tables. Is there any way to do so???

Thanks in advance.

Michael.

 

[wh00t]

have you tried a delete query?