Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Unanswered: "too many users" error on a Access 97 app w/ SQL backend

    I have an old application that's giving us problems. It's an Access 97 database front end with a SQL Server 2000 backend (running in 6.5 compatibility mode). The tool is no longer 'supported' according to some but the number of users continues to grow. It is not uncommon for the error message "too many users" to show up when a user tries to log into the database (using .mda security file--yes, .mda) during peak hours of the day.

    From what I've seen on other forums and articles, there is a 255 limit to the number of accounts that can be signed into the db. Is there any way around this limitation?

    I can play around with converting to a newer version of Access and other settings between SQL and Access, but do not have the luxury or funding to completely rewrite the application to another platform.

    Any suggestions are welcome within the boundaries stated above.

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by emijohns
    From what I've seen on other forums and articles, there is a 255 limit to the number of accounts that can be signed into the db. Is there any way around this limitation?
    I vaguely recall something with the Tool your describing as not being supported in 2000. Can you give more info on this which might possibly kick in those brain cells again?

    There was something I recall about the management tool (or something) no longer supported in 2000. I just can't recall what it was offhand. I "think" I had to add in some vba code to overcome the limitation your describing or do something with the compatibility mode but I can't be sure.

    Regardless, you may want to consider changing the compatibility mode which I "believe" was a fairly simple change.
    Last edited by pkstormy; 09-19-09 at 01:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2002
    Posts
    3
    Thanks for your response! What is the management tool you are referring to? Is that an Access thing or another app of some sort?

    I can look into the compatability mode on the SQL side. Any other ideas?

    Thanks!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Oh boy....I just can't recall what it was that was no longer supported in 2000.

    The management tool was the console to manage the databases in SQL Server.

    Now I recall!!...it had to do with ADP projects and the management console which was no longer supported for ADP (MSAccess Data Projects.) Regardless, you want to stay away from ADP's anyway.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can also try this vbs script to open the MSAccess mdb file. I can't recall if this helped or not.

    http://www.dbforums.com/6274786-post19.html

    Sorry I can't be of more help but I'll try to think about what it was years ago when I encountered the similar limitation problem you're encountering.

    Still, the script was what I created to overcome one of the user limitations (such as multiple users in the same mdb file.)

    I think it will help in your situation.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are getting the message 'too many users'
    that could be too many users using the MDE/MDB, ie an Access problem or it could be a SQL server problem, not enough clinet licences.

    if ita an Access problem then you may hit a limit with, as Paul suggests to many using the same MDB/MDE the solution to that is to deploy mor copies of the MDB/MDE, amny woudl argue that an individual copy of the front end deployed on each workstation using Pauls technique is a smart approach

    the other possibility is that you are hitting problems with too many people trying to access the same workgroup file. if all users are hitting the same workgroup file this can cause problems. so you may need to allocate users to different (copies of) the workgroup file. bear in mind Access allways uses a workgroup file.

    if its a SQL server client licence problem, chucking more money at licences may help. depends on what licencing model you chose on installation, could be per seat or per connection.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    healdem,

    Some have argued that SQL Server Express 2005 Free is better than SQL Server 2000 (for a typical 0-2 gig database). I didn't have much luck with 2005 free and went back to 2000 (but I also use 2005 live on another server). I didn't deal with the licensing scheme though for 2000. Can you shed some light on that? We had a non-taxable (Energy Savings license at my old company but I don't recall what we paid on it for 2000.) We only had 1 SQL Server server.
    I believe I also dealt with the workgroup problem once.
    I've definitely dealt with the "locked by another user" error which is why I designed the script to fix. It's saved me many hours of hunting down users who have an mde open.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its a long long time ago since I installed a SQL server for anythign except development purposes. We had a fixed number of licences and the choice of per seat or per connection, I think you could migrate from per connection to per seat once.

    the per seat IIRC allowed a specific workstation to connect as many times as required concurrently to the db. the per connection allowed anybody to connect tot he db upto the limit. the problem is that some apps had multiple connections, and IIRC depending on the style of Access development its possible to have multiple connections in the same app. don't quote me but it depends on how you retrieve the data.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    We used terminals and had a few external (dialups and other companies) connections. Our network guy told me once but I forgot how much he said it cost per year. (I think around $2000 back in 2001 but don't quote me on that.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2002
    Posts
    3
    Thanks everyone for your input! I read through the "vb script to clone an mde/mdb, launch the cloned file. No auto logoff/logout needed" and think this could be a solution.

    Our current install routine has everyone install Access 97 runtime version (the typical Access version everyone has installed is 2003), a copy of the frontend .mdb to on their workstation and a shortcut to their desktop. The shortcut points them to the runtime .mdb, a single .mda file located on a shared network folder (this is the key change we probably need to make), and the front end .mdb on their C: drive.

    If we simply split the .mda file into multiple files and point different people to different .mda files, would it work? Are there any other locking issues to having one group of people using 123.mda file to open the same SQL database as another group using xyz.mda file? It makes sense to use the cloning method, but it would require us to review the entire security and install process.

    Also, how would the vb script to clone an mde/mdb impact the user if they want to have 2 of the same app open at one time?

    Thanks!
    Last edited by emijohns; 09-21-09 at 13:39.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Where is the backend mdb file? If it's on a network drive, how are desktop/external users connecting to the network? (ie. when they open the front-end mdb file, the linked tables to the backend mdb is on the network drive?) Is it a vpn connection or do the user's use citrix and connect to a shared drive?

    All the vbs script really does is clone a file on the network drive (or UNC name), attach the user's loginID and launch the cloned file with that loginID name attached. There's nothing really MSAccess specific other than the file type is *.mdb or *.mde and it uses the default application to open that file type. I'm not sure how this will work in your environment but in both the vpn and citrix environment it worked fine for cloning an mde/mdb file (although the citrix environments often comprised of backends such as Novell, Linus, or Unix inter-mixed with a Windows front-end for each citrix session. Office was centrally installed and user's typically ran the script on a network drive against the front-end mde also located on the network drive.) (Note: for some backend servers, security of running a vbs script needed to be enabled using Internet Explorer.)

    If it's a VPN environment, you still have the connection to the network drive for the backend tables (where I'm guessing you'd want the runtime on the user's c: drive). I'd then recommend a SQL Server backend for the tables (and an ODBC connection to use linked tables to SQL Server.) This would give you the best advantages of a vpn environment as there would then be no connection to the network drive for the app.
    Last edited by pkstormy; 09-21-09 at 23:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using runtime then you don't need to deploy the front end as MDB, although it does mean you have to make certain your db is the same version as the copy of run time.

    the mda files are read only, so I'd be surprised if they had lock problems. the MDA's shouldn't cause a problem, its not the first place I'd look for resolving this issue, although it may make sense to chop the MDA to groups of say no more than 255....

    I still think the problem lies in your SQL server installation rather than your Access app, especially if you are already deploying a copy of the mdb to each workstation.

    another potential problem could be database corruption, you may need to use the microsoft repair tool to clean the database
    Last edited by healdem; 09-22-09 at 02:48.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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