MS Access Multiuser - How to Identifier Users

Author

How to Identifier Users

jhrBanker

2006-01-09, 1:24 pm

I have a shared db on the network. How can I determine if anyone is in the
db, and their Network ID. When I need to perform maintenance on the db, I
want to make sure that noone is in it. And if someone is it, I need to
identify who they are so that I can contact them.
Thanks

seth

2006-01-09, 8:27 pm

connect to the computer using computer management and look at the open files
will show the open file and who has it open

"jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
news:2D5B113D-C839-4A58-A2DB- 12FE45A02741@microso
ft.com...
>I have a shared db on the network. How can I determine if anyone is in the
> db, and their Network ID. When I need to perform maintenance on the db, I
> want to make sure that noone is in it. And if someone is it, I need to
> identify who they are so that I can contact them.
> Thanks

Douglas J. Steele

2006-01-09, 8:27 pm

If anyone's in the database, there will be an LDB file in the same folder.

Assuming you're using Access 2000 or newer, check
http://support.microsoft.com/?id=285822 for how to read the information from
the LDB file. Note, though, that unless you've applied Access User-Level
Security (so that your users need to log into the application), all you'll
be able to get back is the machine name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
news:2D5B113D-C839-4A58-A2DB- 12FE45A02741@microso
ft.com...
>I have a shared db on the network. How can I determine if anyone is in the
> db, and their Network ID. When I need to perform maintenance on the db, I
> want to make sure that noone is in it. And if someone is it, I need to
> identify who they are so that I can contact them.
> Thanks

Jeff O via AccessMonster.com

2006-01-10, 8:25 pm

I don't know how much of an application you have, but I had a similar need to
know who was logged on to the DB. What I did was to add some simple code to
capture the name of the person logged on to the DB and insert it into a
common table. I (the adminstrator) can now see who's logged on , and how
long they have been logged on. If you want the specifics just let me know.

/Jeff

jhrBanker wrote:
>I have a shared db on the network. How can I determine if anyone is in the
>db, and their Network ID. When I need to perform maintenance on the db, I
>want to make sure that noone is in it. And if someone is it, I need to
>identify who they are so that I can contact them.
>Thanks

--
Message posted via http://www.webservertalk.com

jhrBanker

2006-01-10, 8:25 pm

Thanks Doug, I think this will work for me. I followed the instructions and
ran the code in the Immediate window and the results look good. However, I'm
having a bit of a problem getting it to work in the real environment, due to
may lack of experience. I've built a form with a command button and 4 text
fields (txtComputer, txtLogon, txtConnect & txtTermed). I want to be able to
click the button and have the code populate these fields.

I'd appreciate any help you can provide.
Thanks
====================
====================
============
"Douglas J. Steele" wrote:

> If anyone's in the database, there will be an LDB file in the same folder.
>
> Assuming you're using Access 2000 or newer, check
> http://support.microsoft.com/?id=285822 for how to read the information from
> the LDB file. Note, though, that unless you've applied Access User-Level
> Security (so that your users need to log into the application), all you'll
> be able to get back is the machine name.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
> news:2D5B113D-C839-4A58-A2DB- 12FE45A02741@microso
ft.com...
>
>
>

jhrBanker

2006-01-11, 8:25 pm

Jeff: Thanks for the offer. I also have a log file, but I don't actually
write the record to the log file until they exit the database. I'd like to
take a look at your code.

====================
=========
"Jeff O via webservertalk.com" wrote:

> I don't know how much of an application you have, but I had a similar need to
> know who was logged on to the DB. What I did was to add some simple code to
> capture the name of the person logged on to the DB and insert it into a
> common table. I (the adminstrator) can now see who's logged on , and how
> long they have been logged on. If you want the specifics just let me know.
>
> /Jeff
>
>
> jhrBanker wrote:
>
> --
> Message posted via http://www.webservertalk.com
>

Douglas J. Steele

2006-01-11, 8:25 pm

I think you may have to modify that routine to write the data to a table,
and bind your form to that table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
news:F5FF192A-2E4C-4C21-A584- 3D2EC29FCD9B@microso
ft.com...[color=darkred]
> Thanks Doug, I think this will work for me. I followed the instructions
> and
> ran the code in the Immediate window and the results look good. However,
> I'm
> having a bit of a problem getting it to work in the real environment, due
> to
> may lack of experience. I've built a form with a command button and 4
> text
> fields (txtComputer, txtLogon, txtConnect & txtTermed). I want to be able
> to
> click the button and have the code populate these fields.
>
> I'd appreciate any help you can provide.
> Thanks
> ====================
====================
============
> "Douglas J. Steele" wrote:
>

jhrBanker

2006-01-12, 9:24 am

I understand that I have to modify the code. My question is, what
modifications are required? The table is bound to the form.
====================
====================
============

