Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    6

    Unanswered: Access Slow loading multiuser

    I have FE/BE access (97) application. both mdb multiple users (4-5) use BE to read/write data. First user program opens fine. User two signs on and program is very slow to open

    After spending many hours looking into this and then testing, it is my opinion that the problem revolves around the ldb file on the server.

    It is true that there is only one .ldb that shows on the server. However, if
    Control Panel>Administrative Tools>Computer Management> System Tools> Shared Folders>Open Folders> #Locks is looked at the following is seen.

    One user Locks = 0
    2nd user and up #Locks is all over the place but numbers like 92, 68, 120 are seen.

    Has anyone looked into this? Any suggestions besides changing BE to SQLServer?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    The ldb file is just a lock file that access generates to say that at least one person is using the access BE and to control how the records are locked. It is not the reason why your program is slow in a Multi-user environment. Access is quite simply just not made to be a BE server. That's why there's software out there like Oracle, SQL Server, etc. These types of software are designed to host data for multiple users.

    So the short answer is, there's some things you can do to get minimal increases in speed. But until you switch to a real BE server, you're not going to see any real change.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    6
    Nick - I truly appreciate you taking the time to reply; however, like in most of the form stuff I read the answer is kind of cookie-cutter and does not address the problem. That is why I have not joined a form until now.

    First off the problem is loading slow not running slow. Acutally, the program is not slow once it is loaded. By the way, the program is used by hundreds of people in over 40 different locations and has been for over 7 years. This load situation appears to be somewhat recent and from posts I've read maybe happening to others.

    Second - on the server the ldb is not just a locking file - it keeps track of all users linked to the server and applies what appears to apply somesort of locks. That is why I noted the Control Panel>Administrative Tools>Computer Management> System Tools> Shared Folders>Open Folders> #Lock

    Third I said: "Any suggestions besides changing BE to SQLServer?" Which should indicate that I have considered other severs and rejected them. There are specific reasons for using access - cost, system incompatilibity, system resourses.

    Fourth there are actually quite a few things you can do to speed up an Access database particularily if you followed Microsoft's suggestiona in the first place.

    I'm sorry this reply is so negative but I would truly like to see forms which directly addressed the problems asked. I also truly feel there is a problem out there related to mine which needs to be addressed.

    Lastly, I did notice you referenced links on the subject, which I agree took some work and were in the right direction. I had already checked these before coming here.
    Last edited by vidny; 05-02-09 at 22:41.

  4. #4
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    What's changed recently that might be related to the slowdown? A new user with a creative way to use the front-end or back-end? Someone with a troublesome install of access97 or another version of access that wants to take over? Software updates to the workstations? Does everyone have the same libraries? Have the permissions changed on the folders where the front-end and back-end are stored? If none of those apply, here are a couple more cookie-cutter answers:

    Do you have multiple users accessing the same front-end file? I've noticed with a97 and a2000 that sharing a front end can slow loading, even if you just open a switchboard and use unbound forms. XP and 2003 don't seem as bad - I haven't played with 2007, yet.

    Can you give each user a local copy of the front end? IF it's a problem woith just one workstation, it won't affect anyLoading from a network share will always take longer than loading from a local hard drive. If the IT trolls will let you, add a folder to the top level of the local hard drive and put the front end there. You may also have to redo your security model if you're distributing the front-end to each user. There are topics in this forum to cover distributing front-ends and keeping them up-to-date.

    Good luck.

  5. #5
    Join Date
    May 2009
    Posts
    6
    trowe,
    Thank you for taking the time to reply.
    No recent changes/updates other than what may have been done automatically by Microsoft on the workstations and/or server. All workstations run XP, the severs vary.

    Good point about multiple versions of access for others; however, our start up routine points only to A97 which is in its' own folder on each workstation.

    FE's are all loaded in top level under root. In most case, server is also.

    We do not use any security except that the startup routine references System.mdw (which I know nothing about and have been have a hard time finding out about it and how it works). It has been running this way for years.

    I really believe it has something to do with the .ldb file on the server. First workstation loads fine 2nd and so recently started loading very slowly.
    That Control Panel>Administrative Tools>Computer Management> System Tools> Shared Folders>Open Folders> #Lock I noted goes nuts when 2nd user logs on. DO YOU KNOW ANYTHING ABOUT THIS - I can't find anything anywhere.

    P.S. - I even tried a version from 3 years ago and the same thing is happening.

    Thankyou again



    Each work station has it's own F/E (probably should have mentioned this becasue it seems to come up a lot).

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Vidny,

    Just a thought, but do you maintain a persistent connection between the BE and FE?
    Me.Geek = True

  7. #7
    Join Date
    May 2009
    Posts
    6
    Thanks again - we do that

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can also try this little cloning method out. It clones the "source.mdb" file with the user's loginID and then opens that mdb (and ldb) file separately for that user. If you use *.mde files, they will preform even faster. You can also have a hundred or so users without ever getting a "locked by another user" error again. Since all users are running their own mdb file, there's no conflicting code involved in a single application.

    If perhaps your user A has created a bunch of temp table data (by running morning reports), it's possible those tables are large enough to impact performance for user B. The cloning method takes care of that since each user is getting a "fresh" copy of the source.mdb file every time they get into it.

    All users have to do is open the mdb file by running a vbs script on their desktop instead of an MSAccess shortcut. The script takes care of cloning the database and opening up the cloned file (with the user's name attached). You can then see who is in the mdb by looking at the ldb files in that folder (along with when they last opened it (ie. date of mdb file). I believe there are about 6 lines of actual code in the script which execute (the rest are comments). It's pretty simple to follow the instructions and easy to do (you change 1 or 2 lines).

    Here's the link if you're interested: http://www.dbforums.com/6274786-post19.html

    Also keep in mind that moving the backend to SQL Server may not solve your problem! You'll can still have locked mdb files regardless if the backend is SQL Server or MSAccess. The locking part is done on the mdb file itself (ie. someone leaves it open for 4 hours without any activitiy on an update field, exits unexepectdly, or code crashes. (Unless all your MSAccess forms were designed to be "unbound" to the recordset. Then disregard.)
    Last edited by pkstormy; 05-04-09 at 23:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    May 2006
    Posts
    386
    Dear all, I too am facing the exact problem, suddenly all the front ends on our network have been extremely slow. I followed all of the suggestions in this thread including the last one which suggested opening the front end by cloning it to the backend by the vbs code, one one computer the vbs code did open the front end (but made no difference in the speed at all) but on the 2nd computer i followed the exact procedure but somehow it is not working and it gives me a message saying "The command line you used to start Microsoft Access contains un option that Microsoft Access Doesn't recognize. Any help and advice would be greatly appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    May 2009
    Posts
    6
    Emal,
    Is your issue running slow or loading slow?

    In my case the issue was loading slow, changing some of the queries that were running during the load process helped. This does not explain why it started happening after running fine for many years.

    Either way please post whether it is loading slow or running slow, I would like to communicate with you on the subject.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the app has been running fine for a few years and its suddenyl goen wrong my suspisicions its either a hardware or netwrok issue or its a patch / system update issue.

    i would agree with the idea of individual front ends deployed on the local machine, especially if that is deployed as a MDE

    I'd want to make sure the db was as tight as possible (so I'd want to make certain it was compacted & repaired if deployed as a MDB

    I'd want to make certaint here were no compilation errors (open the apps code window and iirc do a compile.

    when you say oits slow to load.. do you mean to get to the first splash screen or to the first form. if its the first flash screen or switchboard then I suspect it may be an system issue, if its tot he first form then I'd want to check to see if there is any code running before anythign is seen by the user. quite often stuff (running queries, hiusekeeping and so on) is done in background and this could be the issue if the app runs fine.

    of course it could just be Microsoft playing silly beggars and deploying a new version of JET which snafu's A97 in a desperate attempt to encourage you to buy new software... I'd want to know what patches have been deployed by or on behalf of network admins

    is there any user written code running between start up and first sign of life from the app. I'd want to check for any autoexec macros and or any macros/functions called from a keyboard shortcut.

    I'd want to make sure that the back end is comapcted and repaired also.. its possible you may have an index which is corrupted or badly blown.

    you don't say if this slowness has come on suddenly or hjas crept up on you. if is a sudden appearance then its system hardware or software (unless say a new front end was deployed), if its crept up on you then I'd suspect its corruption or problems with the back end.

    however I don't think anyone will be able to diagnose a specific problem by remote its going tobe more trial and error.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2006
    Posts
    386
    Vidny,
    Thank you for responding to my post. in fact, until 7 days ago all 4 computers in the office were using one FE which was located on my pc and other 3 pcs were accessing it through the network. one of my colleagues started raising some invoices, she she clicked on the invoice button the DB gave this error Microsoft Access has encountered a problem and needs to close" so I clicked on "Don't Send" right after that for some mysterious reason the front end was deleted from the entire network and despite many attempts I could not find it. So, I created a nother front end and this time put the FE on each PC separately so everyone has their own FE and I was hoping that this will make the speed even faster than before. Since then, the DB has become dramatically slow in loading and running (mainly running)especially when I click on one of the Comoboxes to navigate from one client to another to raise an invoice against a record that is already in the table/query, it used to tick no more than 2 to 3 seconds, however now it takes 40 to 50 seconds and sometimes even longer.

    I followed the instructions suggested by PKStormy (above) and written by and the DB now cloned to the small vbs application and it opens it from desktop rather than dirctly from MS Access BUT STILL no change in the speed on either of the PCs.

    I also followed the instructions suggested by Microsoft which suggested that all the Subdatasheet Name from tables should be changed from Auto to None to help the DB performance, So i changed the Subdatasheet Name for all tables to None by viewing the tables in Design View>View>Properties>Subdatasheet Name>None but still no change in the DBs speed performance.

    Furthermore, since this problem has started, we are getting more of this error when we click on a button the raise an invoice "Microsoft Access has encountered a problem and needs to close". By the way, in terms of Compacting and Repaire, i have set all the FE to Compact and Repaire as soon as the DB is closed.

    Any idea and help would be greatly appreciated.
    Last edited by Emal; 08-01-09 at 07:09.
    Emi-UK
    Love begets Love, Help Begets Help

  13. #13
    Join Date
    May 2009
    Posts
    6
    Emal,

    Well, first things first. Getting "Microsoft Access has encountered a problem and needs to close" generally means there is a bug in the code. And since it is apparently occurring on multiple machines it is even more likely. This has to be fixed, it will probably just get worse as well as the possibility of loosing data exists.

    From my experience the best thing to do is start over from the last know good copy that does not give the "Microsoft Access has encountered a problem and needs to close" error. In your case, you said you created a new FE so perhaps this in not too great a chore.

    Make sure that you “debug” the code during the development process. Open any module then a Debug option should occur on the top bar. Use “Compile and Save All Modules”

    While what PKStormy said sounds interesting, I do not believe it applies to your case.

    There are more things that can be done but let me know if the above will work for you.

    Vidny

Posting Permissions

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