Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Limit # Database Users (2002)

    Hi,

    I have a database that resides on 15 workstations. Each workstation is linked back to the backend tables that are stored on the server. I'd like to limit the number of users at one time. For example, only 7 users could be active at one time and if an 8th tries to log on they'd be blocked. Is this possible?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit # Database Users (2002)

    The following function (copied into a module) will return the number of users in the database:

    Public Function CountUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer

    On Error GoTo ExitHandler

    Set cn = CurrentProject.Connection

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    Do While Not rs.EOF
    i = i + 1
    rs.MoveNext
    Loop

    ExitHandler:
    CountUsers = i

    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    End Function

    (Adapted from How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2002 or in Access 2003)

    You can use this function in the On Load event of the startup form of your database, or in an AutoExec macro. For example:

    Private Sub Form_Load()
    If CountUsers > 7 Then
    MsgBox "Too many users! Please try again later.", vbExclamation
    Application.Quit
    End If
    End Sub

    Note: this is not watertight. If users can bypass the startup options by holding down Shift while opening the database, the code will not run.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit # Database Users (2002)

    Thanks Hans!!

    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit # Database Users (2002)

    Just out of curiosity, what is your reason for wanting to limit the number of people using the database? The reason I ask is that often the question is asked in another way, such as "How can I get more people to use my database and maintain acceptable performance?"
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit # Database Users (2002)

    Hi Wendell,

    The database I've been working on for our agency is most likely going to be bought by other "sister" agencies. The woman I work for is trying to figure out how to price it and we were thinking of a licensing approach. 2 of the agencies have multiple offices and users. Other offices have one office and few users. To price it at one price across the board doesn't seem fair to the smaller operations and seems more than fair to the larger ones.

    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit # Database Users (2002)

    Leesha,

    Please keep in mind that unless you either create an .mde file or apply user-level protection to your database, experienced users can easily bypass your protection, for example by importing all database objects into a new database and modifying the code.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit # Database Users (2002)

    Hi Hans,

    Yes I will keep this in mind. I've never had anyone want to purchase what I've done and am amazed at all there is to consider. I've used .mde versions in the past but the tables were all split and in this database there are temporary tables that aren't split off to the backend database and I'm not sure how that will affect it. I've also read that there can be issues/bugs with mde files and I certainly don't want that to happen. So much to consider!

    Thanks,
    Leesha

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limit # Database Users (2002)

    I've never had any problems using temporary tables in a FE MDE database. I am also unaware of any "issues/bugs" with using MDE databases; again my experience has been good.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit # Database Users (2002)

    The main issue with .mde databases in Access 2002 is that you can only create them in Access 2002 format. Many developers (perhaps most) work with Access 2000 format databases in Access 2002, but it is not possible to create an Access 2000 format .mde.

    There was a thread a few months ago about .mde databases not being as secure as we thought.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limit # Database Users (2002)

    True, there ares holes in the "security" of an mde, but I think you can properly deal with them. Requires turning-off all the database properties that allow the user access to the database window and design mode. As with all these possible steps, it just depends on what is most important, and then you do what you can do within those constraints. In most cases, the aim is to discourage the nosey user; and this can be done quite effectively; but often a developer will know the way around them.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit # Database Users (2002)

    You've received lots of good advice already so I'll take a bit different tack. Another approach you might consider is to implement Access User Security (you would probably want to anyhow), and then build the Security Files for each using agency yourself. Then remove the ability to administer security from the front-end mde so you are the only one who can add or remove users. That assumes that you are in physical contact with the individual agencies. If you aren't, then you would probably need to add code to allow the agency to add and remove users, but you could set a maximum number of users that would be allowed in the security file.

    Another issue to consider is how the database is deployed. It's always advisable to put the front-end (I would also recommend an MDE) on the user workstation, and have the back-end on a server somewhere. Doing it the first time is challenge enough, but when you are asked to make some design changes for a specific user, it can get to be ugly pretty fast. You might want to consider a tool for doing that if you get more than one or two other agencies to adopt your database.
    Wendell

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit # Database Users (2002)

    Hi!

    My mind is swimming. When I started developing this for our offices in October it was not intent to sell it. Presently it resides in 5 separate offices and it contains front end and back end tables. My biggest issue to date has been when the backend tables need table changes, having to them do it for all 5 offices. I've done alot of experimenting with code but there doesn't seem to be an easy way to do it other than manually. The other issue I run into is when new backend tables are added, its a pain to split them off and then link them etc. for 5 offices. The program is such that its a work in progress and will never be done. It's gotten so large that I know develop small pieces at a time and import them into the main database so as to keep track of queries and reports etc. My staff have no problems using it and require little of my time to maintain it (just to keep building it) which encourages me that it is in fact stable enough to sell. To maintain it or make it user friendly when updates/revisions are done is the thing making me hesitate. Add to it all the issues of pricing, security etc. Jeeze!

    With that said, I've never worked with the agency specific security files that you mentioned. Where would I look for info on this?

    Thanks,
    Leesha

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit # Database Users (2002)

    I didn't mean to scare you too much - but there are pitfalls out there and it's better to know about them in advance. Have you already implemented Access User Security? If you have you probably already know the basics. If you haven't, we have a short User Security Tutorial on our website that might be a useful starting point. Jack MacDonald has a longer more detailed User Security paper that I highly recommend. Finally, if you find you need to manipulate the security model in VBA, the Access Developers Handbook has a couple of excellent chapters on doing things like adding users, changing group assignments and the like.

    On the deployment issue, there are a couple of products that can help as you need to deploy new front-ends (and perhaps security files - we usually put the security file on the workstation along with the front-end). FMS has a product called Total Access Startup that does lots of useful things, and we have something called DBLauncher that takes a somewhat different approach but achieves the same result. In addition, if you get into trying to support a number of different agencies with slightly different designs or customizations, you might want to consider a source control system such as Visual SourceSafe. Hope this provides some help is setting a strategy.
    Wendell

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit # Database Users (2002)

    Scared doesn't even describe it! I'm ready to run for the hills, but then again that stubborn persistent side of me won't let that happen. Believe it or not I bought the developers guide you mentioned awhile back before I found this forum, but it was way over my head and I've never returned to it. I've learned so much from all of the help on this forum that I might even be able to digest some of it. I will look into all this info and you can be sure I will be back with questions.

    Speaking of questions, does the site take donations? If this thing does sell, I'd like to make a donation to the site to help in keeping it running and to as a way of saying thank you for all the help that its been to me.

    Leesha

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit # Database Users (2002)

    No, we don't take donations, but thanks, we appreciate the idea!

    You can click on the Google ads in the lower left corner of most Lounge pages; the Lounge earns a small amount of money from those clicks.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •