Audit trail of remote Access 2007 database table? 3

[BxWill]

Is it possible to create a audit trail of a remote MS Access 2007 database table?

Currently, have a single user MS Access database on the network drive that data is entered directly into the table.
Also, there is no primary key used in the table.

(Realize that this is inefficient but cannot change...)

Therefore, I would like to create a audit trail to capture the following;

1. Date that a new record is entered
2. Date that a record is modified
3. The field that is modified
4. Data before the modification
5. Data after the modification

Is this possible or even feasible?

An alternative thought is to copy the contents of the table to another MS Access database table that resides on my local drive. However, I would need to copy on a daily basis.

Appreciate any insight that will enable me to capture the modifications to a remote MS Access database.

 

[MajP]

No, not in 2007. There are no events at the table level. However, 2010 introduces data macros which can capture changes at the table.
Can you explain the reason for this design? This sounds silly to me. On one hand you need a robust data integrity/tracking, but your design is the worst possible solution to ensure this ? Why would a single user db need to be on the server? Why no forms? Why no PK? Why can it not be changed? This is worse than inefficient, it is begging for data corruption and data loss.

 

[BxWill]

I have no control over the design of the database but I need to obtain data and determine changes.

Currently exploring the following option (will move to MS Office forum);

1. Create two MS Excel worksheets - CurrentData, PreviousData
2. Using MS Query, query MS Access table on day 1 and store results in the worksheet titled "CurrentData"
3. On day 2, move data from the worksheet titled "CurrentData" to the Worksheet titled "PreviousData"
4. On day 2, Using MS Query, query MS Access table and store results in the worksheet titled "CurrentData"
5. Run MS Excel vba to obtain the differences between the worksheets.

 

[MichaelRed]

your inquiry implies multiple users access the data?

Is it a 'single user' user db? really?
Then why the necessity of collection?

Is it possible that it is the BE (data only) where multiple users access if through local FE's? Perhaps even using forms?

If the latter, all you want / need may be possible.

MichaelRed

 

[MichaelRed]

Failing the above, it should be possible to do some of this in another Ms. A db (of your own).

b]THE FIRST VERY STRONG REQUEST WOULD BE TO CREATE AT LEAST AN AUTONUMBER FIELD IN THE TABLE[/b]

Then:

Create a "clone" of the existing db. This is a one time one shot effort.

On a schedule of your choosing, add a query to your db, which grabs the existing db. Preferably as a 'disconnedted' recordset.

W/o knowing the info/structure of the existing db, the rest is somewhat the worst case, but does depend on the existance of the autonumber ...

Using the autonumber field, comparisions are possible.

It may be possible to change any or ALL the fields or any combination of the fields (except the autonumber) which might be viewed as the deletion of one record and the creation of a new one.

Using the autonumber field, compare each record field by field for differences and record them in a new recordset recording the info required.

Of course, you cannot guarntee the accuracy of the data fields, or even that you capture all changes. Since, within any reasonable interval, a record could -concievably - have one or more fields changed from its 'original' value to a 'new' value, and then later changed back to the original valus.

Using this , of course you can also track any deletions.

MichaelRed

 

[assets]

in 2003 you could the field showing the changes is a memo field in the table

Never give up never give in.

There are no short cuts to anything worth doing

 

[BxWill]

Trying to implement a version of MichaelRed's solution.

Did go down the route of trying to implement in MS Excel but ran into the problem of extensive time needed to not only determine the differences between the remote MS Access table and my static copy of the table contents a few days ago that is on the MS Excel worksheet.

Basically, I need to have a table within my database mirror the table within the remote database, have several lookup columns based on the remote data and a few calculated fields and then store this into a table (minus the calculated fields) within my database.

At any given moment in time, I should be able to access the form within my database and be able to see all of the contents of the table within the remote database along with the calculated fields and the other fields that I have entered.

Is this possible?

Will continue down the path trying to implement a version of MichaelRed's solution without the autonumber field. Don't really think I need the autonumber field ...

Any further insight is appreciated.

 

[PHV]

Don't really think I need the autonumber field ...
How would you recognize the records without a Primary Key ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BxWill]

Thanks for the insight,

I can definitely see the value with the autonumber field.

As initially stated, I have very little control over the design of the remote database.

After several inquiries, it appears that there are two primary users of the database - one person (person A) who enters data and doesn't want any additional fields added and the other person (person A's supervisor) who periodically deletes records from the table.

Originally, the objective was to create a audit trail to
capture;

1. Date that a new record is entered
2. Date that a record is modified
3. The field that is modified
4. Data before the modification
5. Data after the modification

Will shift the focus and therefore, the modified objective is to calculate the estimated charges for all customers within the remote MS Access database table and then to create a monthly report ( on the 15th of each month) that displays all customers that have estimated charges greater than $10,000.

In order to fulfill the modified objective, it appears that I have to clone the table within the remote MS Access database.

Due to my lack of control over the deletion of items within the remote MS Access database, I assume that I have to "capture" the contents of the table within the remote MS Access database on a periodic basis and store within a table within my personal MS Access database.

Note, I did try to implement a system within MS Excel. However, the Excel-based system will not allow me keep up to date records for all of the customers in a easy fashion.

Will continue to research the cloning of a table within a remote MS Access database and post back with additional information...

Let me know if this requires a re-posting

 

[PHV]

calculate the estimated charges for all customers
What are the rules ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886