Determin which Database the user in using

[memaxt]

Hi All,

I have several databases operating in my office, I am able to deterime which users are currently in my databases but cant determine which database they are in. how would it do this?

Current Code:
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean
texists = False
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN ) VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New DAO.Field: Set f2 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set t = Nothing: Set db = Nothing

Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)


Else
fOSUserName = vbNullString
End If
End Function
Private Sub Form_Close()
CurrentDb.Execute "DELETE * FROM users_logged_in WHERE USER = '" & fOSUserName & "'"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub

[ranman256]

You could give them a button to: msgbox currentdb.Name

[rpeare]

You should be able to open any .ldb or .laccdb file using filesystemobject commands and read who is currently in your database from that file, it would just involve cycling through all pertinent folders to pull the information.

Before you get started make sure you can open the .ldb or .laccdb files and see if you are getting enough information. to go any further. For instance on my machine it lists my PC name (which you may be able to track to a specific user) and my security level, not my user name.

[fredz]

You could add a login & logout procedure to everyone of your databases.

The login procedure would use fosUserName() & CurrentDB.Name to append a record to a log table.
Locate the procedure in the OnLoad event of your opening form, within each database.

The logout procedure would use fosUserName() & CurrentDB.Name to delete the matching record from your log table.
Locate the procedure in the OnClose event of your opening form, within each database.

Then, whenever you need to see whose in which databases, just query the log table.

Alternate #1
You could modify the log table with fields for login date/time and logout date/time.
Then, instead of deleting records during the logout procedure you would just update the logout date/time.
Now you could review the table and also determine who, when, and how often your users are using the databases.

Fred