Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2007
    Posts
    10

    Angry Unanswered: Too many users? But there are only 2!

    I have an form that people use to sign in when they arrive and sign out when they leave. It sits open on one computer all day.

    Sometimes I will open the database up from another computer to make some changes or run some reports.

    My problem is that periodically when I try to run a query from the other computer it will tell me that there are "too many users" I have found that if I close and then reopen the form on the sign in computer I can then access the queries again.

    I am not sure but it seems more likely to happen when a large group of people all come in or leave together (ie lots of sign ins/outs in a short time frame) So I think my form is not closing connections for some reason.

    Does anyone have any suggestions?
    I've uploaded the frontend if that is at all useful (It's still somewhat of a work in progress so any other suggestions are welcome)

    MathLab.zip

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should not make any design changes when a user is in the database. This will lock the item you are changing until exclusive access is gained so that it an be compiled.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I did for a sign on/out type system is made it so the Access mdb opened via their logon script (I had the network guys add in opening the Access mdb in everyone's logon script since each person has a logon script that runs a series of commands when they logon.) The Access mdb was the last command in the logon script. Once the Access mdb opened, the user simply clicked one of 3 buttons: "In and Available", "In but not Available", or "Not In". Once the user clicked a button, the mdb closed!!

    I did the same thing for the logout script.

    I had another mdb (which had tables linked into it from the login/out mdb) and this mdb showed all the user's and what their status was. This way, anyone could open this mdb and see what status a user was in (and they could also change their status if they needed to.)

    This worked very well for a sign-in-out type of board.

    I eventually wrote a vb script which did the same thing when the user logged on/out but had code in the vb script to write to the Access tables (so the actual Access mdb didn't need to be opened.) This worked even better.
    Last edited by pkstormy; 04-25-08 at 02:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting, but not the best approach for many, since the poor user would have to log out and then back in again in order to change their status from say, In but not Available to In and Available.

    The VB script method is much better, have you got a sample of that? I'd like to know how to get a VBS to write to an Access database.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    No. The user would not have to log out and back in to change their status. As I mentioned, I also had another mdb where the user could see all the other user's status's (and they could also change their status if they needed to.) The vb scripts only prevented Access from having to be opened in the logon/logout scripts that run when a user logs in/out from the computer (Note though that some IT administrators do not always have a logout script when a user logs out but there is almost always a login type script of some type run for each user when they log in (which maps drives for the user, does their settings, etc..))

    There were actually many different status's the user could select from in the main interface (ie. Available, Not Available, At a Meeting, Out to Lunch, etc...) tracked in another field and it was a little more in-depth than I implied (sorry about that) but the key field was the LoginStatus field which showed whether they were actually logged into the computer or not (loggedIn/LoggedOut).

    Here's an example of the vb script which was run in the login/logout script for the user (but again, it was a little more in-depth in the actual mdb interface - this script was used to update the key field in the table (originally updating an Access table but converted to SQL Server) which again, simply showed if the user had logged into their computer or not):

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName
    'varUserdomain = WSNet.UserDomain
    'varNBName = WSNet.ComputerName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    MyConn.open = "DRIVER={SQL Server};"_
    & "SERVER=SQLSERVER;"_
    & "DATABASE=PKTime;"_
    & "OPTION=35;"

    sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'"

    'ex: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & "PaulK" & "'"
    'ex for logout: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedOut" & "' WHERE LoginID ='" & varUserName & "'"
    'ex: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedOut" & "' WHERE LoginID ='" & "PaulK" & "'"

    MyConn.Execute sql_query
    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing

    I'll see if I can find the vb script which updated the Access table (which just had different settings for MyConn.Open.)
    Last edited by pkstormy; 04-25-08 at 14:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah, my bad. Me no read good

    Thanks for the vbs script Very much appreciate that. It would be lovely to see one that writes to an Access file too.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To Update an Access table via a vb script (and utilize a variable to get the current logged on user):

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName
    'varUserdomain = WSNet.UserDomain
    'varNBName = WSNet.ComputerName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AccessExamples\CustomersXPSample.mdb"

    Set RS = MyConn.Execute("UPDATE Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'")

    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing

    Note for example above - Users is a table name in the CustomersXPSample.mdb with a LoginID field and a LoginStatus field. There is a record in the Users table with LoginID = the current logged user. The above example sets the LoginStatus field for the current logged user to 'LoggedIn'.
    Last edited by pkstormy; 04-27-08 at 16:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Awesome! Thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're welcome StarTrekker.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Posts
    10
    That's an interesting idea unfortunatly it won't work in my situation.
    I am running a math tutoring center at a high school, so the students are not loging into computers. The database is to track usage and allow me an easy way to tell teachers if/when students are here (I used to have students sign in on paper, looking back through a long list of name to tell a teacher if a student came in last week was a horrible pain) Eventually I'd like to give teachers remote access to the database from their room computers but if I'm going to have "too many user errors" with just 2 computers thats not going to be possible.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no fundamental reason why an Access app cannot support upto 255 concurrent users on any one workgroup file, I'm aware of Access applications which support thousands (but not using JET, using a server backend, but those are also limited to no more than 255 users per workgroup)

    the problem as others have already said is that you are trying to change the applications, and that locks out other users.

    the solution is to leave your live application alone, let people use it as required, and make any changes to a copy of the live application. it has an added bonus that if you make any mistakes your changes don't screw up the live data. this is one of the main reasons in my books not to have a shared access application (if you allow your users to have access to MDB rather than MDE files as the front end).

    if you need to you can connect to live data in your development application, but that means you cannot change the data design until everyone else is out of the system.

    of course you could "just" examine the lockfile, there is some code that originally I came across in the Sybex developer book, which can tell you who is logged onto an access app. whether its of any use to you I don't know.

  12. #12
    Join Date
    Nov 2007
    Posts
    10
    The problem does not occur when I make changes to the application. I always close the other computer save as an .mde and reopen it. It occurs when I am trying to change the data in the database (Or even just open up a query to view some data). For example some student accidently put in the wrong teacher so I try to open up that record to correct it.
    Last edited by MathTutor; 04-28-08 at 15:31.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that shouldn't cause a problem....
    you should be able to modify data concurrently.. mind you I'm not sure what will happen if you open the table for data editing, rather than via a form. that may well cause lockouts.

    what do you use for records locking? it possible if you are using table locks that could caused a problem.. record locks would be preferable

    when you open up the record how do you edit it, via a form, a datasheet?

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry MathTutor - we seemed to have gotten offtrack to your problem.

    I took a look at your mdb and the one thing I could see which could possibly conflict with you editing a record and the error you're getting is the InputBoxTextMask module which I'm not familiar with but looks like it's based on an API accessing the windows timer.

    If you were to backup the mdb and then remove this module and references to it (which I understand you need to hide the password - but just to test), I can't help but wonder if it's somehow conflicting with your ability to edit records or "too many users" error. This module would be my best guess on why you're getting this type of error especially since it seems related to a windows timer function.

    If this ends up being the problem (which I suspect is the problem), I'd consider using a different module (or method) for hiding a password or change the inputmask on that field in the table to 'Password' (which will then not show the characters entered on the form.)

    Other than that, I wonder what options are set for: Tools -> Options, Advanced tab (Default Open Mode and Default Record Locking). It should be set to "Shared" and "No Locks" and the "Open Databases using record-level locking" should be checked.
    Last edited by pkstormy; 04-28-08 at 20:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Nov 2007
    Posts
    10
    healdem -
    when I edit the data I usually just open up a saved query I have that shows all the new records from today and edit it from the datasheet.

    pkstormy -
    my options were already set the way you recommended. I'm going to try disabling the input masking routine and see what happens (I thought I found the code from here but I can't find it again) It may take awhile for me to know for sure since i'm still not sure how to cause the error to happen.
    Last edited by MathTutor; 04-29-08 at 11:57.

Posting Permissions

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