How can I keep a running Log of users who use my Access database? 2

[lachlanmullen]

I have searched all over the internet this morning, and have not found how to keep a log of uders who access me Access database.

Any help is GREATLY appreciated.

I am trying to prove that said employees use a tool i developed.

 

[alvechurchdata]

Simple method is to put some code in a Form.Load which writes the time and date to a text file. If you've got a login form then put the code there and capture the login id.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lachlanmullen]

I do not have a logon form, is there any way to capture that data on logon, and write ti to a text file?

 

[alvechurchdata]

is there any way to capture that data on logon,

There must be an API call to get the user's Windows login and the machine's network ID. I don't know enough here to be able to help though. I hope someone else will give you the details.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[sostek]

We use Access as a front end for many MySQL databases. In the past week I've finally secured the MySQL databases by database, table and column so it's critical we authenticate users.

We use the MySQL ODBC connector. What I did was have the user enter a username and password on a form, then pass that to the connect string. I suppose you could do something similar and record the id and date/time in a table or a text file.

Here's the code I used:

Public Sub getTbls()

10    On Error GoTo getTblsError
      Dim username1 As String
      Dim password1 As String

20    username1 = [Form_login_form].username
30    password1 = [Form_login_form].password
40    DoCmd.Hourglass True


50    Set dbse = CurrentDb.QueryDefs("show_entity_catalog_tables")
60    Set dbsf = CurrentDb.QueryDefs("show_owners_manual_tables")
70    Set rst = dbse.OpenRecordset
80    Set rst1 = dbsf.OpenRecordset


90    With rst
100       Do While Not rst.EOF
110       tblName = .Fields("Tables_in_entity_catalog")
120       DoCmd.TransferDatabase acLink, "ODBC Database", _
              "ODBC;DSN=entity_catalog;UID=" & username1 & ";PWD=" & password1 & _
              ";Language=us_english;" & "DATABASE=entity_catalog", acTable, tblName, _
              tblName, , False
          
130       rst.MoveNext
          
140       Loop
          
150   DoCmd.Hourglass False
          
160   End With

170   With rst1
180       Do While Not rst1.EOF
190       tblName1 = .Fields("Tables_in_owners_manual")
200       DoCmd.TransferDatabase acLink, "ODBC Database", _
              "ODBC;DSN=owners_manual;UID=" & username1 & ";PWD=" & password1 & _
              ";Language=us_english;" & "DATABASE=owners_manual", acTable, tblName1, _
              tblName1, , False
          
210       rst1.MoveNext
          
220       Loop
230   End With

240   rst.Close
250   rst1.Close
260   Set dbse = Nothing
270   Set dbsf = Nothing

280   [Form_login_form].Visible = False
290   Exit Sub

getTblsError:
      'MsgBox ("An error occured. " & Err.Number & " - " & Err.description)
300   DoCmd.Quit

End Sub

Basically I get the username and password and incorporate it in the connection string you see.

Line numbering is turned on for reference btw. In any cases, lines 20 to 30 snatch the values off a form while lines 120 and 200 pass those variables to the connect string.

So anywhere after line 30 you could insert your code to write the name to a table or file.

Hmm.. this brings up something interesting for me. Now I can see if someone is trying to hack someone elses password.

 

[MaxEd]

Use this.

Environ("UserName")

Output this to a file or a table.
I suggest using a table, it's easier to manage because if the administrators of the server screws with the rights and directory structure, your database is hosed.



-Laughter works miracles.

 

[lachlanmullen]

Thanks guys, sostek, your solution is a little more than I asked for, but I can't fault you for that! Thanks for the help.

MaxEd, that works perfectly, thank you so much for your timely and valuable help!

Lach