Who is using the database?
- Thread starter Carl_R
- Start date
Swannie
Registered User.
- Local time
- Today, 00:09
- Joined
- Nov 15, 2002
- Messages
- 26
What we did to get around the problem is to use the function that you can get to with this link
Function Call
We then created a table to record who was going into the database and when, and then when they were leaving the database.
That way we were able to have a reasonable level of security on the system as well.
Function Call
We then created a table to record who was going into the database and when, and then when they were leaving the database.
That way we were able to have a reasonable level of security on the system as well.
Hi
Thanks for the reply.
My Mum always said I was never really that smart when it came to making myself understood... after 36 and some years, nothing has changed...
I can see who has used the DB and what fields they have edited etc. via an audit feature. No probs there.
What I am after is a way to see, in real-time, how many users are in the actual database and who they are.
Our problem is this: Due to a management decision to roll the database out to all and sundry, we have no idea who will be using it. We want a way to be able to quickly see who is on the DB in case we have to send out email information notifying users to exit the DB for urgent maintenance.
Thanks for the reply.
My Mum always said I was never really that smart when it came to making myself understood... after 36 and some years, nothing has changed...
I can see who has used the DB and what fields they have edited etc. via an audit feature. No probs there.
What I am after is a way to see, in real-time, how many users are in the actual database and who they are.
Our problem is this: Due to a management decision to roll the database out to all and sundry, we have no idea who will be using it. We want a way to be able to quickly see who is on the DB in case we have to send out email information notifying users to exit the DB for urgent maintenance.
Swannie
Registered User.
- Local time
- Today, 00:09
- Joined
- Nov 15, 2002
- Messages
- 26
The way that we use this code here, is to write the user name into a table, and have another database linked to this one with a query that shows all people currently logged onto the database.
This is done with a boolean that that on closure of the database, you need to check the field.
This means that at any time, you can see who is logged into the database, and you can keep a log of who has been using the database.
Code -
Option Compare Database
Option Explicit
' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Const NoError = 0 'The Function call was successful
Function Main()
Dim x As String
x = GetUserName
DoCmd.OpenForm "frmName"
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLog")
With rs
If IsNull(DLookup("[field1]", "tblLog", "[Users]='" & x & "' AND [Active] = True")) = True Then
.AddNew
.Fields("Users") = x
.Fields("Time In") = Now()
.Fields("Active") = True
.Update
Else
MsgBox "xxxxxx" & vbCrLf & vbCrLf & "xxx", vbExclamation, "app1"
DoCmd.Quit
End If
End With
End Function
Function GetUserName()
' Buffer size for the return string.
Const lpnLength As Integer = 255
' Get return buffer space.
Dim Status As Integer
' For getting user information.
Dim lpName, lpUserName As String
' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)
' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)
' See whether error occurred.
If Status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
' An error occurred.
MsgBox "Unable to get the name."
End
End If
' Display the ID of the person logged on to the machine.
If IsNull(DLookup("[User]", "tblUsers", "[PRef] = '" & lpUserName & "' AND [Confirm] = True")) = True Then
MsgBox "xxx." & vbCrLf & vbCrLf & _
"Access is restricted", vbCritical, "xxxx"
DoCmd.Quit
'getusername = lpUserName
Else
getusername = DLookup("[User]", "tblUsers", "[PRef] = '" & lpUserName & "' AND [Confirm] = True")
End If
End Function
You will need to have a close database function with to set
Fields("Active") = False
This is done with a boolean that that on closure of the database, you need to check the field.
This means that at any time, you can see who is logged into the database, and you can keep a log of who has been using the database.
Code -
Option Compare Database
Option Explicit
' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Const NoError = 0 'The Function call was successful
Function Main()
Dim x As String
x = GetUserName
DoCmd.OpenForm "frmName"
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLog")
With rs
If IsNull(DLookup("[field1]", "tblLog", "[Users]='" & x & "' AND [Active] = True")) = True Then
.AddNew
.Fields("Users") = x
.Fields("Time In") = Now()
.Fields("Active") = True
.Update
Else
MsgBox "xxxxxx" & vbCrLf & vbCrLf & "xxx", vbExclamation, "app1"
DoCmd.Quit
End If
End With
End Function
Function GetUserName()
' Buffer size for the return string.
Const lpnLength As Integer = 255
' Get return buffer space.
Dim Status As Integer
' For getting user information.
Dim lpName, lpUserName As String
' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)
' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)
' See whether error occurred.
If Status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
' An error occurred.
MsgBox "Unable to get the name."
End
End If
' Display the ID of the person logged on to the machine.
If IsNull(DLookup("[User]", "tblUsers", "[PRef] = '" & lpUserName & "' AND [Confirm] = True")) = True Then
MsgBox "xxx." & vbCrLf & vbCrLf & _
"Access is restricted", vbCritical, "xxxx"
DoCmd.Quit
'getusername = lpUserName
Else
getusername = DLookup("[User]", "tblUsers", "[PRef] = '" & lpUserName & "' AND [Confirm] = True")
End If
End Function
You will need to have a close database function with to set
Fields("Active") = False
Create a form with a cmd button and an unbound listbox. Name the list box LoggedOn and the command button should be UpdateBtn. Then put ths coad behind the form. This will work for you as long as the database is on a network drive.
By the way I am not the original author of this code so I can't take credit for it. If anyone here knows who wrote this please let me know.
By the way I am not the original author of this code so I can't take credit for it. If anyone here knows who wrote this please let me know.
Code:
Option Compare Database
' Declare a record type to break down the user info
Private Type UserRec
bMach(1 To 32) As Byte ' 1st 32 bytes hold machine name
bUser(1 To 32) As Byte ' 2nd 32 bytes hold user name
End Type
Private Sub Form_Open(Cancel As Integer)
Me.LoggedOn.RowSource = WhosOn()
End Sub
Private Sub UpdateBtn_Click()
Me.LoggedOn.RowSource = WhosOn()
End Sub
'---------------------------------------------
' Subject : WhosOn()
' Purpose : Will read *.LDB file and read who's currently
' logged on and their station name.
'
' The LDB file has a 64 byte record.
'
' The station name starts at byte 1 and is null
' terminated.
'
' Log-in names start at the 33rd byte and are
' also null terminated.
'
' I had to change the way the file was accessed
' because the Input() function did not return
' nulls, so there was no way to see where the
' names ended.
'---------------------------------------------
Private Function WhosOn() As String
On Error GoTo Err_WhosOn
Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim sPath As String, X As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ' Defined in General
Dim dbCurrent As Database
' Get Path of current database. Should substitute this code
' for an attached table path in a multi-user environment.
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
sPath = dbCurrent.Name
dbCurrent.Close
' Iterate thru dbCurrent.LDB file for login names.
sPath = Left(sPath, InStr(1, sPath, ".")) + "LDB"
' Test for valid file, else Error
X = dir(sPath)
iStart = 1
iLDBFile = FreeFile
Open sPath For Binary Access Read Shared As iLDBFile
iLOF = LOF(iLDBFile)
Do While Not EOF(iLDBFile)
Get iLDBFile, , rUser
With rUser
i = 1
sMach = ""
While .bMach(i) <> 0
sMach = sMach & Chr(.bMach(i))
i = i + 1
Wend
i = 1
sUser = ""
While .bUser(i) <> 0
sUser = sUser & Chr(.bUser(i))
i = i + 1
Wend
End With
sLogStr = sMach & " -- " & sUser
If InStr(sLogins, sLogStr) = 0 Then
sLogins = sLogins & sLogStr & ";"
End If
iStart = iStart + 64 'increment to next record offset
Loop
Close iLDBFile
WhosOn = sLogins
Exit_WhosOn:
Exit Function
Err_WhosOn:
If err = 68 Then
MsgBox "Couldn't populate the list", 48, "No LDB File"
Else
MsgBox "Error: " & err.Number & vbCrLf & err.Description
Close iLDBFile
End If
Resume Exit_WhosOn
End Function
Last edited:
http://www.mvps.org/access/modules/mdl0055.htm
Take a look at this link. I have several databases that I need to know who is logged on, this has helped alot.
Take a look at this link. I have several databases that I need to know who is logged on, this has helped alot.
- Local time
- Yesterday, 18:09
- Joined
- Feb 28, 2001
- Messages
- 30,503
The biggest problem with this type of question is that Microsoft, despite their tendency to tout their WONDERFUL network support software, doesn't really support this kind of function so clearly.
In most time-sharing mainframes, you can run a function to dump the contents of the lock table, or can query the lock table for all users of a particular drive. That function is not so reliable under most MS operating systems. Nor is reading the contents of the .LDB a guaranteed answer.
I can tell you a couple of ways to do this, but they require some cooperation and/or tolerance from your user community.
Method #1: Build a startup form. Doesn't matter whether it does anything else at all, but make it do the following:
1. On DB startup, use the startup form to show something like a logo for a couple of seconds.
2. While the logo is up, use the Form_Load event routine to write an entry to some hidden table that you use for auditing. Have the entry say "Connecting." Make the entry include the user name from the CurrentUser function (if you are using workgrous) or use the name supplied from one of the many alternative methods you can find in this forum regarding the non-workgroup situations. Also include the date and time.
3. Have the startup form minimize itself once the audit entry has been written. DO NOT LET THE STARTUP FORM CLOSE ITSELF.
4. In the Form_Close event routine for the startup code, write another entry to that hidden table to include the user name and the entry "Disconnecting". Sub-Alternative: Have the Form_Close routine write a mini-SQL query that it then executes to whack all entries with the CurrentUser as a user name.
Now you should see Connect entries for active sessions. Or, you would see pairs of entries that have come and gone, plus unbalanced entries for active sessions.
Problem: This fails if the user in question suffers a system crash or the network takes a swan dive. You will get false "connected" entries in either case. So you might have to purge old session entries if they are older than some age that you choose arbitrarily. Or you might have to assume that no user will connect to the same database twice, so purge all but the oldest entry for each user.
Method #2: Again, use a startup form.
1. Make the form open up to show a logo.
2. Minimize the form. You can also hide it.
3. Before minimizing, set up a form timer.
4. In the Form_Timer routine, do a silent query of a hidden table of scheduled down-times.
5. In the Form_Timer routine after the query, do nothing for time events in the past.
6. Use Date_Diff to compute the time in minutes until the next scheduled operation in the future.
7. When this number of minutes falls into a certain range, kick off a message box that says "Everyone log off by time X."
8. Make this timer long enough to give folks time to do things but often enough that they will quickly get annoyed and log off.
9. (Optional, depending on just how nasty you really want to get...) When this number of minutes reaches 0, give one last message box that says, "I'm gone." Then do an Application.Exit
Now, all you have to do is place a scheduled (future) down-time event in the hidden table. You can protect it so that anyone could read it through the Startup Form but no-one could write it except for you. Then, if someone is in the database, they will have a minimized but still active (and hidden) form executing timer events every few minutes or so.
The faster the user's machine and the longer the time interval for the form's timer, the less likely your database users group would be to see the brief load that this query represents. If you make the message boxes into modal dialogues, they will not be ignored. Be warned that this is a really easy way to alienate your clientelle if you schedule the warning message boxes to occur more often than every 3-5 minutes and if they appear more than about 3-5 message-box iterations in total.
In most time-sharing mainframes, you can run a function to dump the contents of the lock table, or can query the lock table for all users of a particular drive. That function is not so reliable under most MS operating systems. Nor is reading the contents of the .LDB a guaranteed answer.
I can tell you a couple of ways to do this, but they require some cooperation and/or tolerance from your user community.
Method #1: Build a startup form. Doesn't matter whether it does anything else at all, but make it do the following:
1. On DB startup, use the startup form to show something like a logo for a couple of seconds.
2. While the logo is up, use the Form_Load event routine to write an entry to some hidden table that you use for auditing. Have the entry say "Connecting." Make the entry include the user name from the CurrentUser function (if you are using workgrous) or use the name supplied from one of the many alternative methods you can find in this forum regarding the non-workgroup situations. Also include the date and time.
3. Have the startup form minimize itself once the audit entry has been written. DO NOT LET THE STARTUP FORM CLOSE ITSELF.
4. In the Form_Close event routine for the startup code, write another entry to that hidden table to include the user name and the entry "Disconnecting". Sub-Alternative: Have the Form_Close routine write a mini-SQL query that it then executes to whack all entries with the CurrentUser as a user name.
Now you should see Connect entries for active sessions. Or, you would see pairs of entries that have come and gone, plus unbalanced entries for active sessions.
Problem: This fails if the user in question suffers a system crash or the network takes a swan dive. You will get false "connected" entries in either case. So you might have to purge old session entries if they are older than some age that you choose arbitrarily. Or you might have to assume that no user will connect to the same database twice, so purge all but the oldest entry for each user.
Method #2: Again, use a startup form.
1. Make the form open up to show a logo.
2. Minimize the form. You can also hide it.
3. Before minimizing, set up a form timer.
4. In the Form_Timer routine, do a silent query of a hidden table of scheduled down-times.
5. In the Form_Timer routine after the query, do nothing for time events in the past.
6. Use Date_Diff to compute the time in minutes until the next scheduled operation in the future.
7. When this number of minutes falls into a certain range, kick off a message box that says "Everyone log off by time X."
8. Make this timer long enough to give folks time to do things but often enough that they will quickly get annoyed and log off.
9. (Optional, depending on just how nasty you really want to get...) When this number of minutes reaches 0, give one last message box that says, "I'm gone." Then do an Application.Exit
Now, all you have to do is place a scheduled (future) down-time event in the hidden table. You can protect it so that anyone could read it through the Startup Form but no-one could write it except for you. Then, if someone is in the database, they will have a minimized but still active (and hidden) form executing timer events every few minutes or so.
The faster the user's machine and the longer the time interval for the form's timer, the less likely your database users group would be to see the brief load that this query represents. If you make the message boxes into modal dialogues, they will not be ignored. Be warned that this is a really easy way to alienate your clientelle if you schedule the warning message boxes to occur more often than every 3-5 minutes and if they appear more than about 3-5 message-box iterations in total.
Vassago
Former Staff Turned AWF Retiree
- Local time
- Yesterday, 19:09
- Joined
- Dec 26, 2002
- Messages
- 4,748
http://support.microsoft.com/default.aspx?scid=kb;en-us;176670
I downloaded this file and it works wonderfully in telling you how many users and what the computer names are that are logged into the database. Is there a way to tell the NT login of the person as well? You would think so, considering you can find this much out.
If anyone has a solution, please let me know.
Vassago
I downloaded this file and it works wonderfully in telling you how many users and what the computer names are that are logged into the database. Is there a way to tell the NT login of the person as well? You would think so, considering you can find this much out.
If anyone has a solution, please let me know.
Vassago
ghudson
Registered User.
- Local time
- Yesterday, 19:09
- Joined
- Jun 8, 2002
- Messages
- 6,194
chewy,
Your sample is only returning the info from the .ldb file which details the users "in" the database by their computer name and the workgroup name that they have logged into the db with.
This sample will allow you to grab the users network name but it will only do it for the computer that is running the code. I have yet to see any code that will allow you to extract a users network name from "your" computer.
Your sample is only returning the info from the .ldb file which details the users "in" the database by their computer name and the workgroup name that they have logged into the db with.
This sample will allow you to grab the users network name but it will only do it for the computer that is running the code. I have yet to see any code that will allow you to extract a users network name from "your" computer.
Thanks all.
Yes, I can't actually 'grab' the users name using access97 and NT
My workaround is courtesy our NT Team:
A txt file populated with the name, computer name etc. is appended to each time someone logs on to the network. In my access app, I can look at the .LDB file to ascertain who is logged on (by computer name). I then lookup the computer name in this file to get the persons name etc.
All a bit longwinded but it works.
Yes, I can't actually 'grab' the users name using access97 and NT
My workaround is courtesy our NT Team:
A txt file populated with the name, computer name etc. is appended to each time someone logs on to the network. In my access app, I can look at the .LDB file to ascertain who is logged on (by computer name). I then lookup the computer name in this file to get the persons name etc.
All a bit longwinded but it works.
