×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Audit trail of remote Access 2007 database table?
3

Audit trail of remote Access 2007 database table?

Audit trail of remote Access 2007 database table?

(OP)
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.


 

RE: Audit trail of remote Access 2007 database table?

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.

RE: Audit trail of remote Access 2007 database table?

(OP)
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.

RE: Audit trail of remote Access 2007 database table?

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


 

RE: Audit trail of remote Access 2007 database table?

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


 

RE: Audit trail of remote Access 2007 database table?

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   smile

RE: Audit trail of remote Access 2007 database table?

(OP)
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.

RE: Audit trail of remote Access 2007 database table?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close