Determing if Other Users are Connected

[Gaming_World]

For an application I am writting, I would like to have a system to identify if other users are currently connected to the database (with editing permissions), and whether any of those users are actively editing a record.

The reason for needing this information is that I would like to provide a notification to the user, yet still allow mutiple users to edit the database at once.

I need to know if anybody (even persons not using my program, but using other database editing applications) have the permission to edit the database, and whether they are accually editing the database.

What is the best way to get this information?

My program is currently setup to use the ADODB system, and is currently using an access database (mdb format, for ease of editing and viewing while programming).

Also, I am currently using Visual Basic 6.

[mendhak]

In your user table, you can have an extra column which specifies the user's role as well as a bit field possibly with a 1 or 0 to indicate whether they are logged in or not.

All you would then need is to retrieve this information using SQL. Example

SELECT username WHERE loggedin=1 AND rank=5

[si_the_geek]

That would be a way to do it for programs you write, but the bit about "using other database editing applications" unfortunately blows the whole idea out of the water. Obviously you cannot alter every other program to follow your rules, and they would not give the information you want normally.

If you are happy to only have this for editing that occurs in your program, then mendhak's method is probably the way to go (possibly with an extra table for "editing" details). Otherwise you cannot really get the info, so should just use an appropriate locking scheme for your recordsets (possibly Pessimistic), and write code to deal with any errors that occur.

[mendhak]

Oh good point, I didn't see that part in his post.

[Gaming_World]

I don't really have a user table, as there is no real need in the application to have people log-in to use it. I have noticed that when the database is in use, it creates a ldb file, and I would think there should be some way to determine if there is any other process (on any computer or user on the computers) with the database open for writing.

Also, I have to know if the user disconnects, even improperly from a crash.

[si_the_geek]

Well there isn't actually any way to know for sure who is connected to an Access database - especially if their computer has crashed.

There is an LDB viewer tool somewhere on the Microsoft web site, which is about the best you can do (with an Access database). In my experience with it, it wasn't too reliable, or clear who was supposedly using the database (it just gave computer names).

[Gaming_World]

I don't really care who is accessing the database, I just want to if somebody is accessing it, aside from the current user (meaning the person currently on the local copy).

Basically, the idea is to display a stop light on the status bar, giving the user a notification if somebody else currently has the database open.

The three states I want to be able to read for sure are:

• Green Light: The current user is the only one with the database open.
• Yellow Light: Atleast one other user has the database open in read/write mode (I don't care about read-only mode).
• Red Light: Atleast one other user is currently editing the database (has atleast one record open with read/write permission; again, I don't care about read-only mode)

If possible, I might also add a forth light, for if another user is connected in read-only mode.

[Gaming_World]

I guess I could take mendhak's idea, and add in a time-out to handle if a person crashes. If possible, however, I would like to use a more generic system that can handle other applications, which may not follow the specifications for the database.

[si_the_geek]

Access databases are very limited in multi-user situations, so unfortunately this kind of thing is complex and unreliable.

Reading the LDB file is probably the most accurate way of knowing which/how many computers are connected to the database (you would need to determine which one was the current user), but you will have no idea of what they are doing. I dont think this works properly in the case of a crash either.

An expansion of mendhak's idea is the best you can do for what you want - but it will not work for other applications, and has the potential to give false readings under some situations (such as if anyone takes longer than your timeout to do their editing).


Whatever you do it will not be entirely accurate, so you still need to deal with locking & errors as mentioned above.

[Gaming_World]

As the point isn't to prevent errors, but just to let the user know how "safe" it is to make modifications, having it somewhat inaccurate woun't matter too much.

Unless somebody can come up with a better solution, I will just end up using mendhak's idea.

[RobDog888]

An easy way to determine if someone else other then yourself is connected to an Access db is to attempt to connect to it Exclusively. If anyone at all is connected or has the db open it will generate an error. If you are the only one connected then it will allow the connection. No need to read an ldb file as stated it ios an unreliable method.

[Gaming_World]

An easy way to determine if someone else other then yourself is connected to an Access db is to attempt to connect to it Exclusively. If anyone at all is connected or has the db open it will generate an error. If you are the only one connected then it will allow the connection. No need to read an ldb file as stated it ios an unreliable method.

That would work, except I don't want to accidently lock somebody else out of the database if they attempt to connect during the few seconds I might have it open excusiavilly.

[RobDog888]

You could always have them just restart the program and blame it on the network

Anyways, what type of editing do you mean? Object editing or recordediting. If record then just set the lock typoe to edited record and only one person at a time can edit the same record.

[Gaming_World]

You could always have them just restart the program and blame it on the network

Anyways, what type of editing do you mean? Object editing or recordediting. If record then just set the lock typoe to edited record and only one person at a time can edit the same record.

Talk about a cop-out .

Anyways, the point is to know if somebody is accessing/editing, not preventing multiple people from doing so.

[RobDog888]

But your not stating if it accessing/editing a record or an object?

[Gaming_World]

Basically, I want to know if they:
[li][*]have the database open with writing permission (anything contained within the database)[*]have a record (or any item within the database) currently open for editing.
[/li]

Currently the database consists of about 7 tables (don't have it open right now to check for sure)..

Not sure exactly what object editing is (I am fairly new to doing database programming, but know VB quite well otherwise), but currently all editing is done on records (and eventually I'll probably add functions to add/edit tables).

Basically, I want to know how "safe" it is for a user to edit the table, and display the information to the user.

[RobDog888]

You can not get a listing of what records others are viewing for editing. Only error trapping for when you may be attempting to edit the same record.

Object editing is when you may be editing a table or some other db object structurally. For ex. adding a field or changing a fields data type or such.

Sounds like what you really need is some kind of logging on the users actiuons. This can only be done manually by writting it into your program. Whenever you start an add or edit procedure, write that action out to a logging table or textfile so you can use it when you need to audit for whos doing what.

[Gaming_World]

As I don't care which records are being editing, I am just planning to use mendhak's idea coupled with a time-out on the entries to get the status. Its too bad there isn't an easier way to do it though.

[RobDog888]

But you may have allot of problems with that apporach as when they log out you will need to reset the field back to "0" but if there is a problem or the user disconnects abruptly, like from just closing the program or logging out of windows/reboots, you will not be able to set the logged in state as "0".

[Dnereb]

You could build a class to make an entry in your active user table and managing all database activity by an ado recordset (withevents) downside you need to do all database activeti through the class t o make it work
upside a class terminate event will fire in most situations on ending your program. the basic structure would be a bit like this (not tested just some off the basic routines names to handle the database)

Option Compare Database
Option Explicit

Private WithEvents Rc As ADODB.Recordset
Private WithEvents Cn As ADODB.Connection

Private Sub Class_Initialize()

Set Rc = New ADODB.Recordset
Set Cn = New ADODB.Connection

End Sub

Private Sub Class_Terminate()

On Error Resume Next

'remove user entry from table if possible

Rc.Close
Cn.Close

Set Rc = Nothing
Set Cn = Nothing

On Error GoTo 0

End Sub

Private Sub Cn_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)

'handle SQL inserts inhere

End Sub

Private Sub Rc_FieldChangeComplete(ByVal cFields As Long, ByVal Fields As Variant, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'code to remove signal edit is in progress in the user table

End Sub

Private Sub Rc_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'code to signal edit is in progress in the user table

End Sub

Public Function OpenConnection() As Boolean

    Dim bResult As Boolean
    OpenConnection = bResult

End Function

Public Function CloseConnection() As Boolean

    Dim bResult As Boolean
    CloseConnection = bResult

End Function

Public Function Openrecordset() As ADODB.Recordset

    'give an instance to RC to the program for use.

End Function

doden is rokelijk

[Gaming_World]

But you may have allot of problems with that apporach as when they log out you will need to reset the field back to "0" but if there is a problem or the user disconnects abruptly, like from just closing the program or logging out of windows/reboots, you will not be able to set the logged in state as "0".

Thats the point of the time out .

[Gaming_World]

You could build a class to make an entry in your active user table and managing all database activity by an ado recordset (withevents) downside you need to do all database activeti through the class t o make it work
upside a class terminate event will fire in most situations on ending your program. the basic structure would be a bit like this (not tested just some off the basic routines names to handle the database)

Option Compare Database
Option Explicit

Private WithEvents Rc As ADODB.Recordset
Private WithEvents Cn As ADODB.Connection

Private Sub Class_Initialize()

Set Rc = New ADODB.Recordset
Set Cn = New ADODB.Connection

End Sub

Private Sub Class_Terminate()

On Error Resume Next

'remove user entry from table if possible

Rc.Close
Cn.Close

Set Rc = Nothing
Set Cn = Nothing

On Error GoTo 0

End Sub

Private Sub Cn_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)

'handle SQL inserts inhere

End Sub

Private Sub Rc_FieldChangeComplete(ByVal cFields As Long, ByVal Fields As Variant, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'code to remove signal edit is in progress in the user table

End Sub

Private Sub Rc_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'code to signal edit is in progress in the user table

End Sub

Public Function OpenConnection() As Boolean

    Dim bResult As Boolean
    OpenConnection = bResult

End Function

Public Function CloseConnection() As Boolean

    Dim bResult As Boolean
    CloseConnection = bResult

End Function

Public Function Openrecordset() As ADODB.Recordset

    'give an instance to RC to the program for use.

End Function

doden is rokelijk

That is pretty much what I was planning to do, and in fact what I have already been working on programming.

The design of the program thus far involves tons of class modules, and piles of custom DLLs. As I currently have it set up, almost all of the program will be contained in seperate DLLs and loaded via a plug-in system.

All of the functionality for the connected users will be contained within one of the plug-ins, and so it call be called under any case, unless the database connection is lost.