"Douglas J. Steele" wrote:

> I think you may have to modify that routine to write the data to a table,
> and bind your form to that table.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
> news:F5FF192A-2E4C-4C21-A584- 3D2EC29FCD9B@microso
ft.com...
>
>
>

Jeff O via AccessMonster.com

2006-01-12, 8:25 pm

Ok, here it is. It's VERY simple however...nothing elaborate.
I am mearly recording who logs on and when they log off into a table. I have
a report that I can run on the fly to show me who's logged on.

in the startup form open event add the following code:
'Record the logon of this user in the current_logins table
Call logon_record.logon_user

in the form close event add the following code:
'Record the logoff of this user in the current_logins table
Call logon_record.logout_user

Note: Be sure you set the appropriate permissions on the current_logins table

to allow users to read+write to it.

Now create two objects.

1) create the module "logon_record" (see code below)
2) create table "current_logins"
definition is:

Two fields: field Name: Data Type
current_user text (field size 50)
login_time Date/Time

logon_record is a seperate module...here it is.

'Begin logon_record ********************
*

Option Compare Database
Dim db As Database
Dim strSQL As String
Dim strFunctionName As String

Function logon_user()
strFunctionName = "logon_user"

Set db = currentdb
Dim f1 As String
Dim f2 As Date
f1 = currentuser
f2 = Now

'Add this user to the logon table along with time
strSQL = "INSERT INTO current_logins ( current_user, login_time ) VALUES (" &
_
"'" & f1 & "'" & "," & "'" & f2 & "'" & ")"
db.Execute strSQL
End Function

Function logout_user()
strFunctionName = "logout_user"
Set db = currentdb
strSQL = "delete from current_logins " & _
"WHERE current_user = " & "'" & currentuser & "'"
db.Execute strSQL
End Function

'End logon_record ********************
**

jhrBanker wrote:[color=darkred
]
>Jeff: Thanks for the offer. I also have a log file, but I don't actually
>write the record to the log file until they exit the database. I'd like to
>take a look at your code.
>
> ====================
=========
>
>[quoted text clipped - 9 lines]

--
Message posted via http://www.webservertalk.com

Douglas J. Steele

2006-01-12, 8:25 pm

Assuming you've got a table with field names COMPUTER_NAME, LOGIN_NAME,
CONNECTED and SUSPECT_STATE, something like the following untested air-code
should work:

Sub ShowUserRosterMultip
leUsers()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdCurr As ADODB.Command
Dim strSQL As String

Set cn = CurrentProject.Connection

Set rs = cn. OpenSchema(adSchemaP
roviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

Set cmdCurr = New ADODB.Command
Set cmdCurr.ActiveConnection = cn

Do While Not rs.EOF
strSQL = "INSERT INTO MyTable " & _
"(COMPUTER_NAME, LOGIN_NAME, CONNECTED, SUSPECT_STATE) " & _
"VALUES('" & rs.Fields(0) & "', '" & rs.Fields(1) & "', " & _
rs.Fields(2) & ", " & IIf(IsNull(rs.Fields(3)), "Null", "'" &
rsFields(3) & "'") & ")"
cmdCurr.CommandText = strSQL
cmdCurr.Execute
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set cmdCurr = Nothing
Set cn = Nothing
End Sub

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"jhrBanker" < jhrBanker@discussion
s.microsoft.com> wrote in message
news:A48A54CD-8F62-413E-8081- 8F33D2FA3D98@microso
ft.com...[color=darkred]
>I understand that I have to modify the code. My question is, what
> modifications are required? The table is bound to the form.
> ====================
====================
============
>
> "Douglas J. Steele" wrote:
>

RFGunManRos

2006-01-23, 1:24 pm

Jeff,

This is great i borrowed your script with some minor adjustments to table
names etc. I have little deeper thought. I need to retrieve computer ID's not
user names how can I get this.

Even better do you have a script to retrieve the Computer_Name and User_ID
that is logged into that computer. I'm not using access security in my
database so really your script is returning "Admin, or USER".

"Jeff O via webservertalk.com" wrote:

> Ok, here it is. It's VERY simple however...nothing elaborate.
> I am mearly recording who logs on and when they log off into a table. I have
> a report that I can run on the fly to show me who's logged on.
>
> in the startup form open event add the following code:
> 'Record the logon of this user in the current_logins table
> Call logon_record.logon_user
>
> in the form close event add the following code:
> 'Record the logoff of this user in the current_logins table
> Call logon_record.logout_user
>
> Note: Be sure you set the appropriate permissions on the current_logins table
>
> to allow users to read+write to it.
>
> Now create two objects.
>
> 1) create the module "logon_record" (see code below)
> 2) create table "current_logins"
> definition is:
>
> Two fields: field Name: Data Type
> current_user text (field size 50)
> login_time Date/Time
>
>
>
> logon_record is a seperate module...here it is.
>
> 'Begin logon_record ********************
*
>
> Option Compare Database
> Dim db As Database
> Dim strSQL As String
> Dim strFunctionName As String
>
> Function logon_user()
> strFunctionName = "logon_user"
>
> Set db = currentdb
> Dim f1 As String
> Dim f2 As Date
> f1 = currentuser
> f2 = Now
>
> 'Add this user to the logon table along with time
> strSQL = "INSERT INTO current_logins ( current_user, login_time ) VALUES (" &
> _
> "'" & f1 & "'" & "," & "'" & f2 & "'" & ")"
> db.Execute strSQL
> End Function
>
>
> Function logout_user()
> strFunctionName = "logout_user"
> Set db = currentdb
> strSQL = "delete from current_logins " & _
> "WHERE current_user = " & "'" & currentuser & "'"
> db.Execute strSQL
> End Function
>
> 'End logon_record ********************
**
>
>
>
> jhrBanker wrote:
>
> --
> Message posted via http://www.webservertalk.com
>

