Limit no of ConCurrent Users to MS-Access 2003/2007

[tasawer]

Hi Serious Programmers,
I wish to read the LDB file to limit the no. of concurrent users to an msaccess database.
On a test database that has nothing but FORM1, I have implemented thread705-278774 but I get Error 53, Bad filename or number.
I have checked the filenames and datapaths but it is all beyond my understanding.

My actual Database is FE on Several machines and BE is on a shared/mapped drive S:\
I have both Access2003 and Access 2007 users.

Any help to resolve this is greatly appreciated.

Thanks

 

[RuralGuy]

I think you will be disappointed until you impliment your own log-in scheme. The .ldb is not particularly reliable for this effort.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

Hmmm... and where do I start?

I did try to implement a scheme where I created a table of users that updtaes as each users logs in and after the license number is reached, it refuses further connections.
However, if the system crashes, or user logs out inappropriatly, technically, he remains logged.

I could also check if LDB file exists on PC1, PC2, PC3 etc and if it does then refuse further connections?

Any ideas?

 

[RuralGuy]

That is the same problem you would encounter with the ldb file.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

by reading the main LDB, if no of users is breached, I can throw a message to ask to restart main server in which case the LDB file would be reset.

if a system crashes or user logs out erroneously, same user can log back in.

How do I reset my table?

providing a RESET ALL USERS button would look very unprofessional.

 

[RuralGuy]

I take it you have a split application FE/BE and have a limit to the number of concurrent users, right? Is there a license charge for a seat at the table? Can the user buy additional seats? How do you plan to handle that?

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

I have a global constant 'LimitUsers=x'.
I have a users table that allows to add many users, but only x number can be active at any one time.

problem is that one user can have multiple logins.

I added an extra field 'LOGGEDIN" that is set to true when users logs in and set to false when user logs out.
This way, Same user is disallowed multiple logins.
However, if there is a system crash or erroneous log out, LOGGEDIN remains set to true and user remains locked out.

talking in terms of system crash and not breach of users, I have an idea to RESET all users via admin login, but this would mean all users having to log out.
Alternatively, administrator could authorise login, but that would be troubling him everytime there is a system crash.

 

[RuralGuy]

How about using an Activity Timer to keep the system aware of who is logged on. So much time goes by without activity and the user is kicked off of the system and/or the table is updated.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[RuralGuy]

I guess it would be helpful to have a link:

http://support.microsoft.com/Default.aspx?kbid=210297

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

This still does not combat the system crash issue, unless we wait the specified time before login (say 15 minutes!!!).

When we have several forms that could be open at one time, how can I run the timer that can check activity on any form?

 

[RuralGuy]

I open an invisible form with the AutoExec macro that I use for housekeeping. It is also the idle time counter and the persistant connection keeper. With the timer in this form you check for Screen.ActiveForm.Name changing. If it does then change a value in the BE so the supervisor can tell this user is still running. You could also use it to log off and shut down the ap if you wanted.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

I have found my perfect LDB solution. Thanks

 

[RuralGuy]

Care to share your solution with the others that read this thread?

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

of course.. I got busy implementing it...
Create a module with the code given below.

download the file msldbusr.dll and copy to windows\system

From within your applciation, you can get the no of users connected with GETUSERS()
The rest is upto you how you as to how you implement it.
if you check the debug window, you can even get the PC names of logged on users.

***Start of Code
Option Compare Database
Option Explicit

Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" _
(lpszUserBuffer() As String, ByVal lpszFilename As String, _
ByVal nOptions As Long) As Integer

Public Function GetUsers(Optional StrDbPath As String)

ReDim lpszUserBuffer(1) As String
Dim intLooper As Integer
Dim Cusers As Long
Dim strMsgBox As String

On Error GoTo Err_GetUsers

' Check to see if a database path was passed
' to the function. If the argument was not used,
' assume that we're to investigate the .ldb
' of the current database.
If IsMissing(StrDbPath) Or StrDbPath = "" Then
StrDbPath = CurrentDb.Name
End If

' Set Cusers to the number of computers currently connected
' to the database. Insert computer information into the
' lpszUserBuffer array.

' Arguments of LdbUser_Get Users:
' 1 = All users who have logged in since the LDB file was created
' 2 = Only users who are currently logged in
' 4 = Only users who are causing the database file to be corrupted
' 8 = Just return the count of users

StrDbPath = "S:\ACC786_data.mdb"
Cusers = LDBUser_GetUsers(lpszUserBuffer(), StrDbPath, 2)

GetUsers = Cusers

' Print possible errors returned by the function.
Select Case Cusers
Case -1
strMsgBox = "Can't open the LDB file"
Case -2
strMsgBox = "No user connected"
Case -3
strMsgBox = "Can't Create an Array"
Case -4
strMsgBox = "Can't redimension array"
Case -5
strMsgBox = "Invalid argument passed"
Case -6
strMsgBox = "Memory allocation error"
Case -7
strMsgBox = "Bad index"
Case -8
strMsgBox = "Out of memory"
Case -9
strMsgBox = "Invalid Argument"
Case -10
strMsgBox = "LDB is suspected as corrupted"
Case -11
strMsgBox = "Invalid argument"
Case -12
strMsgBox = "Unable to read MDB file"
Case -13
strMsgBox = "Can't open the MDB file"
Case -14
strMsgBox = "Can't find the LDB file"
End Select

If Not IsEmpty(strMsgBox) And strMsgBox <> "" Then
MsgBox strMsgBox, vbCritical, "Error"
Exit Function
End If

' Print computer names to Debug window.
For intLooper = 0 To Cusers - 1
Debug.Print "User"; intLooper + 1; ":"; _
lpszUserBuffer(intLooper)
Next

Exit_GetUsers:
Exit Function
Err_GetUsers:
MsgBox Err.Number & " " & Err.Description, , "Get Users"
End If
Resume Exit_GetUsers

End Function

***End of Code

 

[RuralGuy]

Excellent! Thanks for the update.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[RuralGuy]

FYI, the ldb file will have the same problem you described before. At least you have a solution that you feel works, and that is what is important.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[tasawer]

Could you please elaborate on this unforeseen problem!

 

[RuralGuy]

You already described it in your second post in this thread.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit