Well I got a solution but it does not look real pretty. It is also convoluted using 4 temporary tables. Can you see a more efficient way? Here is the code and many thanks for steering me in the correct direction
'Get all records Prior to them being changed
("SELECT CQ2.* INTO Temp " _
& "From CQ2 WHERE NOT Exists " _
& "(SELECT * FROM [Contractor Qualified] AS CQ WHERE " _
& "((CQ2.Area = CQ.Area) AND " _
& "(CQ2.[Trade Type] = CQ.[Trade Type]) AND " _
& "(CQ2.[Rate Above Par] = CQ.[Rate Above Par]) AND " _
& "(CQ2.[Contractor Id] = CQ.[Contractor Id])))")
'Create a table with all the records that have been added or changed
("SELECT [Contractor Qualified].* INTO Temp2 " _
& "From [Contractor Qualified] WHERE NOT Exists " _
& "(SELECT * FROM CQ2 WHERE " _
& "((CQ2.Area = [Contractor Qualified].Area) AND " _
& "(CQ2.[Trade Type] = [Contractor Qualified].[Trade Type]) AND " _
& "(CQ2.[Rate Above Par] = [Contractor Qualified].[Rate Above Par]) AND " _
& "(CQ2.[Contractor Id] = [Contractor Qualified].[Contractor Id])))")
'Compare 3 fields in temp against temp2 to find matching records that have been changed
'And add them to temp FROM Temp 3
'That leaves changed records before and after in Temp and new records in Temp2
'Add the required fields. Name, Date etc to Temp
MyDb.Execute "ALTER TABLE [Temp] ADD COLUMN [When Changed] DATETIME;"
MyDb.Execute "ALTER TABLE [Temp] ADD COLUMN [By Whom] TEXT(25);"
'Copy records FROM Temp to Change Transaction Table
MyDb.Execute ("INSERT INTO [CQ Changes] SELECT * FROM Temp;")
'Copy records FROM Temp2 to backup table
MyDb.Execute ("INSERT INTO [CQ Backup] SELECT * FROM Temp2;")
'Clean up Temporary Tables
MyDb.Execute ("DROP TABLE CQ2")
MyDb.Execute ("DROP TABLE Temp")
MyDb.Execute ("DROP TABLE Temp2")
MyDb.Execute ("DROP TABLE Temp3")
Cq2 is a duplicate table of contractor qualified that is created prior to a user going into the form which allows them to add remove or change records.
The primary key is the 3 fields, [contractor id],[trade type] and [area].
I suppose it can be modified but it was designed to stop multiple rates being added for the same contractor ,area and trade.
The primary table is Contractor Qualified, the Backup is CQ Backup.
Incidentley I will be modifiying it (as soon as i find out how) to store the backup table in another access97 database.
CQ Changes is the transaction table that only records a changed record, when it was changed and by whom.
I started to give a solution but realized I needed additional information. In your original post, you were concerned about deleletions. Now, it seems that maybe you want to track all transactions. So for clarification, please provide what your goal is - be as descriptive as possible. Especially when it comes to the table that will hold the deletions/additions that you have discovered. What do you want to do with this information. Are you concerned with all transactions or just mainly the deletions ? And confirm that Access is the only database you will be using for this entire process.
Sorry, yes there were two different goals. One was to track unauthorised deletions from the Items Used Table, the other was to track transactions in some other tables. The contractor qualified is to track changes being made to a contractors rate. The problem is that the users claim they are not changing the rates (which afect payments) so i need to be able to track the changes to provide management with either the proof that users are changing the figures or that the program is behaving erratically.
The second problem with missing records is to be able to rebuild the transactions should the records be deleted inadvertenlyor on purpose. What I intend doing is to track the removal of a record by users but also in a separate table in a separate DB keep track of all the records that were ever associated with a particular job. This will enable me to rebuild the necessary transactions if a malicious user is getting into the db and mass deleting records.
A couple of questions: Which version(s) of access are you using ? Do all the tables reside in the same database ? What program are you using to access the database (access, vb, ...) ? Do you have control over the software ? Is it a 3rd party software package ? How are you triggering the "select into" statements ?
Are you the one that writes the vb software/code ? How do you know when to run your "select into" statements - what are you using to trigger these statements ? Where are the "select/into" statements being run ?
1. The solution you are proposing with a few modifications. I am
concerned that the activity will cause performance problems as well
as a higher potential for data corruption.
2. Another solution is using replication in access.
3. The last solution would mean modifying your software to create a
transaction log table for each table you want to track. This last
option will be more efficient. Basically, you can create a table
that is a copy with additional fields including a guid (like a time/date
stamp) and a type of transaction (delete, update, insert). When
perform an update/delete/insert just propagate this transaction to
log table.The benefit of this over your initial solution is you have less
Let me know if you additional direction/discussion.