MS Access Forum / Multiuser / Networking / January 2006
How to Identifier Users
|
|
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
|
|
|