If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tracking records that have been deleted
I am creating a scheduling project and I wanted to know if there was a way to track the records that have been deleted from the database, either from rescheduling appointments, canceling appointments, to 'no-shows'. I would like to be able to call this the 'Archive', and be able to have it empty out every five years. I heard this was called an 'Audit Trail Form'?
If this is possible, please someone help me!! Thanks, Robin |
#2
|
|||
|
|||
Simply add a checkbox to the record that says "deleted". NEWVER delete the
records, just mark them as deleted. Then exclude those from all your standard queries, reports, and forms. You might even add the current user, current date,a nd current time to a "timestamp" field wen this box is checked. In genral, you don't "archive" items, you simply leave them where they are and exclude them from your other objects based on date or status. If you run into performance issues, or have data you will NEVER need, then you might think about 'archiving' it to a separate file, or deleting it. -- Rick B "r_shawn" wrote in message ... I am creating a scheduling project and I wanted to know if there was a way to track the records that have been deleted from the database, either from rescheduling appointments, canceling appointments, to 'no-shows'. I would like to be able to call this the 'Archive', and be able to have it empty out every five years. I heard this was called an 'Audit Trail Form'? If this is possible, please someone help me!! Thanks, Robin -- r_shawn |
#3
|
|||
|
|||
Tip: If you want true Audit Trail do not allow the system to physically
delete anything. Add a field a Yes/No (boolean) type will do to indicate whether the record is deleted or not. You may also consider it a good idea to record the date "deleted" along with (or instead of) the marker. Disable all areas where the record can be physically deleted and add code to set the above marker to true. When running all the forms and objects that use records that are not deleted ensure you query the new field to check that it is set to false. The archive screens can use it when the marker is set to true. When using this approach it is a good idea to use a Select Query as a wrapper for the table(s). Say the table is Appointment you could create a Query called QAppointmentDeleted and one called QAppointment. You then use these queries instead of the base table as the basis for all subsequent queries used in your forms and reports. This approach allows you to query live and deleted data either together or seperately and even recover deleted records on the archive screens by resetting the marker. -- Slainte Craig Alexander Morrison "r_shawn" wrote in message ... I am creating a scheduling project and I wanted to know if there was a way to track the records that have been deleted from the database, either from rescheduling appointments, canceling appointments, to 'no-shows'. I would like to be able to call this the 'Archive', and be able to have it empty out every five years. I heard this was called an 'Audit Trail Form'? If this is possible, please someone help me!! Thanks, Robin -- r_shawn |
#4
|
|||
|
|||
You could realy get fancy by adding a history table with the following fields
-- RecordKey - This and the date is the unique key for this table. DeleteDate User - from login if used. Remarks - text or memo field. "Craig Alexander Morrison" wrote: Tip: If you want true Audit Trail do not allow the system to physically delete anything. Add a field a Yes/No (boolean) type will do to indicate whether the record is deleted or not. You may also consider it a good idea to record the date "deleted" along with (or instead of) the marker. Disable all areas where the record can be physically deleted and add code to set the above marker to true. When running all the forms and objects that use records that are not deleted ensure you query the new field to check that it is set to false. The archive screens can use it when the marker is set to true. When using this approach it is a good idea to use a Select Query as a wrapper for the table(s). Say the table is Appointment you could create a Query called QAppointmentDeleted and one called QAppointment. You then use these queries instead of the base table as the basis for all subsequent queries used in your forms and reports. This approach allows you to query live and deleted data either together or seperately and even recover deleted records on the archive screens by resetting the marker. -- Slainte Craig Alexander Morrison "r_shawn" wrote in message ... I am creating a scheduling project and I wanted to know if there was a way to track the records that have been deleted from the database, either from rescheduling appointments, canceling appointments, to 'no-shows'. I would like to be able to call this the 'Archive', and be able to have it empty out every five years. I heard this was called an 'Audit Trail Form'? If this is possible, please someone help me!! Thanks, Robin -- r_shawn |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
recover deleted items | G P Zob (Capt) | General Discussion | 0 | June 22nd, 2005 03:36 PM |
restore deleted records from a query | ranachohan | General Discussion | 1 | May 23rd, 2005 02:35 PM |
restore deleted access records | Hancho | Running & Setting Up Queries | 2 | May 23rd, 2005 12:49 AM |
How do I get Access to release space for deleted records at run ti | Newbee Adam | New Users | 1 | March 9th, 2005 06:51 PM |
New records can't be seen | rleblanc | Using Forms | 6 | August 14th, 2004 02:43 PM |