Inserting into 2 tables

[Pretty1996]

Hi Guys, </SPAN>

I wonder if this is possible.</SPAN>

Basically what I want to be able to do is have 2 tables which are exactly the same. So that Table2 mirrors Table1.
</SPAN>
Table1 is the main entry table and any record I add to Table1 gets added to Table2 as well.
</SPAN>
But any record I delete from Table1 still remains in Table2.
</SPAN>
How would I go about this? Any advice or guidance is appreciated.
</SPAN>
Hope I’m making sense. Thanks in advance.</SPAN>

By the way I am using Access 2010.</SPAN>

 

[Joe4]

Re: Inerting into 2 tables

Take a look at "Delete Queries" under "Action Queries".

I have to question though, why do you have two tables where one is supposed to be the exact replica of the other? Seems unnecessary and redundant.

 

[Pretty1996]

Re: Inerting into 2 tables

Hi Joe,

Thanks for your reply, I appreciate you looking into it.

To be honest I'm not entirely sure about my own idea either at the moment, but it's not really a duplicate table I'm trying to create but more like a tracker table.

I am/was thinking of using it as an audit table really.

I've had a look on the net and have found a few examples of audit trails in Access but can't find one which suits my needs really.

I want the audit table to have the same fields as my main table but maybe have a couple of fields that record whether the record was added/edited/deleted and the date.

But I dont think my original idea I mentioned in my original post will work really.

~ Sukh

 

[Joe4]

Re: Inerting into 2 tables

I have never really done anything like that myself. I don't know if that is how I would try to approach it. I think I might approach it in the following manner:
- Make sure my Main Table has a unique, primary key field.
- Add a few fields to the table that show the status (Added/Edited) and last change date. For deleted records, you might want to move them to a separate table of deleted records.
If you cannot add the Status and Change Date to that same table, you could create a new table that has your unique identifier, Status, and Change Date, which links to your main table.

Obviously, I think you want most of your editing to be Form driven where you can use VBA to make the necessary updates. If you will be importing data, you might need to import it into a Temporary table first, and then have processes move the necessary records and make the necessary updates.

I don't know if that is the best way of going about it, but that is probably how would I approach if I couldn't find anything better via a Google search.

 

[SydneyGeek]

Re: Inerting into 2 tables

Chucking in 2 cents...

The tblAudit table needs to be an exact copy of the fields in tblMain, with the following extra fields:
tblAudit has its own Autonumber primary key, a CreateDate field with default =Now(), and and CreateBy field that inserts the Windows user name of the person making the change.

When you edit a record, use the form's BeforeUpdate event to push the data to the audit table -- use the OldValue property of each control to capture the values pre-editing.
When you create a record just leave it as is, in the main table. You can timestamp the records in this table too.
When you delete a record, use the BeforeDelete property to push all the field values to Audit. Don't use the OldValue property this time.

To get a full history you can see records that were never edited or deleted by querying records in the main table whose IDs are not in the audit table, UNION ALL the records in Audit.

Denis