Limit number of concurrent Users connecting to the BE

[PercyN]

Hi,
I'm working on a database for a client which I intent to offer on a "per-user-logged-on" license basis. So for instance he may create 1000 users if he wants in the users table but only 5 for instance can logon concurrently because thats what he paid for. What is the best way to go about this?
1. Each time a front end is connecting to the back end is there a way to know how many users are already connected to the BE?
2. Or do I have to maybe create a tick box in the users table which I then check when the user logs on and uncheck when he logs off? That way I can count how many are logged on at any point in time. This one has its own problems, for instance is the particular workstation loses power and the user did not have time to logg off.

Is is there a better, easier way?

Thanks.

 

[madonnac]

from this site:

http://en.allexperts.com/q/Using-MS-Access-1440/showing-users-shared-database.htm

This will tell you who is logged in:

Option Compare Database
Option Explicit

Private Type UserRec
  bMach(1 To 32) As Byte  ' 1st 32 bytes hold machine name
  bUser(1 To 32) As Byte  ' 2nd 32 bytes hold user name
End Type
'-------------------------------------------------------------------------------------
'   Subject : WhosOn()
'   Purpose : Will read *.LDB file and read who's currently
'             logged on and their station name.
'
'             The LDB file has a 64 byte record.
'
'             The station name starts at byte 1 and is null
'             terminated.
'
'             Log-in names start at the 33rd byte and are
'             also null terminated.
'
'             I had to change the way the file was accessed
'             because the Input() function did not return
'             nulls, so there was no way to see where the
'             names ended.
'-------------------------------------------------------------------------------------
Function WhosOn() As String

On Error GoTo Err_WhosOn

  Dim iLDBFile As Integer, iStart As Integer
  Dim iLOF As Integer, i As Integer
  Dim sPath As String, x As String
  Dim sLogStr As String, sLogins As String
  Dim sMach As String, sUser As String
  Dim rUser As UserRec    ' Defined in General
  Dim dbCurrent As Database

' Get Path of current database.  Should substitute this code
' for an attached table path in a multi-user environment.

  Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
  sPath = dbCurrent.Name
  dbCurrent.Close

' Iterate thru dbCurrent.LDB file for login names.

  sPath = Left(sPath, InStr(1, sPath, ".")) + "LDB" ' change to "LACCDB" for newer versions of access

' Test for valid file, else Error

  x = Dir(sPath)
  iStart = 1
  iLDBFile = FreeFile

  Open sPath For Binary Access Read Shared As iLDBFile
  iLOF = LOF(iLDBFile)
  Do While Not EOF(iLDBFile)
     Get iLDBFile, , rUser
     With rUser
        i = 1
        sMach = ""
        While .bMach(i) <> 0
           sMach = sMach & Chr(.bMach(i))
           i = i + 1
        Wend
        i = 1
        sUser = ""
        While .bUser(i) <> 0
           sUser = sUser & Chr(.bUser(i))
           i = i + 1
        Wend
     End With
     sLogStr = sMach & " -- " & sUser
     If InStr(sLogins, sLogStr) = 0 Then
        sLogins = sLogins & sLogStr & ";"
     End If
     iStart = iStart + 64 'increment to next record offset
  Loop
  Close iLDBFile
  WhosOn = sLogins

Exit_WhosOn:
  Exit Function

Err_WhosOn:
  If Err = 68 Then
     MsgBox "Couldn't populate the list", 48, "No Lock File"
  Else
     MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
     Close iLDBFile
  End If
  Resume Exit_WhosOn

End Function

 

[smedvid]

Just some quick thoughts...
You can also check which PC Name is connected/accessing DB.
Be sure to develop a flush-the-queue rountine in case you have users/PCs connected for extended periods and they do not exit properly and appear to be connected still.
htwh,

Steve Medvid
IT Consultant & Web Master