473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Making a user logfile in acess 2007

mose Mbugua
Am trying to make an access report which will capture how a user after entering their username and password use system forms in a database. the report should show all the activities that took place after the user logged in. It should also show the time.
How do i go about that in access 2007?
Thanks
Sep 18 '14 #1

✓ answered by twinnyfo

mose Mbugua,

The short of it is that you will have to add code to every event procedure fired by every form, and every AfterUpdate event on every control on every Form. This is possible, but the type of thing you are asking for is incredibly extensive, as you are essentially asking the DB to log every mouse click and every data change.

You would also want a Table that could capture all those activities, so you could report it. There is no automatic activity log in MS Access.

8 1111
twinnyfo
3,653 Expert Mod 2GB
mose Mbugua,

The short of it is that you will have to add code to every event procedure fired by every form, and every AfterUpdate event on every control on every Form. This is possible, but the type of thing you are asking for is incredibly extensive, as you are essentially asking the DB to log every mouse click and every data change.

You would also want a Table that could capture all those activities, so you could report it. There is no automatic activity log in MS Access.
Sep 18 '14 #2
twinnyfo
what if we reduce that so that the report can only capture only forms and reports which were opened by a user? Do i have to use code or is there another way?
Sep 18 '14 #3
twinnyfo
3,653 Expert Mod 2GB
You must still use code. One way might be to use the OnOpen Event of each form, saving the Form Name, User Name and Time Accessed to your table.

The simplest way to do that is create a Public Function in a separate Module that saves that information to a Table--then you just call that Function any time the Form is opened.
Sep 18 '14 #4
NeoPa
32,556 Expert Mod 16PB
Mose:
What if we reduce that so that the report can only capture only forms and reports which were opened by a user?
The original question was no different from this. Hence the answer is the same. Anything that is to be logged must be logged by your project by design. There is no automatic logging in Access.
Sep 18 '14 #5
twinnyfo
3,653 Expert Mod 2GB
Mose,

Here is my five-minute solution--there may be other/better ways to do this, but this will work...

Create a Table named tblLogActivity with the following fields:

Expand|Select|Wrap|Line Numbers
  1. Field         Data Type
  2. =======================
  3. LogKey        Long, AutoNumber
  4. FormName      Text
  5. UserName      Text
  6. TimeAccessed  Date/Time, Default Value = Now()
In the module behind each of your forms:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.     LogActivity Me.Form.Name, Environ("USERNAME")
  7.     Exit Sub
  8. EH:
  9.     MsgBox "There was an error opening the Form!  " & _
  10.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  11.     Exit Sub
  12. End Sub
And then, in a separate Module, include the following function:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function LogActivity(FormName As String, UserName As String)
  5. On Error GoTo EH
  6.     Dim db As Database
  7.     Dim strSQL As String
  8.     Dim rst As Recordset
  9.     Set db = CurrentDb()
  10.     strSQL = "SELECT * FROM tblLogActivity;"
  11.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  12.     If Not rst.RecordCount = 0 Then
  13.         With rst
  14.             .AddNew
  15.             !FormName = FormName
  16.             !UserName = UserName
  17.             .Update
  18.         End With
  19.     End If
  20.     rst.Close
  21.     db.Close
  22.     Set rst = Nothing
  23.     Set db = Nothing
  24.     Exit Function
  25. EH:
  26.     MsgBox "There was an error logging the activity!  " & _
  27.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  28.     Exit Function
  29. End Function
Not the fanciest code every created, but should serve as an introduction to the process. Of course, the process could be expanded to include other values also.
Sep 18 '14 #6
Am new to code guys. Guide me to doing that.
Sep 18 '14 #7
twinnyfo
3,653 Expert Mod 2GB
Do your forms only use macros? If so, I would encourage you to learn more about VBA and convert your macros to VBA. It is a much more powerful means of making your DB useful and flexible.
Sep 18 '14 #8
jforbes
1,107 Expert 1GB
This is some Code I recently started using for logging Errors and other Events of interest. Hopefully it's not to long to post or overkill for this Question.

Also, there are some Methods in there that are not included, like GetWindowUser() and dbLocal(), if anyone wants them, I can wrangle them together.

As for Logging, the logEvent() function creates a record in SQL Server. Like Twinnyfo's example, to log an event on Form Open (Load):
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Call logEvent("FormOpen",0, "", "FormName", 0, "")
  3. End Sub
The customErrorHandler() does pretty much the same, but also Logs it to a text file as well as displays the message to the user. So then, I put the following in error handlers throughout the Database:
Expand|Select|Wrap|Line Numbers
  1. Call customErrorHandler(mProcedureName, True, Err, Erl, "")
Here is the Module:
Expand|Select|Wrap|Line Numbers
  1. Private Const mProcedureName = "ErrorHandler"
  2. Public gErrorMessage As String
  3.  
  4. Public Function customErrorHandler(ByRef sActiveObject As String, ByRef bShowError As Boolean, ByRef oErr As ErrObject, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
  5.  
  6.     Dim sError As String
  7.     Dim sSQL As String
  8.     Dim iErrorFileHandle As Integer
  9.     Dim sLogFile As String
  10.     Dim iErrorNumber As Integer
  11.     Dim sErrorDescription As String
  12.     Dim sAppDir As String
  13.  
  14.     ' On Error GoTo Statments reset the error object
  15.     iErrorNumber = oErr.Number
  16.     sErrorDescription = oErr.Description
  17.  
  18. On Error GoTo GotoError
  19.  
  20.     DoCmd.Hourglass False
  21.     DoCmd.SetWarnings True
  22.  
  23.     sAppDir = getAppDir()
  24.     iErrorFileHandle = FreeFile
  25.     sLogFile = sAppDir & gShortAppName & ".log"
  26.  
  27.     ' Build Error Message
  28.     sError = sError & "" & Now() & vbCrLf
  29.     sError = sError & " Code Library/Line: " & sActiveObject & "/" & iErrorLine & vbCrLf
  30.     sError = sError & " User/Computer: " & GetWindowsUser() & "/" & GetWindowsComputerName() & vbCrLf
  31.     sError = sError & "     Error Message: " & sErrorMessage & vbCrLf
  32.     sError = sError & "     Error Number: " & iErrorNumber & vbCrLf
  33.     sError = sError & "     Error Description: " & sErrorDescription & vbCrLf
  34.  
  35.     ' Write Error message to file
  36.     If Len(gShortAppName) > 0 And Len(sAppDir) > 0 Then
  37.         Open sLogFile For Append Access Write As #iErrorFileHandle
  38.         Print #iErrorFileHandle, "~" & sError
  39.         Close #iErrorFileHandle
  40.     End If
  41.  
  42.     ' Display Error
  43.     If bShowError Then msgBoxError ("An Error has occured in " & gShortAppName & ":" & vbCrLf & sError)
  44.  
  45.     ' Insert Error into SQL
  46.     customErrorHandler = logEvent("RuntimeError", iErrorNumber, sErrorDescription, sActiveObject, iErrorLine, sErrorMessage)
  47.  
  48. GotoExit:
  49.     Exit Function
  50.  
  51. GotoError:
  52.     MsgBox "Error in ErrorHandler.customErrorHandler: " & Err.Description
  53.     Resume GotoExit
  54.  
  55. End Function
  56.  
  57. Public Function logEvent(ByRef sEventType As String, ByRef iErrorNumber As Integer, ByRef sErrorDescription As String, ByRef sActiveObject As String, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
  58.  
  59.     Dim sSQL As String
  60.  
  61.     logEvent = False
  62.  
  63.     ' Insert Error into SQL
  64.     sSQL = ""
  65.     sSQL = sSQL & " INSERT INTO EventLog ("
  66.     sSQL = sSQL & "  EventType "
  67.     sSQL = sSQL & ", ErrorNumber "
  68.     sSQL = sSQL & ", Description "
  69.     sSQL = sSQL & ", Object "
  70.     sSQL = sSQL & ", Line "
  71.     sSQL = sSQL & ", Message "
  72.     sSQL = sSQL & ", UserID "
  73.     sSQL = sSQL & ", Computer "
  74.     sSQL = sSQL & ", Application "
  75.     sSQL = sSQL & ", ApplicationVersion "
  76.     sSQL = sSQL & ") VALUES ( "
  77.     sSQL = sSQL & "  '" & sEventType & "'"
  78.     sSQL = sSQL & ",  " & iErrorNumber & ""
  79.     sSQL = sSQL & ", '" & trimForSQL(sErrorDescription) & "'"
  80.     sSQL = sSQL & ", '" & sActiveObject & "'"
  81.     sSQL = sSQL & ", '" & iErrorLine & "'"
  82.     sSQL = sSQL & ", '" & trimForSQL(sErrorMessage) & "'"
  83.     sSQL = sSQL & ", '" & GetWindowsUser() & "'"
  84.     sSQL = sSQL & ", '" & GetWindowsComputerName() & "'"
  85.     sSQL = sSQL & ", '" & gShortAppName & "'"
  86.     sSQL = sSQL & ", '" & mVersion & "'"
  87.     sSQL = sSQL & ")"
  88.  
  89.     dbLocal.Execute sSQL
  90.  
  91.     logEvent = True
  92. GotoExit:
  93.     Exit Function
  94.  
  95. GotoError:
  96.     MsgBox "Error in ErrorHandler.logEvent: " & Err.Description
  97.     Resume GotoExit
  98.  
  99. End Function
Sep 18 '14 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: FJ | last post by:
Hi all, I hope someone can help me with the following: I want my database to make a logfile in which it puts the date and time a code is used. The code is triggered by a timer. Every x mins it...
5
by: Tarscher | last post by:
Hi all, I have a user control and a form. I want the user control to be visible when the form is shown. I haven't found a good webtutorial on this so maybee someone can help me out? regards...
4
by: Stephen Adam | last post by:
Hi there, I am working on a project were a user can update a SQL Server DB via a web form. I've included a number of text boxes for a user to enter in strings. The text from these boxes is then...
10
by: wish | last post by:
Dear all, in my application, after user login with their password then some user just can view certain page and some page is confidential. What is the command that i need to add in?
1
by: Milan Mehta | last post by:
Dear All, I am new to Access and trying my hand on Access 2007. When I try to create a from using Form Wizard and then selects all fields, all text box remain of same width. If I alter width of...
4
by: SpaceMarine | last post by:
hello, i am having a problem creating a .ACCDE file from an Access 2007 .ACCDB file. its somewhat large application, about 80 megs. it was originally an Access 2000 app that somebody else...
2
by: GypsyEyes1480 | last post by:
I am creating a database for my boss and I am a new user to Access, so pretty much I am bumbling through slowly. I created two tables with similar info for two sep semesters. I created a query for...
1
by: rawilken | last post by:
Does anyone know of a Calendar developed for Access that mimics the functions of the traditional Outlook calendar?
2
by: jmborron | last post by:
I have a report in Access 2007 that is populated by a parameter query. My boss wants this report ran for all 250 optoins once a month, saved as a pdf and emailed to the contacts located in a table...
2
by: afizam | last post by:
Dear All, I am trying to make a simple user form in Excel 2007 to be used in my workshop. This is part of the code I've made : If Me.txtFirstName.Value = "" Then MsgBox "Please enter a First...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.