Douglas J. Steele

2006-01-23, 8:25 pm

http://www.mvps.org/access/api/api0008.htm at "The Access Web" shows how to
retrieve network name, http://www.mvps.org/access/api/api0009.htm shows how
to retrieve computer name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"RFGunManRos" < RFGunManRos@discussi
ons.microsoft.com> wrote in message
news:41269DB6-57BC-47F6-B4DD- 7F859C9058C7@microso
ft.com...[color=darkred]
> Jeff,
>
> This is great i borrowed your script with some minor adjustments to table
> names etc. I have little deeper thought. I need to retrieve computer ID's
> not
> user names how can I get this.
>
> Even better do you have a script to retrieve the Computer_Name and User_ID
> that is logged into that computer. I'm not using access security in my
> database so really your script is returning "Admin, or USER".
>
> "Jeff O via webservertalk.com" wrote:
>

RFGunManRos

2006-01-24, 9:24 am

Douglas,

I have tried the first link you gave me and made the module. But for some
reason myabe my own lack of knowledge on this issue but I dont get anything
in return even though I'm logged in. I actually get an error "Compile Error.
Expected Variable or procedure not a module" My name is correct for module
and I cut and paste "fOSUserName" in the immediate window. I would like to
populate this in a table if at all possible like this script does if you know
the changes that need to be made (I would greatly appreciatte the help!"

The problem with the below script is that does this but actually returns the
rights (Admin or User) versus the user name. Its returning how you actually
logged into Access versus the computer name or network login. This is a
Module below by the way.

Option Compare Database

'Begin logon_record ********************
*

'Option Compare Database
'Dim db As Database
Dim strSQL As String
Dim strFunctionName As String

Function logon_user()
strFunctionName = "logon_user"

Set db = CurrentDb
Dim f1 As String
Dim f2 As Date
f1 = CurrentUser
f2 = Now

'Add this user to the logon table along with time
strSQL = "INSERT INTO current_logins ( current_user, login_time ) VALUES ("
& "'" & f1 & "'" & "," & "'" & f2 & "'" & ")"
db.Execute strSQL
End Function

Function logout_user()
strFunctionName = "logout_user"
Set db = CurrentDb
strSQL = "delete from current_logins " & _
"WHERE current_user = " & "'" & CurrentUser & "'"
db.Execute strSQL
End Function

'End logon_record ********************
**

Thank You in Advance
Kurt
RFGunMan

"Douglas J. Steele" wrote:

> http://www.mvps.org/access/api/api0008.htm at "The Access Web" shows how to
> retrieve network name, http://www.mvps.org/access/api/api0009.htm shows how
> to retrieve computer name.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "RFGunManRos" < RFGunManRos@discussi
ons.microsoft.com> wrote in message
> news:41269DB6-57BC-47F6-B4DD- 7F859C9058C7@microso
ft.com...
[color=darkred]
>
>
>

Douglas J. Steele

2006-01-24, 8:25 pm

Assuming that login_time is a date/time field, and not text, you must
delimit f2 with # characters, not quotes. As well, the date must be in
mm/dd/yyyy format, regardless of what your Short Date format has been set to
through Regional Settings. (Okay, this last part isn't strictly true. You
can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point
is, you can't reliably use dd/mm/yyyy)

In addition, try using the dbFailOnError argument with your Execute
statement, so that you can trap errors.

strSQL = "INSERT INTO current_logins " & _
"( current_user, login_time ) VALUES (" & _
"'" & f1 & "'" & "," & Format(f2, "\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")"
db.Execute strSQL, dbFailOnError

Of course, you have to introduce error-trapping for this to work:

At the beginning of the module, put

On Error GoTo Err_Trap.

At the end, put:

Exit_Here:
Exit Sub ' or Exit Function if it's a function

Err_Trap:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"RFGunManRos" < RFGunManRos@discussi
ons.microsoft.com> wrote in message
news:962D664D-A0EA-4ED9-8297- D6729608D95A@microso
ft.com...[color=darkred]
> Douglas,
>
> I have tried the first link you gave me and made the module. But for some
> reason myabe my own lack of knowledge on this issue but I dont get
> anything
> in return even though I'm logged in. I actually get an error "Compile
> Error.
> Expected Variable or procedure not a module" My name is correct for module
> and I cut and paste "fOSUserName" in the immediate window. I would like to
> populate this in a table if at all possible like this script does if you
> know
> the changes that need to be made (I would greatly appreciatte the help!"
>
> The problem with the below script is that does this but actually returns
> the
> rights (Admin or User) versus the user name. Its returning how you
> actually
> logged into Access versus the computer name or network login. This is a
> Module below by the way.
>
> Option Compare Database
>
> 'Begin logon_record ********************
*
>
> 'Option Compare Database
> 'Dim db As Database
> Dim strSQL As String
> Dim strFunctionName As String
>
> Function logon_user()
> strFunctionName = "logon_user"
>
> Set db = CurrentDb
> Dim f1 As String
> Dim f2 As Date
> f1 = CurrentUser
> f2 = Now
>
> 'Add this user to the logon table along with time
> strSQL = "INSERT INTO current_logins ( current_user, login_time ) VALUES
> ("
> & "'" & f1 & "'" & "," & "'" & f2 & "'" & ")"
> db.Execute strSQL
> End Function
>
>
> Function logout_user()
> strFunctionName = "logout_user"
> Set db = CurrentDb
> strSQL = "delete from current_logins " & _
> "WHERE current_user = " & "'" & CurrentUser & "'"
> db.Execute strSQL
> End Function
>
> 'End logon_record ********************
**
>
> Thank You in Advance
> Kurt
> RFGunMan
>
> "Douglas J. Steele" wrote:
>
>

Larry Linson

2006-01-26, 5:01 pm

Your "script" (actually those are procedures, "script" in Access refers to
Access macros, not VBA code) uses the CurrentUser function. That returns the
Access userid that is logged on... which, by default, if you haven't invoked
Access' security will be exactly what you are seeing, "Admin".

I am 100% certain that is not the code from the API sections of
http://www.mvps.org/access to which Doug referred you.

And, the code to which he referred you is not going to make the CurrentUser
function return anything other than the Access userid. You are going to have
to use it to retrieve the Network Login and/or Computer Name, and store them
in a variable if you want to use them.

Larry Linson
Microsoft Access MVP

"RFGunManRos" < RFGunManRos@discussi
ons.microsoft.com> wrote in message
news:962D664D-A0EA-4ED9-8297- D6729608D95A@microso
ft.com...[color=darkred]
> Douglas,
>
> I have tried the first link you gave me and made the module. But for some
> reason myabe my own lack of knowledge on this issue but I dont get
> anything
> in return even though I'm logged in. I actually get an error "Compile
> Error.
> Expected Variable or procedure not a module" My name is correct for module
> and I cut and paste "fOSUserName" in the immediate window. I would like to
> populate this in a table if at all possible like this script does if you
> know
> the changes that need to be made (I would greatly appreciatte the help!"
>
> The problem with the below script is that does this but actually returns
> the
> rights (Admin or User) versus the user name. Its returning how you
> actually
> logged into Access versus the computer name or network login. This is a
> Module below by the way.
>
> Option Compare Database
>
> 'Begin logon_record ********************
*
>
> 'Option Compare Database
> 'Dim db As Database
> Dim strSQL As String
> Dim strFunctionName As String
>
> Function logon_user()
> strFunctionName = "logon_user"
>
> Set db = CurrentDb
> Dim f1 As String
> Dim f2 As Date
> f1 = CurrentUser
> f2 = Now
>
> 'Add this user to the logon table along with time
> strSQL = "INSERT INTO current_logins ( current_user, login_time ) VALUES
> ("
> & "'" & f1 & "'" & "," & "'" & f2 & "'" & ")"
> db.Execute strSQL
> End Function
>
>
> Function logout_user()
> strFunctionName = "logout_user"
> Set db = CurrentDb
> strSQL = "delete from current_logins " & _
> "WHERE current_user = " & "'" & CurrentUser & "'"
> db.Execute strSQL
> End Function
>
> 'End logon_record ********************
**
>
> Thank You in Advance
> Kurt
> RFGunMan
>
> "Douglas J. Steele" wrote:
>
>

Sponsored Links