How do you knock out users or Disconnect users from a database remotely?

[anoble1]

I have multiple Access 2003 databases. I do most of the editing in them all the time. I am having a problem of users all over the state being in the database, and won't get off of it. I currently use LDB Viewer. (Which just tells me the computer name that's currently connected). But, is there a way I can write something that will knock everyone out, of whomever I choose, so I can edit the database?

Thanks

[Megalog]

I keep a table in the backend database that stores user login/logout information. I also have a boolean field that only I can access. In the client frontend, there is a hidden form that checks that table every 5 minutes. If the boolean field is set to True, then it saves whatever record they are working, logs them out, and closes access.

So, at any time I can log in and check who's actively using the database, and issue warnings or plain kick them all out. It works great for when I'm doing a late night update and I discover that 3 people left their databases running when they left.

Also, you are using a Front End/Back End setup for your clients right? They're not all logged into the same file?

[NeoPa]

Not easy.

The locking system is designed to protect the integrity of the data within the database. You'd need to take that into consideration in any code you developed. I suspect something like this would involve a great deal of care taken to ensure the data was not compromised in any way in the process.

[missinglinq]

This site has a download with a working example and a number of other links concerning detecting who's currently using the database:

http://www.accessmvp.com/JConrad/acc...e/kickoff.html

Linq ;0)>

[anoble1]

I found a similar thread. And I have a question on it. Please see the question on the bottom.

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long
 
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=S:\Workgroups\APC Power Delivery-Contract Services\Safety Meeting.mdb"
 
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=S:\Workgroups\APC Power Delivery-Contract Services\Safety Meeting.mdb"
 
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
 
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
 
    'Output the list of all users in the current database.
 
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name
 
    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), _
        rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Wend
 
End Sub

Then you push CTRL + G, then type ShowUserRosterM ultipleUsers
and it list all the computer names connected. But, how do you get the LOGIN_NAME or the NTID of the person to work on there?

[NeoPa]

I could spend another ten minutes trying to make sense of the question, but I guess it's really down to you to spend the time to do that. I'll have another look if you do.

[twinnyfo]

Megalog,

I would be very interested in your code for viewing users and kicking people out of the database. I have frequent similar problems. It always seems that when I schedule an update over lunch (or evening), that this is the one time that everyone forgets to log out of the DB. i would love to have the ability to force a save (because I don't want to lose anything) and remove the user from teh system so I can perform updates.

Please post your code or contact me directly. I'd love to customize for my DB.

Many thanks!

[anoble1]

I assume you are using an Access database? If so, how many copies do you have of the database? I have 2. One on my local computer to where only I can access it. And I have one more on the network drive that everyone can see. Is yours like that?

[twinnyfo]

@anoble1,

Yes, on both counts: Access 2007, local master copy of database and second copy on a shared server. The time required for me to have users out of the DB is just long enough for me to copy over my new changes (several seconds, really), but is delayed when users have the DB locked for editing and run out to grab a byte to eat.

[NeoPa]

Moderator comment:
Technically this is a hijack, but bearing in mind ANoble1 seems to be engaging the interloper, and the question never got a satisfactory answer, I'm inclined to let it ride (Splitting it away into another thread that made any sense at all would be tricky anyway - to say the least).

If anyone manages to work out what's required and actually provide a solution from these extra posts then that's an added bonus.