p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   user log details... (http://p2p.wrox.com/showthread.php?t=50802)

anukagni November 27th, 2006 07:12 AM

user log details...
HI all,

  In my database i have three user and i want to know the user log details. Like .. by what time they logged in and what are the things they did like editing the data or adding data soo on ..

 Is this possible guy's ..I heared about this but iam not having vast knowledge...please help me ..

Learn as you can..

leehambly November 27th, 2006 07:22 AM


Anything is possible... but unless I missed a meeting, you will have to build this yourself.

I would suggest building a User Table (for your user details), User Trail Table (to store the user actions you want to record) and also a set of forms and a module to administrate the whole thing.

For the User Trail: you are best off using a function to add new trail events. Use the function whenever you come across an event you want to add to the trail. There are no triggers in Access, so you have to build them yourself, essentially.

Frankly, user security and the like are doable in so many different ways in Access, I would suggest a book on the subject.

NB: I DON'T suggest using the Access security features in any way, shape or form.

mmcdonal November 27th, 2006 09:12 AM


   Create a function in a module. Put this code in:

Public Function sU()
sU = (Environ$("Username"))
End Function

   This will pull the username of the person opening the app as they are logged into their workstation.

   On your main form On Load event, put code that takes the current date/time (Now()) and the username from the module Function (sU()) and pass those to a table that has fields named: Username, Login, Logout.

   On your main form On Close event, put code that takes the sU(), then looks up in the table you made for record(s) where Username = sU, and Logout = "". Then push the current date/time to the logout field.

   This will keep a record of the login and logout times by user. You may want to set the table to hidden.

   Let me know if you want to add the following fields to your other tables to track this info:

DateCreated - per record

   These will only keep the name and date/time of the person who created a record, and the last name and date/time of the person who modified the record.



Brendan Bartley November 28th, 2006 06:18 AM

hi Mmcdonal
I have been following this thread and if could advise me how to do the following it would be great

DateCreated - per record

Brendan Bartley

mmcdonal November 28th, 2006 08:21 AM

Sure. In each table that you want to track these issues, create these four fields:

DateCreated - date/time - default = Now()
WhoCreated - text
DateModified - date/time - no default
WhoModified - text

Put these fields on your form, but make them invisible. At least change them to disabled.

Then on your forms, on the Before Insert insert event, add this code:

Dim sUser As String
sUser = (Environ$("Username"))
Me.WhoCreated = sUser

On the same form's Before Update event, add this code:

Dim sUser As String
sUser = (Environ$("Username"))
Me.WhoModified = sUser
Me.DateModified = Now()

This will track the date and time and who created each record, and the date and time of who LAST modified each record, if they use the form to create and modifiy records.

The only way to track the total modification history is to create a transaction table, and start logging old and new values in that table with this data as well.



Brendan Bartley November 28th, 2006 12:10 PM

Thanks for your reply just one question does the table have to be stand alone.To setup an audit trail is this the case


Brendan Bartley

mmcdonal November 28th, 2006 12:17 PM

I am not sure what you are asking. The table used to log when users open and close the app should be stand alone. The fields to record who creates and modifies records are part of existing tables where you want to track this, and a transaction table should be stand alone.


Brendan Bartley November 28th, 2006 12:31 PM

Thanks again for your reply I am not sure what the tranaction table is

Brendan Bartley

mmcdonal November 28th, 2006 12:37 PM

You have to decide what goes into the transaction table.

Like, if you have a table with these fields:

NameID - PK
FName - text
LName - text

And you want to see who does what in the table, create a form that is unbound, but allows users to create or modify records in this table. Then when a record is modified, let's say, you record this in the tblNameTrans table:

TransID - PK
NameID - copy
FName_Old - old value
FName_New - new value
LName_Old - old value
LName_New - new value
UserName - the user that made the modification
DateCreated - date/time default Now()

For grins, you may want to store the transaction table in a totally different database, and just send the data there on the before insert and before update events of a bound form.

Lots of ways to do this.


Brendan Bartley November 28th, 2006 01:21 PM

Exactly how would you transfer the information to another database

Brendan Bartley

mmcdonal November 28th, 2006 01:51 PM

That depends. What kind of database, and how are you accessing it?

Basically, open a connection on the SQL database, then open a recordset on the transaction table, declare enough variables to hold all the information you want to pass, then get the info, mostly like this:

Dim sOldFName, sNewFName As String

sOldFName = Me.FName.OldValue
sNewFName = Me.FName.Value


Then pass the values to the recordset using AddNew and Update.

Exact code depends on ADO v. DAO, and how you are referencing the database, either ODBC or Provider.



All times are GMT -4. The time now is 10:36 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.