Determining Who Else Is Using My Database

[jdegeorge]

Hi:

I have a compact/backup routine that I want to run when only I close the database. The routine works fine, but I want to check to see if anyone else is using the datbase before the routine runs so I don't corrupt what they're doing.

We're using Windows XP Pro and Access XP (2002).

I found some code on this site and on MVPS, but all the code was to capture "NT" user IDs, etc. Some of the code is corrupting my database when I try to use it. Thankfully, I keep backups of my database.

Does anyone know how I can get a list of all UserIDs on the Windows XP platform? I'm listening... Thanks!

Jim DeGeorge

 

[FancyPrairie]

Open a module and goto Tools|References

 

[jdegeorge]

Fancy

That explains it, I guess. I don't have 9 but rather 10. Is 9 something that I can download from Microsoft?

Jim DeGeorge

 

[Enlin]

Although it might not give you detailed information, I just do a check for the record locking information, so something like:

If Dir(&quot;database.ldb&quot <> &quot;&quot; Then
MsgBox &quot;Compact failed. Please make sure all users have closed the database before compacting&quot;
Exit Sub
End If

Make sure you change the directory to wherever your database is kept. Crude, but simple. Of course you could just look for the file manually without ever having to touch Access. There's no need to any program to read the information in this file either. It's much easier to open it in Notepad or similar.

 

[jdegeorge]

Enlin

Thanks. I'll try this and post back my results.

Jim DeGeorge

 

[cdavisdccb]

Guys, there is a really more simple solution.

Just right click on the .ldb (locking database) file icon and choose <open with> - Notepad.

This will show the pc name.

I made it a shortcut on my desktop. It will always have the same name as your database but the suffix will be .ldb instead of .mdb Therefore when no one is in the database, you'll get an error message stating that windows couldn't find the file.

Hope this helps, it seemed easier than writing code.

Chris

&quot;It doesn't matter how idiot-proof you make it, someone makes a better idiot!&quot;

 

[jdegeorge]

Chris

This is much simpler, but the problem with my firm is that they use a PC asset number as the PC NAME rather than the person who's actually using the PC. So, all I get is something like:

NYCHFCDA532865 Admin

Any way to get the NT ID of all the logged-in users? That's what I really need.

Thanks!

Jim DeGeorge

 

[hwkranger]

I think I posted this before, but here's a solution(once again)

This was written by someone at National City, on a database that I now support. I've used similiar strategies, if you look at this - it shows you how to use the API and the call, and then returns the number of users in the database.

You can get the MSLDBUSR.DLL from Microsoft. It's an unofficial library file.

Private Declare Function adh_apiLDBUser_GetUsers Lib &quot;L:\DATA\RETAIL\DATASERV\CENTRAL\MSLDBUSR.DLL&quot; _
Alias &quot;LDBUser_GetUsers&quot; (strUserBuffer() As String, _
ByVal strFilename As String, ByVal lngOptions As Long) As Integer

Const adh_apiAllLDBUsers = &H1
Const adh_apiLDBLoggedUsers = &H2
Const adh_apiLDBCorruptUsers = &H4
Const adh_apiLDBUserCount = &H8

Public Function RemoveTicketsCheck()
Dim intUsers As Integer
Dim varUser As Variant
Dim strUsers As String
Dim db1 As String
Dim varDb As Variant
Dim FunctionRan As Integer

ReDim astrUsers(1) As String
db1 = CurrentDb.Name
varDb = db1

If Not IsNull(varDb) Then
intUsers = adh_apiLDBUser_GetUsers(astrUsers(), _
varDb, adh_apiLDBLoggedUsers)
End If

If DLookup(&quot;[RemoveNotice]&quot;, &quot;FILE INFORMATION&quot = &quot;&quot; Then
FunctionRan = 0
ElseIf DLookup(&quot;[RemoveNotice]&quot;, &quot;FILE INFORMATION&quot <> &quot;&quot; Then
FunctionRan = 1
End If

If FunctionRan = 1 Then
MsgBox (&quot;The Remove Old Information procedure has already been run for the month.&quot
Exit Function

'*********** LOOK HERE **************
ElseIf FunctionRan = 0 And intUsers > 1 Then
MsgBox (&quot;There are currently &quot; & intUsers & &quot; users in the file. This procedure cannot be run if more than one person is in the file. Please have all other personnel exit the file before trying to run this procedure.&quot
Exit Function
ElseIf FunctionRan = 0 And intUsers = 1 Then
MsgBox (&quot;The system will now Remove the Old Information. Please be patient, it may take the procedure a few minutes to complete. Please press OK to begin.&quot
DoCmd.RunMacro &quot;Remove_Tickets&quot;
End If

End Function

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

for knowing EXACTLY who is logged on, I use a form that holds their Operator ID, and I keep a table that when they log on in the morning that keeps track of machine name, and username.

Since you know how to get machine name, this will give you the ability to relate machine name to user name. If you want, you can even put a little phone number by their name, and call them.....

Private Declare Function apiGetComputerName Lib &quot;kernel32&quot; Alias _
&quot;GetComputerNameA&quot; (ByVal lpBuffer As String, nSize As Long) As Long

Public Function LogUserMachine()
Dim OpName As String
OpName = [Forms]![Operator]![OperatorID]

Dim xpMachineName As String
xpMachineName = fMachineName

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset(&quot;Operator&quot

With rst
If .RecordCount Then
.MoveFirst
Do Until rst.EOF
If !OperatorID = OpName Then
.Edit
!ComputerName = xpMachineName
.Update
End If
.MoveNext
Loop
End If
End With
End Function
Private Function fMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fMachineName = Left$(strCompName, lngLen)
Else
fMachineName = &quot;&quot;
End If
End Function




Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[jdegeorge]

Randall

Thanks for your recent suggestions. I wound up following your 2nd suggestion of storing the user name and deleting it when the person logs out. It's working okay, but not 100% because if the user closes the MDB without using the Close button, the user log isn't updated and I'm left with phantom users. But, it's okay for now.

Thanks!

Jim DeGeorge