Max # of users? A way to limit connections?

[pat_nospam]

I'd like to be able to throttle the max number of connections down to a certain number, but then package the database with the packaging wizard in Office XP Developer so that number can't be changed (i.e. if the max connection level is 3, only 3 people at a time can logon).

Thanks in advance for ideas,

Pat

 

[John.Woody]

If your users log on and off the database you can count the number of users logged on when it is opened. If that exceeds the number you want the force the database to close. You could either hard code the maximum number or put it in a hidden table according to how secure you want it and whether you may want to be able to increace the number of users without having to send out a new db.

hth

John

 

[pat_nospam]

I agree, hard coding the number into the be.mdb and then checking connections to it seems the best way to do this. I'll do some searching for some sample code to point me in the right direction of accomplishing this -- or if someone has a link.

Thanks again

Pat

 

[pat_nospam]

Okay, I created a table tblUserlogin and tblUserlogoff which seems to track the usage fairly well, but if someone doesn't exit the proper way, it skews the system.

I'm not quite sure how to go about hard coding a limit (perhaps reading the LDB file directly -- which I may be able to tool around with), but if you can point me in the right direction. Does Access have a variable set in MSysObjects that I can query

As always, thanks in advance!

Pat

 

[pat_nospam]

Can I use type -32758 to count the number of users in the linked tables?

---------------------

tblObjectTypeCodes:
Type TypeDesc
-32768 Form
-32766 Macro
-32764 Reports
-32761 Module
-32758 Users
-32757 Database Document
-32756 Data Access Pages
1 Table - Local Access Tables
2 Access Object - Database
3 Access Object - Containers
4 Table - Linked ODBC Tables
5 Queries
6 Table - Linked Access Tables
8 SubDataSheets

 

[Mile-O]

Okay, I created a table tblUserlogin and tblUserlogoff which seems to track the usage fairly well, but if someone doesn't exit the proper way, it skews the system.

Why two tables?

If you have the fields:

tblUserLog
LogID - Autonumber (primary key)
UserID - Number (foreign key to users tables)
TimeIn - Date/Time
TimeOut - Date/Time

For those in you can query the TimeOut field where it Is Null


For a hardcoded user constant just put, in a module:

Public Const MAX_USERS = 3

On the startip of the database some code can eaily check:

If DCount("LogID", "qryTotalUsers") = MAX_USERS Then
    MsgBox "You can't get in"
Else
    ' proceed with writing user to table and allowing access
End If

 

[pat_nospam]

Exactly -- This is the approach I am taking (only with two tables, but one will be much cleaner), but I still run into the problem that if someone doesn't use the "exit" button, the TimeOff field isn't populated by the query and it skews my system (query on Null returns logged in users + users not properly logged off).

I was hoping that reading the LDB or using some hard coded VBA to read MSysObjects would give me more reliable data, but the table method is the only one I knew how to implement.

Just curious if there are alternative solutions or methods to make sure the user count is accurate.

Thanks again for the help -- it's much appreciated!!

Pat

 

[Mile-O]

I've done a similar login eample or two already.

The trick is to have the very first form that opens be invisible. On it's Open event, log the user in, and on it's Close event, log the user out.

 

[Kevin_S]

but if someone doesn't exit the proper way, it skews the system.

Pat - just force the user to exit the way you intended them to - you can accomplish this by disabling the "X" on the main access application window (search this forum for the way to do this as its been posted alot!) and eliminate the standard menu bars so they cannot exit from here... then the only way to get out will be the exit button you created...

HTH,
Kev

 

[pat_nospam]

Okay, one last thing I want to put out there before I go redo my login with the above mentioned system. I've adapted this from the Whos_On code that's floating around that reads the LDB file. I could launch this from the On_Load() of my intro form to check for users -- since the Whos_On code arranges the names and then loops through, I just tinkered by setting the IF stop to the loop variable = Max_Users. Will this crash spectacularly? Or could this work?

Thanks again for all the extra pairs of eyes!

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
   
   If i = MAX_USERS Then
    MsgBox "Maximum number of connections already established, please try later"
   End If
   
   Set dbCurrent = Nothing

 

[Pat Hartman]

Try this article from the MSDN library:
Use ADO to Return a List of Users Connected to a Database