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

 

[MichaelRed]

search here for [boo | kick] & users. The general technique is to have the startup object log in/out of a table on a BE db and use a flage in that to send warning message and shutdown the FE for Users. That leave the BE free for you for maintanence.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hwkranger]

dood - unless you're uber with API's chances are you won't be able to do this from within access. I was able to get it to work on my access file, but it's not the greatest.

microsoft has a app that they distribute freely that reads the record locking file (ldb). I would suggest using it: it's called LDBView.exe (gives machine ID not windows)

Otherwise, gear up for being the uber API writer. You need to use MSLDBUSR.DLL in an API - and you can get the information you seek



Randall Vollen
National City Bank Corp.

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

 

[hwkranger]

Just another thing about what i said with the API.

This is what I have for getting the number of users in the DB (without using the LDBView):

Private Declare Function adh_apiLDBUser_GetUsers Lib "L:\DATA\RETAIL\DATASERV\CENTRAL\MSLDBUSR.DLL" _
Alias "LDBUser_GetUsers" (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

this here will give you the COUNT of users, you can use this to decide if you want to do your compact - (this will warn you if you try to compact and there are users in the DB)

Randall Vollen
National City Bank Corp.

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

 

[FancyPrairie]

Check out thread705-639609 There's a function there called WhosLoggedOn.

 

[jdegeorge]

Hi...Sorry for not getting back sooner.

MichaelRed...I'll search for that. Thanks!

hwkranger...where'd you get that DLL file? Should I store it on the LAN where the MDB is saved/used or have everyone save it somewhere on their hard drive? Please let me know.

FancyPrairie...I checked out that thread and have no idea what the MDW file is. I have the same questions about the MDW file that I had with the DLL file. Please let me know.

Thanks to all for responding!



Jim DeGeorge

 

[FancyPrairie]

If you have security setup, then the database that contains the security info has a file type of .mdw By default, Access ships with System.mdw.

If you are not using security, then pass the name of the Back-end database to WhosLoggedOn. It really doesn't matter.

 

[jdegeorge]

FancyPrairie

I don't use Access security. I tried to pass the MDB name but access didn't like "ADOB". I changed it to "DAO" then it had a problem with "Invalid use of NEW" and other features.

I'm using Access XP. What version of Access was this written in?

Jim DeGeorge

 

[FancyPrairie]

Sorry, that should be ADODB not ADOB. I'll report that in the other thread.

 

[jdegeorge]

FancyPrairie

It was ADODB, I just typed it wrong in the POST. It still doesn't work.

Jim DeGeorge

 

[FancyPrairie]

The problem is (I think) the order in which your references are set. But, regardless, this is how it SHOULD look:

    Dim rst As ADODB.Recordset

    Call WhosLoggedOn("\\path\YourWorkgroup.mdw",rst)


Public Sub WhosLoggedOn(strWorkgroup As String, _
                        rst As

ADODB.

Recordset, _
               Optional varSortField As Variant = 0, _
               Optional varAscDesc As Variant = "Asc")

'********************************
'*  Declaration Specifications  *
'********************************

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    On Error GoTo ErrHandler

'*************************
'*  Open Workgroup file  *
'*************************

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=" & strWorkgroup

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

'*********************************
'*  Create Fields for Recordset  *
'*********************************

    Set rst = New ADODB.Recordset

    rst.Fields.Append rs.Fields(0).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(1).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(2).Name, adBoolean
    rst.Fields.Append rs.Fields(3).Name, adInteger
    
'*************************************************************************
'*  Loop thru Recordset and add Computer Name, etc. to user's recordset  *
'*************************************************************************

    rst.Open
    
    While Not rs.EOF
        
        rst.AddNew
        If (Not IsNull(rs.Fields(0))) Then rst.Fields(0) = rs.Fields(0)
        If (Not IsNull(rs.Fields(1))) Then rst.Fields(1) = rs.Fields(1)
        If (Not IsNull(rs.Fields(2))) Then rst.Fields(2) = rs.Fields(2)
        If (Not IsNull(rs.Fields(3))) Then rst.Fields(3) = rs.Fields(3)
        rst.Update
        
        rs.MoveNext
    Wend

    If (varSortField <> -1) Then
        rst.Sort = rst.Fields(varSortField).Name & &quot; &quot; & varAscDesc
    End If
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Set rs = Nothing
    Set cn = Nothing

    Exit Sub

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:

    Err.Raise vbObjectError + 20100, &quot;Error occcurred in function WhosLoggedOn&quot;, &quot;Error Number: &quot; & Err.number & vbCrLf & vbCrLf & &quot;Error Description: &quot; & Err.Description
        
    Resume ExitProcedure

End Sub

 

[jdegeorge]

Fancy

I opened a new module and pasted in the above code. I compiled it, but Access didn't like the external reference in the call line so I remarked it out.

I compiled again, but Access didn't like the DIM statement (User-defined type not defined), so I remarked it out.

I compiled again, but Access didn't like:

[tt]Public Sub WhosLoggedOn(strWorkgroup As String, _
rst As ADODB.Recordset, _
Optional varSortField As Variant = 0, _
Optional varAscDesc As Variant = "Asc&quot[/tt]
again, (User-defined type not defined).

Does this work with Office XP Pro and Access XP?

Jim DeGeorge

 

[jdegeorge]

MichaelRed

FE and BE. Are you suggesting that I split the database in 2 parts, where the tables are in 1 MDB and everything else is in another MDB with links to the tables? Won't I still have to know who the users are in order to boot them out before I do my udpates, etc.?

Jim DeGeorge

 

[FancyPrairie]

These statements:

Dim rst As ADODB.Recordset

Call WhosLoggedOn("\\path\YourWorkgroup.mdw",rst)

are just examples of how to call WhosLoggedOn.

The function works. I copied and pasted out of the previous thread and then test it again.

If you want to test it, here's a function that will display the results to you.

Function testme()

Dim rst As ADODB.Recordset

WhosLoggedOn "\\Path\YourBE.mdb", rst

rst.MoveFirst
While Not rst.EOF
MsgBox "Computer Name: " & rst.Fields(0)
MsgBox "Login Name: " & rst.Fields(1)
MsgBox "Connected: " & rst.Fields(2)
MsgBox "Suspect State: " & rst.Fields(3)
rst.MoveNext
Wend


End Function

 

[MichaelRed]

Splitting the db is usually a good idea. It reduces the network traffic and eases the 'tension' between you and the 'net cops' and usually improves performance. That part is, however not STRICTLY necessary. Having some table where, as each user opens the db, their information is captured is necessary. The process can achieve this in a number of ways, as amply illustrated by the plethora of suggestions. Getting the users logged off is slightly different than just finding out who they are (as shown so far in the thread). The site has several threads which discuss the entire process from more than one approach, and I was just attempting to (very briefly and broadly) outline a few of hte more common points. Usually, a major reason for finding the current users is to get them out of the app/db, so as to be able to perform some maintenance activity, so the (pseudo) reference/saearch should turn up a couple of threads which deal with the whole process (logging users on/off the db, booting them off, doing some maintenance activities (at start up or after the booting off). If your interest is somewhat more restricted, at east you can use the pieces and parts which are of interest.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jdegeorge]

Fancy

I really appreciate your working with me on this. I pasted in everything except for the DIM and CALL lines, and Access still gave me an error message (user-defined type not defined).

I can't get beyond compiling without an error let alone testing it as you're suggesting.

Again, does this work with MSOffice XP Pro and Access XP?

Jim DeGeorge

 

[jdegeorge]

Michael

Thanks for the explanation. I'll keep searching for the boot logic. I'll settle for kicking everyone out when I need to update until I can find the proper code for identifying users. Thanks!

Jim DeGeorge

 

[hwkranger]

Jim,

I've been watching the post and not really getting involved (again) since most people know more about access than I do.

I have a db that pretty much does what you want, but mines is DAO in access 97 and uses API calls. I don't use Access security - because I enherited it - and the person before me did a homemade security feature, and much of the database relies on this.

My users log in, using a form (user name/pw validations) where i am able to capture their machine ID (using MSLDBUSR.LL) also, this id is put in the user table - I can get who's logged in using the MSLDBUSR.DLL(which is located on the server and returns machine name) and relating the returned machine name to my table that has machine names and user login which gives me their phone number and name.

Maybe this is a bad way to do this, maybe it's very round about, but... from what I can see in Access97, it was the only way. I see in Access2k and XP there's other ways.

I won't suggest you doing things how I Do them, since you have Access XP, and there is a better alternative.






Randall Vollen
National City Bank Corp.

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

 

[FancyPrairie]

I'm using Access 2000. It shouldn't be a problem with XP. Again, I copied and pasted from the post. By default, Access references "Microsoft Access 9.0 Object Library". Check to see if your database references it. If it does, then post YOUR copy of the code.

 

[jdegeorge]

Fancy

How to I find out if my database is referencing MA 9.0 Object Library? I looked under TOOLS, OPTIONS and found nothing about object libraries.

Jim DeGeorge