What is cq2 ? Also, what is the primary key in [contractor qualified] ? Can this primary key be modified ? What is the primary table and which is the backup table ?

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.

Access is the only DB that will be used

Hope this helps

Regards

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 ?

Access 97
No the tables will reside in different database.
VB v5 is accessing the database. I am not sure what you mean by control over the software and triggering the select statements.

The routines will run after exiting certain forms or once a day as part of the initialiasing routines.

Regards

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 ?

Thanks for the quick reply,

Yes I write the code.

The routines will run after exiting certain forms or once a day as part of the initialiasing routines and all are being run in the VB front end

As for trigger, I am assuming that you mean what is the scenario that causes the code to enter these routines if it is then it is as I said above

Hope this makes it clear for you

Regards

I am thinking of 3 solutions.

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
database activity.

Let me know if you additional direction/discussion.

Good luck.

Which route did you take ? How is the solution working out ? Your other post requested help with external databases/tables - do you still need help ?

Many thanks for the follow up,

As i understand replication it is applied to the whoule db not tables so I have decided against that

I like the third option of transaction logs. I am heading that way using a combination of my original solution with some modifications and transaction logs.

The funny thing is the database has not "lost" a single record over the last 3 weeks since it has been monitored. This leads me to 2 conclusions,

1. Some one has access to my password and is maliciously deleting records direct from the database

2 The system when working over the network is causing mass inconsistencies and is dropping records haphazardly.

I think the real problem is no 2. I have been trying to open the database with readonly access when on the network but it does not sem to work. I am still able to modify recordsets.

The code I am using is as follows
Set MyDb = OpenDatabase(StrDb, False, True, ";pwd=" & pPassword)

I am able to open tables in an external db but I am still learning the syntax of a few joins.

Again, many thanks for your help and replies. If you can suggest anything re the use over the database over a network it would be appreciated.

Thanks Again