Audit Trail (1 Viewer)

[Kevin_S]

GroundRush,

Pass-through queries are relatively simple to do (especially ones to pull the current user) however, the back end needs to be a larger RDBMS to use these queries as they are SQL specific. Plus, I have never dealt with a Novell Server before so I am a little weary of leading you down the wrong path...

What is the backend of your db? is it in Access or some other program? If it is in access then pass-through query will not work for you... if the BE has been upsized then this type of query is what you need - post back if your BE has been upsized and you want help building a pass-through query...

HTH,
Kev

 

[Lyn Mac]

KevinS. . .

It's good to know that you were able to sort out the problem.


My working code is actually a combination of those suggested by Ghudson and CollinEssex.


Regards,

Lyn Mac

 

[Groundrush]

Thanks Kevin_S

I have not upsised the database at the moment so I guess I won't be attempting any pass- through qry's like you mentioned at the moment.

although I will have to read up on upsising as I am sure it could lead to further complications.

I found an interesting thread yesterday that had a good example about securing a database that Hayley Baxter kindley shared

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47750&highlight=security

Users have a user name and password to logon.

I am hoping that the audit trail might pick up user names from there.

Thanks for your kind offer of assisting me

 

[Kevin_S]

GroundRush,

If you are exploring the possibility of upsizing then once you have done this all you will need to do (when your backend tables are upsized and on the server) is open up the query design grid and, from the query drop down menu at the top of the screen, choose the option SQL specific and then -- pass-through query. That will open up the query in SQL mode. Once here its as simple as this script:

SELECT user as CurrenUser

This will get you the current user -- then you bring this into the code and your all set. I also have a few pass-through queries that grab the users server role so I can do functions based on this as well -- if you are interested post back and I can provide examples of these too.

If you are looking in the short term to implement personal security to mantain the current username and password I have a system set up (thanks to the help of Forniatian) that provides a solution to this and all it requires is importing the objects and setting the login form as the opening form to the db! It takes 3 seconds to get set up and has a really nice user interface for managing all of the db users. Once this is setup all you have to do is use a simple DLookUp statement to get the current user. If you would like to try out this security post back and let me know. Its not as secure as MS Access security, but its far easier to setup and maintain. If your not too concerned with being absolutely secure (which I am not sure that you are as you are not even currently using any means of security) and you would like to try this out let me know and I will send you a copy...

HTH,
Kev

 

[Groundrush]

Kevin_S

Thanks that would be very useful, I would like to see your example....anything to improve my knowledge.

by the way did you see the one that Halyey baxter posted?


http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47750&highlight=security

cheers

 

[Kevin_S]

Groundrush,

I saw Hayley's example and the one I have differs from that verion in a few areas so if you are still interested in seeing my version post your e-mail so I can send it to you (unfortunitly it is too big to post here). Also let me know what version of Access you need it to be in. If your not interested anymore and are going with Hayley's example or a different direction that s fine too just let me know

Kevin

 

[Groundrush]

Kevin_S

my email address is:

Deleted


I am using Access 2000

Thanks

 

[Kevin_S]

I emailed you the example....

HTH,
Kevin

 

[hi there]

hi folks,

i was looking for some ideas for an audit trail and came across this great code ghudson developed. i have one question associated with this code. can i use this for multiple forms (unrelated) using the same AuditTrail field? for example i have 3 tables that i want to use this code for. can i include an "AuditTrail" field in each of these tables and an associated "tbAuditTrail" control on their corresponding forms and call the code to the BeforeUpdate event of each form. so basically i would create one module named "dAuditTrail" and call it three different times. is this ok? will things get messed up having 3 fields with the same name in 3 different tables. any help or guidance would be grately appreciated.

thanks

 

[ghudson]

hi there,

That is exactly what you want to do. I do the same thing for I want to track the changes to each table and each table has a AuditTrail field and each form has a tbAuditTrail text box. Using the "public" function Audit_Trail() in each forms BeforeUpdate event is the way to go for that eliminates any redundancy in code.

Good Luck!

 

[hi there]

thanks ghudson.

 

[Maxine1010]

Audit Trail revisited

Dear All,

I must thank all of the people who posted on this thread, I have found it invaluable and I couldn't thank Access World Forums enough for providing this think tank.

I managed to set up an audit trail on my database using the code given which worked perfectly until now and I can't figure out why.
After some help from various Access World Forum members I put the code on my main form and sub form, I tested it by inserting numerous dummy records and everything was ok. However, now that I've realised the database to my team to trial it doesn't work. I can complete the personal detail fields but then I reach the Comments box and the code errors to the before_update event where I 'Call' the audit trail.

I have only a basic understanding of VB so I really haven't got a clue where it's all going wrong. I don't know if its clashing with the On_Current event on my form which is (to lock the form for changes):

Private Sub Form_Current()
If Me.NewRecord Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If
End Sub


Or if its the sub form Audit trail code.

Any help or advice would be greatly appreciated!!!

Thanks

 

[hooi]

Thank you ghudson for the AuditTrail demo. It works great!

 

[mission2java_78]

Hi something similiar to this was posted on a microsoft site.

However,

There is still many applications out there that need a more robust yet at the same time rollback auditing capability in an application.

For instance,

Many clients have asked me to create them trials where changes can be reverted to or revised on. This particular audit trial just gives me what was changed, what happens when the end user modifies 30 records and realizes he wants to go back to the original contents of 25 of them? The current method just stores this information in a text / memo field. The ideal situation would include much more programming and temp-tables, tables, and maybe some append queries.

Jon

 

[hooi]

I noticed that the AuditTrail demo works well on a single form. However, in a form-subform situation, it will not capture changes made to the subform, even though the subform's table has its own tbAuditTrail field.

eg. if I edit a field in the child table, the tbAuditTrail in the parent table shows:

Changes made on 22/10/2003 10:25:19 AM by <username>;

It doesn't show which field's content has been changed.

Can the program be changed to also capture changes made to the subform?

 

[Kevin_S]

hooi - this example will work on a form/subform setup you just have to adapt it a little with a few minor changes. The fix for this is actually posted by Lyn Mac on the first page of this post...

HTH,
Kev

 

[hooi]

Great! Thank you Kevin.

 

[hooi]

I've discovered that in a parent-child form setup, when the Before Update event is set to call AuditTrail subroutine, it causes 'error 3251 - Operation is not supported for this type of object' to be displayed when I try to move to an adjacent record after having edited a record. Has anyone come across this problem? How should it be fixed?

Thanks...

 

[hooi]

Ok, correction on my earlier discovery. The error code 3251 appears because of the lookup fields linked from another table. My solution is to rename the controls of all the lookup fields. eg. Lookup1, Lookup2,... Then slight modification is made to the AuditTrail function as shown below to solve this problem:

If ctl.Name = "AuditTrail" Or ctl.Name Like "Lookup*" Then GoTo TryNextControl 'Skip AuditTrail or Lookup field.

 

[Johnkl]

can you please be more specific because I have the same problem and i really can't understand what to change...


Kind regards