Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Multiuser / Networking / January 2006

Tip: Looking for answers? Try searching our database.

How to Identifier Users

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jhrBanker - 09 Jan 2006 19:19 GMT
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 - 09 Jan 2006 19:48 GMT
connect to the computer using computer management and look at the open files
will show the open file and who has it open

>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 - 09 Jan 2006 22:37 GMT
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.

Signature

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

>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
jhrBanker - 11 Jan 2006 01:02 GMT
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
====================================================

> If anyone's in the database, there will be an LDB file in the same folder.
>
[quoted text clipped - 9 lines]
> > identify who they are so that I can contact them.
> > Thanks
Douglas J. Steele - 11 Jan 2006 23:31 GMT
I think you may have to modify that routine to write the data to a table,
and bind your form to that table.

Signature

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

> Thanks Doug, I think this will work for me.  I followed the instructions
> and
[quoted text clipped - 30 lines]
>> > identify who they are so that I can contact them.
>> > Thanks
jhrBanker - 12 Jan 2006 15:09 GMT
I understand that I have to modify the code.  My question is, what
modifications are required?  The table is bound to the form.
====================================================

> I think you may have to modify that routine to write the data to a table,
> and bind your form to that table.
[quoted text clipped - 33 lines]
> >> > identify who they are so that I can contact them.
> >> > Thanks
Douglas J. Steele - 13 Jan 2006 02:22 GMT
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 ShowUserRosterMultipleUsers()
   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(adSchemaProviderSpecific, _
   , "{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

Signature

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

>I understand that I have to modify the code.  My question is, what
> modifications are required?  The table is bound to the form.
[quoted text clipped - 45 lines]
>> >> > identify who they are so that I can contact them.
>> >> > Thanks
Jeff O - 10 Jan 2006 21:14 GMT
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

>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
jhrBanker - 11 Jan 2006 20:06 GMT
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.

=============================

> 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
[quoted text clipped - 9 lines]
> >identify who they are so that I can contact them.
> >Thanks
Jeff O - 12 Jan 2006 21:39 GMT
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 **********************

>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
[quoted text clipped - 7 lines]
>> >identify who they are so that I can contact them.
>> >Thanks
RFGunManRos - 23 Jan 2006 18:11 GMT
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".

> 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
[quoted text clipped - 68 lines]
> >> >identify who they are so that I can contact them.
> >> >Thanks
Douglas J. Steele - 24 Jan 2006 00:13 GMT
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.

Signature

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

> Jeff,
>
[quoted text clipped - 87 lines]
>> >> >identify who they are so that I can contact them.
>> >> >Thanks
RFGunManRos - 24 Jan 2006 14:56 GMT
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

> 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
[quoted text clipped - 75 lines]
> >>
> >> 'End logon_record **********************

> >> >Jeff:  Thanks for the offer.  I also have a log file, but I don't
> >> >actually
[quoted text clipped - 11 lines]
> >> >> >identify who they are so that I can contact them.
> >> >> >Thanks
Douglas J. Steele - 24 Jan 2006 23:58 GMT
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

Signature

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

> Douglas,
>
[quoted text clipped - 162 lines]
>> >> >> >identify who they are so that I can contact them.
>> >> >> >Thanks
Larry Linson - 25 Jan 2006 23:17 GMT
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

> Douglas,
>
[quoted text clipped - 162 lines]
>> >> >> >identify who they are so that I can contact them.
>> >> >> >Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.