By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

How to limit concurrent users/logins

P: n/a
Short: How can I limit the number of concurrent logins to Access
(2000) DB?

Long: I seem to be having the problem discussed in previous postings
of having more than 9 or 10 concurrent logins. If I can limit the
number of concurrent logins to 8 or 9, that would satisfy our needs.

Thanks

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
As you have had no replies, I will give you 1 clue to a solution, but it's a
lot of work.

You will need a form that is opened when the db is opened that has a command
button "CloseDb" to close the db. It also needs a hidden text box called
"CloseOK". I use a modified Switchboard for this. you then set the OnUnload
of this form to
Private Sub Form_Unload(Cancel As Integer)

If CloseOK = 0 Then ' Must keep the switchboard open
DoCmd.CancelEvent
End If
End Sub

So this means that the Db can not be closed unless the "CloseOK" Text Box
is set to true. This palaver ensures that if a user has logged in, they will
also by default log off.

I am going about this a bit arse about face, but never mind.

You need a table of Users
UserID PK
UserName No duplicates
LoggedOn True/False

Set up the users who are entitled to use the DB

you then need something like this on the open of the Switchboard (or
whatever the initial form is)

Function LogOn() ' Check who is logged on

Dim MyDb As Database
Dim UserSet As Recordset
Dim SQLStg As String

On Error GoTo LogOn_Err

SQLStg = "SELECT Count(Users.UserID) AS CountOfUserID "
SQLStg = SQLStg & "FROM Users "
SQLStg = SQLStg & "GROUP BY Users.LoggedOn "
SQLStg = SQLStg & "HAVING (((Users.LoggedOn)=true));"

Set MyDb = CurrentDb
Set UserSet = MyDb.OpenRecordset(SQLStg) ' See how many using
program

With UserSet
If !CountOfUserID > 7 then
' maximum 8 users
MsgBox "You have exceeded the number of users licensed",
vbExclamation
RunCommand acCmdExit
End If
.Close
End With
Set UserSet = Nothing

SQLStg = "SELECT Users.* "
SQLStg = SQLStg & "FROM Users WHERE (Users.UserName = '" & CurrentUser()
& "');"
Set UserSet = MyDb.OpenRecordset(SQLStg)

If UserSet.BOF Then GoTo NoUser ' Not a valid
user

With UserSet
.Edit
!LoggedOn = True
.Update
.Close
End With
Set UserSet = Nothing
Exit Function

LogOn_Err:
If Err = 3021 Then ' No Current Record
UserSet.Close ' Close the query
Set UserSet = Nothing
Else
MsgBox Err.Description
End If
Exit Function

NoUser:
MsgBox "You have no permission to use this program", vbExclamation
'RunCommand acCmdExit

End Function

On pressing the "CloseDB" button you want something like

Function LogOff()

Dim MyDb As Database
Dim UserSet As Recordset
Dim SQLStg As String

On Error GoTo LogOff_Err

SQLStg = "SELECT Users.UserName, Users.LoggedOn "
SQLStg = SQLStg & "FROM Users WHERE (Users.UserName = '" & CurrentUser()
& "');"

Set MyDb = CurrentDb
Set UserSet = MyDb.OpenRecordset(SQLStg)

If UserSet.BOF Then Exit Function ' Can't find

With UserSet
.Edit
!LoggedOn = False
.Update
.Close
End With
Set UserSet = Nothing

Forms!Switchboard!CloseOK = 1 ' OK to
close database
RunCommand acCmdExit
End Function
Have fun

Phil
"mgPA" <ma*************@gmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
Short: How can I limit the number of concurrent logins to Access
(2000) DB?

Long: I seem to be having the problem discussed in previous postings
of having more than 9 or 10 concurrent logins. If I can limit the
number of concurrent logins to 8 or 9, that would satisfy our needs.

Thanks

Nov 13 '05 #2

P: n/a
Phil gave you an answer that will work well.

Another possibility is to read the ldb-file. (No need for an extra table)
Google on 'WhosOn' and you will find the code. Works beautiful.
Maybe you need to slightly adapt the function (or the way to use it) to your situation.

--
Hope this helps
Arno R
"mgPA" <ma*************@gmail.com> schreef in bericht news:11*********************@l41g2000cwc.googlegro ups.com...
Short: How can I limit the number of concurrent logins to Access
(2000) DB?

Long: I seem to be having the problem discussed in previous postings
of having more than 9 or 10 concurrent logins. If I can limit the
number of concurrent logins to 8 or 9, that would satisfy our needs.

Thanks

Nov 13 '05 #3

P: n/a
Thank you both.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.