Limit Number of users logged in

[Leesha]

Hi,
I have an access database that I'd like to limit the number of users at one time. What would be the best and easiest approact to this.

Thanks,
Leesha

[Wendell]

There are two options that I am aware of - the first is to create a table where you store the number of users logged in and the second is to inspect the lock file to determine the number of active users. Unfortunately either approach has some limitations. The first requires that you have either a login or an autoexec macro that increments the count in the table, and some sort of exit process that decrements the count in the table. Of course those can be bypassed using the Shift key unless you disable the use of special keys, and doing that creates some additional issues I presume you are aware of. The second involves inspecting the lock file (.ldb) when a user opens the database - there are numerous articles on the web about how to do that. You still have the issue of something running at startup, but if all works as it should the lock file will drop the user connection info when the database is closed. However not all closes happen as they should, so you may encounter issues if a workstation crashes, or the network misbehaves.

Can you tell us a bit more about your situation? For example, is your database split, and does each user have their own front-end? (If so that makes the problem more difficult.) Also, how many users do you want to allow, and is it related to performance? If you only want to allow one user, you could set the user open default to Exlcusive.

[Leesha]

>>Can you tell us a bit more about your situation? For example, is your database split, and does each user have their own front-end? (If so that makes the problem more difficult.) Also, how many users do you want to allow, and is it related to performance? If you only want to allow one user, you could set the user open default to Exlcusive.

The database is split with each user having their own front end. The number of users allowed will vary based on the office its being set up in, but lets say 5 to start. The number of users at one time is based on the number of user licenses someone purchases. If they have 20 employees but only purchase 5 licenses, then only 5 people would be able to use the database at one time.

Leesha

[HansV]

MSKB article How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000 applies to later versions of Access too (I checked it in Access 2007). You could use this to determine the number of users logged in to the backend database, and to close the frontend if the number is too high.
You have to be careful, though. You have to check which users are still logged in, and you have to check for duplicates - when I tested it, I was listed twice...

This is going to be tricky; I would hesitate to implement this - there are so many ways it can go wrong!

[Leesha]

Hi Hans,

I'll take a look at the info you sent, but if you are cautioning me re it going wrong then I'm hesitant to use it. Is this the only approach?

Thanks,
Leesha

[HansV]

Wendell pointed out two other approaches in his reply: keeping track of users in a table in the backend, and inspecting the .ldb file (for .mdb databases) or .laccdb file (for .accdb databases) of the backend. The problem with these is that a user might remain listed in this table or file if their Access crashes. In that situation, other users wouldn't be able to access the database even though the number of active users would allow it. Manual intervention would be needed to set things right - not an attractive option.

[sal21]

MSKB article How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000 applies to later versions of Access too (I checked it in Access 2007). You could use this to determine the number of users logged in to the backend database, and to close the frontend if the number is too high.
You have to be careful, though. You have to check which users are still logged in, and you have to check for duplicates - when I tested it, I was listed twice...

This is going to be tricky; I would hesitate to implement this - there are so many ways it can go wrong!

Hi friend.. ihave see this article...
And in declation i see:

Dim i, j As Long

but from MS "the Bible" all 2 variable i and j are declared Long?

I my code i declare one a one all varible is a good idea? similar:
dim i as long, j as long

only to clear my "Amletic" dubt for ever....

[HansV]

You are quite right, the declaration should be

Dim i As Long, j As Long

If you use

Dim i, j As Long

i will be a Variant and only j will be a Long.

As you see, Microsoft doesn't always follow its own rules!

[sal21]

You are quite right, the declaration should be

Dim i As Long, j As Long

If you use

Dim i, j As Long

i will be a Variant and only j will be a Long.

As you see, Microsoft doesn't always follow its own rules!