Tracker Software 2

[LadyDev]

Has anyone ever heard of software (COTS, or otherwise) that tracks the movement of individuals logged into a database. I have a database extracts files from directories. Users have to log in to use it. I have to be able to track what files they used or copied to other directories.

 

[BobStubbs]

Can I ask ... when you say '... tracks the movement of individuals ...', do you want to record the actions which users carry out within a database? If so, there are techniques for doing this. For example, you can write a log record every time a user chooses a Switchboard menu option, clicks a button on a form, etc.

Is this what you need?


Bob Stubbs

 

[LadyDev]

Absolutely BobStubbs that's what I want.

 

[LadyDev]

I already have a table sstructure (4 tables): I need to extract all this information from the tables and with a push of a button create a report that I can email to my manager daily. Any help you lend with be most appreciated. Thanks in advance

tbl_filename
filename_id
dir_filename
backup_folder_name

tbl_filename_history
filename_history_id
timestamp
completed
message

tbl_security
security_id
password
active
access_id
view_id

tbl_userlog
security_id
time_in
time_out

 

[LadyDev]

The problem I am having is the data does not display when I run the query. I need a "Where" clause, but I can quite figure out what where clause to give it. The information is already in the table I am just trying to display it in a report. This is the SQL that was generated from my query:

SELECT tblSecurity.UserID, ztblUserLog.TimeIn, ztblUserLog.TimeOut, tblFileName.in_dir_and_file_name, tblFileName.backup_to_folder_name, tblFileNameHistory.timestamp, tblFileNameHistory.message

FROM (tblSecurity INNER JOIN ztblUserLog ON tblSecurity.SecurityID = ztblUserLog.SecurityID) INNER JOIN (tblFileName INNER JOIN tblFileNameHistory ON tblFileName.file_name_id = tblFileNameHistory.file_name_id) ON ztblUserLog.LogID = tblFileNameHistory.SecurityID;

 

[BobStubbs]

Here is my example code to write a log entry whenever a user clicks a switchboard button. You can use the same idea for any other button ...

Create a table e.g. tblActivityLog with these fields:

ID          autonumber   
UserID      text
Action      text
ObjectName  text
LogDate     date/time    short date format
LogTime     date/time    long time format

Create this function in a module:

Function WriteLogRecord(stAction As String, stObject As String, As String)

'----------------------------------------------------
'- Write a record into the Activity Log table       -
'----------------------------------------------------

    Dim stLogDate As String
    Dim stLogTime As String
    Dim stSQl As String

    stLogDate = Format$(Now, "dd/mm/yyyy")
    stLogTime = Format$(Now, "hh:mm:ss")

    DoCmd.SetWarnings (False)
    
    stSQl = "INSERT INTO tblActivityLog (UserID, Action, ObjectName, LogDate, LogTime)"
    stSQl = stSQl & "VALUES ('" & CurrentUser & "', '" & stAction & "', '" & stObject & "', '" & stLogDate & "', '" & stLogTime & "')"
    DoCmd.RunSQL (stSQl)

    DoCmd.SetWarnings (True)

    WriteLogRecord = "OK"
    
End Function

Place this code wherever you want to log an activity.

stResult = WriteLogRecord(String1, String2)

Example from my switchboard form - code added to the 'exit button' option:

stResult = WriteLogRecord("Exit", "Switchboard Button")

Example code added to any form, which will log the form being accessed:

Private Sub Form_Load()
    Dim stResult As String
    
    stResult = WriteLogRecord("Open Form", Me.Name)
End Sub

I hope that this helps.

Regarding your second question, about a daily report, are you trying to write a 'yesterday report', i.e. a list of all log in / out activity etc. for the previous day?

Bob Stubbs

 

[LadyDev]

Thanks BobStubbs, but I got it working. I had my joins, joined incorrectly - go figure. I finally got something that Access liked and like magic it's working. I am sure that your post will benefit some else in the future.

Right, I need to have the reports previous days activities printed and shipped via email. Something that I can configure on startup. Is this possible?

 

[pbbriggs]

Thanks BobStubbs for providing an easy way to create an activity log!
Star for you.

 

[MichaelRed]

see also (alternatively) faq181-291. although it does not log the activities like switchboard (button clicks), it does offer a nore comprehensive view of the activity on the data itseld.




MichaelRed
mlred@verizon.net