Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2018, 02:57 PM   #1
Phredd
The Wizard
 
Phredd's Avatar
 
Join Date: May 2002
Location: Australia
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Phredd
Identify User

Hi Guys,

I am trying to determine which user currently has a database open in my company to allow me to ask them to close it for maintenance.

I am a little (Lot) out of my depth with this level of coding, but have stolen this code from another site on recommendation.

While I have no errors, I have no idea where/wehat/how to get the resulting user information. The macro appears to run great (No errors) but nothing obvious tells me the user either..

Thx
------------------------------------------------

Option Compare Database
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

Phredd is offline   Reply With Quote
Old 08-15-2018, 03:37 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,850
Thanks: 9
Thanked 3,827 Times in 3,770 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Identify User

Where do you want to use it? Basically you call the function from anyplace (query, VBA, form, etc). In VBA:

VariableName = fOSUserName()

in a form textbox:

=fOSUserName()
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-21-2018, 06:25 AM   #3
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 126
Thanks: 4
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Identify User

To identify the users in database, open the .laccdb file in Word.

RogerCooper is offline   Reply With Quote
Old 08-21-2018, 02:32 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,850
Thanks: 9
Thanked 3,827 Times in 3,770 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Identify User

Quote:
Originally Posted by RogerCooper View Post
To identify the users in database, open the .laccdb file in Word.
How does that help in this situation?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-21-2018, 03:19 PM   #5
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,190
Thanks: 83
Thanked 1,530 Times in 1,426 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Identify User

Whilst the fOSUserName function works, a much simpler solution is just to use:
Code:
Environ("UserName")
OR if you want an external solution, you could use this utility originally by David Crake & updated by me last year: https://www.access-programmers.co.uk...d.php?t=295174

I have a similar system in my own multi-user apps which also includes the ability to:
a) send out a warning to all current users by email
b) start a shut down procedure giving users a specified time to logout e.g. 5 minutes
c) prevent new users logging in during that time
d) email all users after routine maintenance has been completed
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 08-21-2018, 05:22 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,285 Times in 1,224 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Here's code that lists logged in users using the .laccdb file. You can adapt it to your purpose.
Code:
Private Sub PrintConnectedComputers()
    Const FILE_NAME As String = "FileToTest.laccdb"
    Const SRC_PATH As String = "C:\SourcePath\"
    Const DST_PATH As String = "C:\DestinationPath\"
    
    Dim ts As Scripting.TextStream
    Dim tmp As String
    Dim usr As String
    Dim dic As New Scripting.Dictionary
    
    ' copy the existing .laccdb file, and open as stream
    With New Scripting.FileSystemObject
        ' copy (overwrite) the .laccdb file
        .CopyFile SRC_PATH & FILE_NAME, DST_PATH & FILE_NAME, True
        ' open copied file as a Scripting.TextStream
        Set ts = .OpenTextFile(DST_PATH & FILE_NAME, ForReading)
    End With
    
    With ts
        Do While Not .AtEndOfStream     ' loop thru characters in file
            tmp = .Read(1)              ' read each char
            Select Case Asc(tmp)        ' check each char
                Case 0, 32              ' ignore spaces
                Case Else               ' select everything else
                    usr = usr & tmp     ' concat per user
                    If Right(usr, 5) = "Admin" Then     ' test for trailing username
                        usr = Replace(usr, "Admin", "") ' drop trailing username
                        If Not dic.Exists(usr) Then     ' test for user already exists
                            dic.Add usr, usr            ' if not, add to dictionary
                        End If
                        usr = ""        ' prepare for next connection
                    End If
            End Select
        Loop
        .Close
    End With
    
    Dim var
    For Each var In dic.Keys    ' loop thru dictionary keys and print users
        Debug.Print var
    Next
    
    Debug.Print "*** End Run"
End Sub
Note that one user might have multiple connections to a BE file, and once they log out, it does not seem to immediately remove all their connections. Also, this code only works if you do not have a login for the database, because it assumes all database usernames are 'Admin,' which is the default. Also, this code returns the name of the machine that is connected, not the logged in user's Windows username.
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-21-2018, 07:49 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,233
Thanks: 73
Thanked 1,401 Times in 1,322 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Identify User

Quote:
Originally Posted by ridders View Post
Whilst the fOSUserName function works, a much simpler solution is just to use:
Code:
Environ("UserName")
Of course that doesn't return the actual username but the Username Environment Variable which, although defaulted to the username, can be reset to anything.

This expression returns the username.

Code:
CreateObject("wscript.network").UserName


Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Identify windows user bharath744 General 6 12-31-2010 09:45 AM
Identify who is using DB CEH Modules & VBA 13 03-16-2007 10:51 AM
Identify Records by the User which created them. Randelissimo Reports 10 08-09-2004 05:25 AM
Identify User IanT Modules & VBA 1 03-03-2004 04:52 AM
Identify a user Groundrush General 4 07-07-2003 02:16 AM




All times are GMT -8. The time now is 03:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World