none
Slow Database Issue ... please help!! RRS feed

  • Question

  • I have been doing Access development for 20 years and this is the most frustrating problem yet. I have a new database I have created from scratch. I have the Access backend on the server and Access frontends are distributed to users desktops. When I test it, it's lightning fast. As soon as only one other person opens the database, even if they are just on the main switchboard and not opening any other forms, it immediately slows down to about half speed! We have tried this with various people on various machines and it's the same result. There is a noticeable delay moving from record to record, clicking on tabs, etc. I admit the main form is somewhat complex ... lots of tabs with subforms. This is where the slowness mostly occurs. However, I have done just about everything the experts have recommended as far as improving performance and it has helped a little, but not enough. Still a very noticeable delay. I have done the following:

    • Only loading one record at a time instead of all records (only loading the record the user wants to look at).
    • Create a permanent connection to backend by having main switchboard based on small table with one record.
    • Only loading subforms or combobox sources if the user clicks on the tab with that subform or combobox.
    • Changed primary keys from Descending to Ascending (only had this on one table and a few related child tables).
    • Replacing all domain aggregate functions (DCount / DLookup) to recordset counts or "ELookup" function in VBA and queries.
    • Do more functions up front only once when the form loads, rather than on Form_Current.
    • Add primary keys to all tables (I only had 3 tables without primary keys, but all have unique indexes)
    • Replace Nz functions with IIf in queries
    • Analyze Group By queries and make sure they are efficient
    • Adding Option Explicit to all modules
    • Any forms based on tables ... use queries instead if I don’t need all fields.
    • Uncheck “Use Windows-Themed Controls in Forms” on database options.
    • Turning on unicode compression for all text and memo fields (table property).

    I have always had the basic performance enhancements in place ... turning off subdatasheets, turning off autocorrect, etc.

    The other odd thing is ... if I test it only on my machine, but open three separate occurrences (three separate front-end copies on my machine, pretending to be three different users), it is lightning fast on all three, even when all instances are in the same large form and doing various things on the tabs.

    I am puzzled and frustrated and not sure what to do at this point. I thought about possibly moving the backend to SQL Server, but some say that may not improve the speed. Any options on that? Could there be a server or network issue? The IT people said the bandwidth is 100MB, although one guy said in this day and age, it should really be 1G. Don't know if that is an issue or not.

    Any help or advice is appreciated! Thanks.
    Wednesday, June 15, 2016 2:33 PM

Answers

  • Figured out the true culprit!  I have four unbound boxes on that tab that have their control sources all set to complex DLookup functions.  I forgot I had these as DLookups, which from what I read, can really slow the system down!  I changed them everywhere but here.  If I set all four to null, it is lightning fast with two users.  Can't believe it.  They refresh each time the user navigates to a different record, as the data in those boxes changes, so that's obviously what is slowing it down.  I guess it has a harder time pulling this data when someone else has the table open??  I tried changing them all to ELookups (Allen Browne's function), but it's still slow.  So, I will look at it again this afternoon.  Thinking I may go another direction and put these boxes in a subform that links to the main form.  All four are pulling from the same table.  That may be another problem.  Maybe it's too much to be pulling from that table all at once via DLookups?  Anyway, glad I found it and now I need to change it.  Any suggestions or thoughts welcome.
    • Proposed as answer by Paul23 Tuesday, June 28, 2016 9:51 PM
    • Marked as answer by TraciMarie Wednesday, June 29, 2016 2:02 PM
    Tuesday, June 28, 2016 4:05 PM
  • Well, what I ended up doing is I moved those four boxes into a subform and then put that subform on the main form and is is linked by the main ID.  I originally just based that subform on a query (which was based on another query), but that still seemed somewhat slow.  So, now I have it where I do a make-table query (from those queries to get the data) and it created temporary / local table.  It only runs the make-table when the user clicks on the tab the subform is on (or when they go to another main record and the tab is still active).  You would think that would be slow, having to run the make-table all the time, but it is lightning fast!  So for now, that's what we're going with.  I did multi-user testing yesterday with four users and everyone was fast!  I was very happy with the performance ... finally!

    I am thinking the reason it is slow with doing queries directly on the table is because that table is somewhat large ... over 80k records and I have some expressions in the queries.  It only needs to analyze about 100 records (those related to the main table) to get what it needs for the four boxes.  So, when I do the make-tables, it only pulls those 100 records down and then does the expressions / functions on that small local table.

    But I'm open to suggestions on doing it cleaner / better.

    Thanks to everyone for their help!

    • Marked as answer by TraciMarie Friday, July 1, 2016 1:50 PM
    Friday, July 1, 2016 1:42 PM

All replies

  • Let me guess, you have switched to Windows 10?

    Best regards, George

    Wednesday, June 15, 2016 2:36 PM
  • Do you have the backend share mapped to a drive letter?

    Matthias Kläy, Kläy Computing AG

    Wednesday, June 15, 2016 3:10 PM
  • George ... Nope.  Everyone here has Windows 7.  Some have Office 2010 (like me) and some 2013 (my boss).  Does not seem to make a difference.  We just tried testing it on a different server.  Same result.  We just tried doing the multiple app copies test on my boss's machine and having two open at the same time.  Both copies are lightning fast.  But as soon as a DIFFERENT user gets on in another office, it slows to half speed ... even if the other person only has the main menu open and has not opened any of the other forms.  I do have the main menu based on a small table on the backend, to keep the permanent connection.  And that table is only used for this purpose.  It makes no sense at all!!

    Someone mentioned about the users here possibly being on different networks / routers.  I passed the question to IT and their answer was:  "I believe there are 3 user subnets in [your location] and servers are on a different subnet from those, can’t speak much more to it though."

    Could this be the problem??  If so, is there anyway IT can change this?


    • Edited by TraciMarie Wednesday, June 15, 2016 3:18 PM
    Wednesday, June 15, 2016 3:17 PM
  • Matthias ... No.  Tables are mapped directly to \\ServerName\Share\Folder.  I assume having the tables links mapped directly is faster, correct?
    Wednesday, June 15, 2016 3:21 PM
    • I don't think different subnets can cause this but let's make a test:
    1. Let someone on the same subnet as you access the database at the same time as you. Does it work ok?
    2. If so, let someone from a different subnet access the database at the same time as you on the same computer as in 1 above.

    Consult the IT department to make sure case #1 and #2 above are properly tested.


    Best regards, George

    Wednesday, June 15, 2016 3:27 PM
  • I may have missed it, but are you creating a persistent connection between the FE and BE at startup?
    Everyone is connected by a wired LAN?
    Any difference in performance between accdb and accde?
    Your code compiles without errors?
    Is everyone running the same version of Access?  Everyone have the same updates? (this includes the developer - you are developing using the same version as your users?)

    What type of server is being used for the BE?  I've been reading about people having certain issues on server 2012.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Wednesday, June 15, 2016 3:35 PM
  • George ... I'm going to get with IT on this issue and hopefully we can test that.  Thanks.

    Daniel ...
    Yes, I created a persistent connection between FE and BE by having the main menu based on a small table with one record in it.  I assume that is OK.  The main menu always stays open until the application is closed.  I have the recordset type as Dynaset but Allow Edits Additions Deletions set to no.  Is this OK, or should I have it as Snapshot?

    Yes, everyone is connected by a wired LAN (the users who have been testing the new db).  We will have a few people connect via VPN in the future, but they aren't heavy users.

    I have never tried the accde.  Maybe I can try that and see.  I assume I only need to convert the FE to an accde?

    Yes, code complies without errors.

    Some have Office 2010 (me) and some Office 2013, but we all have Win 7.  I developed it on Office 2010.  I assume we all have the same updates.  I believe they are pushed from Central IT to our machines, but I will ask.

    I assume it's a Windows File Server, but I will ask that too and will find out what version.  I assume you mean Windows Server 2012?  If I find out that's what we have, what can be done to fix it?  Patches or upgrades maybe?

    Would migrating BE to SQL Server help?  I don't want to do it unless you think it would definitely fix the problem.  Kind of a pain.


    Wednesday, June 15, 2016 3:59 PM
  • You seem to be on top of things in a general sense.

    Regarding SQL Server, no, it will not improve performance.  Normally, there is even a little (or more) drop in performance, but you gain in many other areas (stability, security, ...).

    I'm wondering about network saturation.

    Your users are running locally on individual PCs, or session off of an application server?  If it is the latter, there could be a resource issue.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, June 15, 2016 4:57 PM
  • Using \\ServerName\Share\Folder may cause a performance problem, so try out what happens if you map this to a drive letter.

    Matthias Kläy, Kläy Compoting AG

    Wednesday, June 15, 2016 6:29 PM
  • Daniel ... I confirmed users are running locally on individual PCs and all are up to date with latest service packs, etc.  Don't know what server version we have yet.

    Matthias ... I was told that having the tables linked via the drive mapping is what would slow it down and having it directly linked via URL is faster ... ??

    Everyone ... Thanks for your feedback!  IT said they have tried something on the server and are going to reboot it tonight to hopefully help performance.  I guess they changed something.  I will also try doing the accde file.  I will test again Friday and keep you posted.  Thanks.
    Wednesday, June 15, 2016 7:30 PM
  • Update on this. The server changes IT made did not work. We’ve done a lot of testing Friday and today with the database, trying to pinpoint the problem. The network slowness is especially noticeable when moving from record to record. So, we decided to do some speed testing based on that. Here is the testing we have done and some interesting results:

    Test 1
    1) User 1 opens database and opens record on main form. User 1 navigates to another record. Time to navigate between records: 1-2 seconds.
    2) While User 1 is still in database, User 2 opens database and opens record on main form. User 2 navigates to another record. Time to navigate between records: 1-2 seconds.
    3) While both users are in database, User 1 then navigates to a different record. Time to navigate between records: 6-8 seconds.
    4) User 2 continues to have 1-2 second navigation time, while User 1 is slow.
    5) User 2 then exits the database. User 1 navigation time is still 6-8 seconds. User 1 exits the system and gets back in. Navigation speed for User 1 improves to 1-2 seconds.

    Test 2
    Repeat steps 1-4 above, but step 5 is ... User 1 exits the system and immediately gets back in. Navigation speed for User 1 improves to 1-2 seconds. Navigation speed for User 2 immediately slows to 6-8 seconds.

    Test 3
    We repeated Test 1 above, except in step 2 … User 2 opens database, but stays on main menu (which is based on a small table) and does not open any forms or reports. Same exact results.

    Test 4
    We repeated Test 1 one more time, except in step 2 … User 2 opens the backend file manually on the server, but does not open any tables. Same exact results.

    So ... the users don’t even have to be on the same form or even asking for data, as Tests 3 and 4 show. Just opening the backend file will cause the first user to slow immediately.

    We also these tests with three users. Whoever opens the database last is fast the first two users immediately slow down. Any user that closes and reopens (therefore becoming the last user) will be fast and slow everyone else down. And when the very first user get slowed down, even if all other users get out of the system, it is still slow for that first user. That implies it’s still holding or locking something, even though those users have exited. Only after that first user closes and re-opens will it become fast again.

    I also tried doing these tests logged on as me, but in two different locations (my desk and a conference room), just to see if the Windows login had anything to do with it. The results were the same. The machine that opened the database first immediately slowed down as soon as the second machine opened the database.

    I also did a multi-instance test on my machine, where I opened two separate frontend files on my machine, connecting to the same backend. Interestingly, both instances are very fast no matter what I do. I confirmed it was running two separate instances of Access thru the task manager. So, it seems it’s slower with different machine locations. The login makes no difference.

    I have never seen behavior like this. Any ideas as to what is causing this?? Is this a server or network setting? Someone here mentioned it may be a 32-bit / 64-bit issue (Office is 32-bit but Server is 64-bit). Any thoughts on that?

    • Edited by TraciMarie Monday, June 20, 2016 5:36 PM
    Monday, June 20, 2016 5:31 PM
  • You've certainly been thru the ringer on this.

    The 1 point that is key to me which is that it is only slow when the 2nd (or greater) user logs on.  I have never seen that.  My experience has always been that it is slow for all.  Never fast for 1 but then slow for multiple.

    It is this point that leads me to wonder about the server itself (or possibly its network segment with a chattering router port).  I would try the back end on a different server and ideally on a different network segment.

    As to Access itself; I myself have never fiddled with the record locking and have always been satisfied with the defaults.  But I would imagine one could screw things up if the record locking is set to X in the back end but to Y in the front end....others with more experience on this aspect probably have better advice though.

    Monday, June 20, 2016 9:04 PM
  • Edward Z ... yes I have!  Thanks for the feedback.  We did try testing on a different sever, in this location.  Same results.<o:p></o:p>

    I always leave the locking as is, which is no locks, but record-level locking turned on.  That is the default setting for the Access client settings so it should be that way on all machines.  Although, thinking about changing record lock property to "edited record" on main form and see what that does - not necessarily for performance, but so users don't attempt to edit simultaneously.  If machines have "Open databases by using record-level locking" checked, I am hoping it will only lock that one record.  I know MS is notorious for having "edited record" really be page-level locking, which I don't want.

    Back to the performance issue ... I spoke with someone in IT this morning who is in charge of server setups.  But she is also in close contact with network people.  Go figure.  We discussed locking management, users possibly on multiple subnets, and bandwidth being throttled down in some places on purpose.  We are also going to try to test it on a server at another location.  Hopefully we'll get to the bottom of it soon.

    I'll keep you posted.<o:p></o:p>

    Tuesday, June 21, 2016 4:09 PM
  • Do all users have full delete rights to the back end folder?

    I suppose the issue of some having a network printer that does not exist can also come into play.

    I would check the back end folder rights – all users need full rights – including delete rights. It’s possible the back end folder is inheriting rights from the first user that creates the locking file. So, for testing, I would ensure that all users have full rights to the back end folder – including delete file rights.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    Tuesday, June 21, 2016 5:53 PM
  • Everyone using the database has full rights to the folder with the backend.  I double-checked before I started testing.  I also monitored the folder during testing and everyone is able to create and delete locking files on there and they can edit the data.

    Network printer ... that is an interesting one.  I don't have a non-existent printer on my machine - I just checked.  I'm not sure if others have that issue.  This is something to check.  But when I did the testing as myself on two different machines, the issue still occurred.  I was logged in as myself on my own machine and then also a conference room machine.  The conference room machines are setup just like the individual user laptops, except there are no printers installed or connected, period.

    Tuesday, June 21, 2016 6:21 PM
  • Did you try if there is a difference if you use a mapped drive letter instead of the share for the backend DB?

    Matthias Kläy, Kläy Computing AG

    Tuesday, June 21, 2016 6:31 PM
  • Yes we did - no difference.  Same results.
    Tuesday, June 21, 2016 6:48 PM
  • I just tried unchecking the record-level locking option on the two machines I was testing it on (client setting options ... unchecked "Open databases by using record-level locking"), and then restarting Access, thereby making it page-level locking to see if that would improve performance.  It does not.  Same results.
    Tuesday, June 21, 2016 6:52 PM
  • Other things I would try...creating a new back end on your server by creating an empty database and importing all the tables from your old one.  And do the same with your front end as well.  

    You could also try linking your front end to back end tables using the numeric ip address of your server instead of its name, e.g. \\123.45.6.7\myfolder\mydb.mdb instead of \\myserver\myfolder\mydb.mdb

    Also if possible, as a test try putting your back end on a non-server machine running Windows 7/8/10 and use file sharing to give access to some test users.  Try out that scenario with multiple users hitting it at the same time and see if you still have the same problems.  While performance overall will likely not be as good at least you should be able to see if later logins have the same negative impact on the earlier ones.  This test might give you an indication whether your server is the problem.

    -Bruce


    Tuesday, June 21, 2016 10:24 PM
  • Thanks Bruce!  I already did the empty database creation / importing all objects ...  for both frontend and backend files.  Same results.  I do like your other two suggestions and I am going to ask IT if we can try those.  They would like to do a test with the backend on a totally different server in another location (an office nearby).  But I like the idea of doing a test on a non-server box too, if that other test does not work.

    Wednesday, June 22, 2016 1:12 PM
  • Bruce ... I tried the IP address in place of the server name, and it did refresh the table links successfully.  But it changes the \\123.45.6.7 back to \\myserver. :(  I did this thru the Linked Table Manager.  Is there any way to do it thru VBA where the IP address would stick?

    NEVER MIND ... figured it out.  Had to browse thru Network instead of Computer.
    • Edited by TraciMarie Wednesday, June 22, 2016 1:34 PM
    Wednesday, June 22, 2016 1:30 PM
  • Ok, so we run though the most common issues.

    And for readers, when a second user slows down, then 9 out of 10 times, a persistent connection will fix this issue. We see this nearly every day on Access forums, and the solution as noted is a persistent connection.

    And for those not reading, they note the application DOES and CAN perform fast with one user (so silly suggestions to change code to speed things up makes no sense since on the given network the application DOES perform fast – just only with one user). After additional users enter the application then does the application slow down. This is (usually) due to the additional users causing Access to attempt deleting of the lock file, and with a persistent connection this re-connecting and attempting to obtain an exclusive lock on the file (for higher performance) eliminates the lock file deleting + creating process. Add in the SWAPPING to single user mode, then this process can take HUGE amounts of time on some networks. So keeping a persistent connection open, then this huge delay and time of deleting the lock file and attempting to obtain exclusive mode use of the file (op locks switch) removes this delay.

    We are now down to the nitty gritty.

    Remaining things to check:

    Try linking the database using the IP address as opposed using the server name. (this was suggested above). So re-link all test users as IP address. This could by-pass or eliminate domain server issues and slow name (dns) resolution (which could reduce name resolution delays).

    Next up, check if the server has any virus protection software – disable for testing as that could be a source of issues. (in fact disable on both  front and back end).

    Another issue is in check if two network connections are active on the client computer (say wiFi still on). With two connections, often one is dropped and the other is activated – again causing delays.

    Also, when is the last time the server been re-booted? If it been many weeks, then that’s also something to try.

    If virus software being disabled (both server and client require this) does not work, and using IP in place of named server then we fast running out of things to check.

    Only thing left after above would be to check op-locks setting on the server, but that’s jumping the gun here until above ideas have been tested.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Wednesday, June 22, 2016 1:42 PM
  • Hi TraciMarie,

    Read the following and have your network administrator help you run a traceroute from each PC using your Access application.

    http://www.howtogeek.com/134132/how-to-use-traceroute-to-identify-network-problems/


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.



    • Edited by DriveEV Wednesday, June 22, 2016 1:58 PM
    Wednesday, June 22, 2016 1:54 PM
  • I am suspicious of antivirus on the server being the problem as well.  This whole issue smells like a server problem of some kind.  I think the OP is already using a persistent connection and even though I suggested using ip address instead of server name in table links I think that DNS problems would present differently.  I am hoping that setting up a test scenario with the back end accessible via simple Windows file sharing or a more plain-vanilla server configuration will be insightful.

    -Bruce

    Wednesday, June 22, 2016 2:25 PM
  • Albert and Bruce ... Relinking tables via IP address did not work.  I also tried removing the backend file password.  I know that was not suggested, but just thought I would try it.
    Thanks for the remaining suggestions.  I will be talking to IT about these things.

    H2fcell ... Thanks for the traceroute suggestion.  I just did that on the server name.  It was very quick.  Two lines and 3 ms total.  I did it on the IP address and it was 6 ms total.

    When I did the ping to the server earlier, to figure out the IP address, it said it made 4 hops.  Is that a lot or normal?

    Wednesday, June 22, 2016 2:37 PM
  • 4 hops and 6ms total is fine.  If all PC’s using your Access application give you the same tracert result, you may want to check each PC’s Local Area Connection Status to ensure everyone is at the same Speed & Duplex.  I remember owning a switch or router that would make all the ports run at the slowest rate used by the slowest configured network card.  It was corrected by forcing all ports and Network cards to 100Mbs and Full Duplex.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, June 22, 2016 3:13 PM
  • H2fcell ... Hmmm ... Mine also shows "Auto Negotiation".  Don't know what others are.  I could look at conference room machine.  But I need to ask IT before I change that.  Don't want to mess anything up.

    IT told me all the desktops and laptops are on one subnet and the servers on another.  They said this can't be changed.  They are going to setup a test where I test the backend on a totally different server in another location.  I am told removing antivirus on any machine on the network is out of the question for our company due to the nature of our work. :(

    IT also told me the networking group can monitor the traffic when multiple people are in the database and see what it does.  I may take that step next if the two things above don't work.

    Wednesday, June 22, 2016 4:05 PM
  • I did test a couple other forms in my database - ones that are very simple with very little functionality and only a handful of fields.  These smaller forms DO move quickly from record to record.  That makes me think that the network or server has trouble handling the larger form.  But I have made a lot of performance improvements to that main form, per my original post above and very little code running when changing records.  Then again, maybe I can't detect the difference between 2ms and 8ms on the smaller forms if they are extremely fast.  I guess 2 seconds to 8 seconds is detectable to human eye.  I may try to strip that main form down to very few fields and see what it does.  But that kind of defeats the purpose of what the users need.

    My mind keeps coming back to the fact that it is always fast when running multiple frontend copies on my machine (on all copies, all the time, no matter what order they are opened).  I confirmed that the backend sees me as multiple users because when I attempted to edit same record at same time, I get a write conflict error.  But it's slow for everyone else as soon as another machine opens the backend (even manually opening it on the server and not opening a table).  What does the network or server see when I have multiple copies open on one machine vs multiple copies open on different machines?

    Wednesday, June 22, 2016 4:12 PM
  • You have a real stumper on your hands.

    My only other suggestion is to take a look at the “Event Viewer” while the PC slows down to see if any errors are being logged to the Windows Logs.  Maybe clear the logs first before performing the test.  That will make finding errors easier.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, June 22, 2016 5:10 PM
  • Your symptoms make me think there is some network aspect involved, switches, AD, cables (and yes cheap cables, old cables can impact a db), ...  This type of issue is incredibly difficult to troubleshoot.

    I would eliminate the server for a moment, and create a copy and place it on a shared folder on a normal PC.  Then relink things and distribute the FE to 2 or 3 users to perform a few tests.  See if there is any difference in behavior.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, June 22, 2016 6:39 PM
  • Well, we did just eliminate the server problem, as we tried it on a server at another location (about 10 miles from here).  Same results.

    I also tried changing the Speed & Duplex settings, per H2fcell's suggestion.  I changed it on my machine and the one other machine we did testing on.  Same results so I changed the setting back.

    I looked at the errors in the Windows logs.  Very few errors and none at the time I was doing testing.

    So, as Daniel just eluded too, it's probably a network issue.  I'm looking into the idea of putting BE on a normal PC.  Discussing with IT now.

    Will keep you all posted.


    • Edited by TraciMarie Wednesday, June 22, 2016 7:14 PM spelling
    Wednesday, June 22, 2016 7:09 PM
  • I may have missed it somewhere, but what version of Access are you running?

    A fellow MVP just mentioned in another discussion a performance issue I had never heard about.  It's a stab in the dark, but who knows it might apply to your situation, perhaps.  See:

    https://support.microsoft.com/en-us/kb/2553116
    http://www.intersoft.co.nz/Support/TSB.aspx?id=TSB1032


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 23, 2016 12:54 AM
  • Also, what is the file format involved?  mdb/accdb?  The original version of which was created with which version of Access?

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 23, 2016 12:59 AM
  • Running Access 2010.  I developed the database from scratch using Access 2010 (both FE and BE files).  File format is accdb.  Some users in our office are running Access 2013, but those are the only two versions running ... 2010 and 2013.

    Thanks for the info on the hotfix.  No way IT will let me apply that.  But I don't believe that is the answer because it runs fine when it's just one user in the database.  It's also fine if I run multiple copies / instances of the FE on my machine connecting to the same BE.  It's only when the BE is opened on another machine that the problem occurs.

    Working with IT group to get to bottom of issue.  Will keep you posted.

    P.S.  One idea that comes to mind is to test the database at the other office about 10 miles away - on the main server there.  Thinking if there really is a network problem at my location, then it should be fast at the other location, correct?


    • Edited by TraciMarie Thursday, June 23, 2016 1:58 PM more info / another question
    Thursday, June 23, 2016 1:13 PM
  • Please inform them of the MS article.  They released a fix for a reason!

    Good luck solving this mystery!


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 23, 2016 1:55 PM
  • OK, I'll ask.  But that hot fix is from 2011.  Aren't all hot fixes incorporated into the next Service Pack?  The exact version number I have is: 14.0.7166.5000, which is beyond SP2.  SP2 was released in 2013.
    • Edited by TraciMarie Thursday, June 23, 2016 2:31 PM
    Thursday, June 23, 2016 2:30 PM
  • Hi TraciMarie,

    To check whether it is related with this specific project, I will suggest you create a simple Project with a Table and a Form, and retest above steps. If it could not reproduced, it would be helpful if you could share us all related code and event in your original main form.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 24, 2016 2:58 AM
  • Do you have any other databases you could test along side to see if the behavior is the same for all databases (IT related) or just this one in particular (database issue)?

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, June 24, 2016 10:59 AM
  • Edward and Daniel ... you have definitely touched on something.  I did have another, older database I created for another company that did not act as slow as this one.  But that one was simpler.  I also have other forms inside my current database that don't seem to be as slow.  I admit the main form does have a lot of components, but I have simplified it a lot as far as code and functions.   I still think the behavior is odd.  I think it's too much to share all the code here, but I will do as suggested and do a very simple database with table and form and test that.  I am also thinking I can simply copy the database I have and strip down the main form to just a few fields and see what that does.

    P.S.  IT said I cannot test the BE on a plain desktop machine to share with others.  They are not permitted to share out anything other than their production servers, due to security measures.  So, that is out.  They are suggesting SQL Server for the BE, which I may end up doing if all else fails.

    Thanks again!

    Friday, June 24, 2016 2:40 PM
  • Just so you know SQL Server typically does not improve performance.  Actually, in many cases there is a performance hit when you make the switch.

    You don't use SQL Server for performance reason.  You use it for Security, advanced functionalities, ...


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, June 25, 2016 12:08 AM
  • Daniel ... Funny, I have seen arguments on both sides of the SQL Server performance issue.  Some say it improves speed and others say, as you do, it could actually make it worse.  I agree with you.  I would prefer to keep the BE in Access anyway.  I'm not all that familiar with SQL.  Just another headache I would have to deal with.

    Today I will be doing a lot of changes, also trying a basic table and form ... and then testing with two users (doing one change at a time and testing it piece-meal).  I'm now setup with a second laptop in my cube (going thru VPN) so multi-user testing will be easier.

    Will keep you posted.

    Monday, June 27, 2016 2:50 PM
  • Well, after much distress over what is wrong with this database, I think I may be getting somewhere!  I did some of the testing suggested above (starting with a very basic form, etc).  That worked - all was fast with two users.  I even took the form I had and removed all tabs and subforms, leaving me with about 10 main fields.   That also worked - very fast with two users.  So I figured the culprit has to be the tab control or one of the pages on it.  I removed each tab, one by one, and tested with two users each time.  When I finally got to this one tab ... once it was removed - everything was lightning fast with multiple users - no matter who was in first or last!!  In fact, it was faster without that tab with two users than it had been before with just one user!!  Record navigation is just milliseconds on both machines!!  I admit that this tab I removed is most complex of all of them.  I am thinking Access doesn't like something on it ... either the main subform itself or a button or control or something I have overlapped, etc.  I would have never guessed.  I have seen odd behavior when controls are too close, but usually it's the same behavior all the time (doesn't tab to that control, etc).

    All of that over one tab???  SMH.  That's Access for ya.  Wow.  Anyway, so I know where to begin tomorrow.  I am going to very slowly and methodically add components of that tab back and test each time and see where it breaks.  If all else fails, my manager and I have discussed moving that tab to it's own form completely.

    Thoughts?

    P.S.  Do you think something could be corrupt on that tab or maybe the subform on there corrupt?


    • Edited by TraciMarie Monday, June 27, 2016 8:58 PM another thought
    Monday, June 27, 2016 8:53 PM
  • Assuming the sub-datasheets are off etc., you could consider only loading in the sub-form to that tab in question WHEN the user selected the tab.

    In fact Access attempts to load all forms in all tabs, so OFTEN a developer will leave the SourceObject setting for the given “sub form control” that you placed on the given tab blank. That way, you can have 1, or 20 tabs and the main form with all these tabs will load instantly since all 20 forms are not being loaded behind each tab.

    So even if you do find the issue, it still a good idea to consider not loading all those forms until such time the user actually decides to use the given form.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada


    Monday, June 27, 2016 9:21 PM
  • Albert, thanks for the tip but I am already doing this - exactly how you describe it.  Hence the third bullet on my original post about things I have already implemented ...

    • Only loading subforms or combobox sources if the user clicks on the tab with that subform or combobox.

    Monday, June 27, 2016 11:54 PM
  • Figured out the true culprit!  I have four unbound boxes on that tab that have their control sources all set to complex DLookup functions.  I forgot I had these as DLookups, which from what I read, can really slow the system down!  I changed them everywhere but here.  If I set all four to null, it is lightning fast with two users.  Can't believe it.  They refresh each time the user navigates to a different record, as the data in those boxes changes, so that's obviously what is slowing it down.  I guess it has a harder time pulling this data when someone else has the table open??  I tried changing them all to ELookups (Allen Browne's function), but it's still slow.  So, I will look at it again this afternoon.  Thinking I may go another direction and put these boxes in a subform that links to the main form.  All four are pulling from the same table.  That may be another problem.  Maybe it's too much to be pulling from that table all at once via DLookups?  Anyway, glad I found it and now I need to change it.  Any suggestions or thoughts welcome.
    • Proposed as answer by Paul23 Tuesday, June 28, 2016 9:51 PM
    • Marked as answer by TraciMarie Wednesday, June 29, 2016 2:02 PM
    Tuesday, June 28, 2016 4:05 PM
  • Hi

    Make the path to the BE as short as possible. I mean really really short. Like Z:\BE.accdb


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    Tuesday, June 28, 2016 5:23 PM
  • Hi

    Ask everyone to shut down the database. Look for locking files (the laccdb)
    If you find any, delete it. Corrupt laccdb files can cause ver bad performance.


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    Tuesday, June 28, 2016 5:29 PM
  • If there is any way that a single SQL statement will enable you to pull values for all four of your unbound "boxes" then I would suggest writing a single sub or function that gets all four values at once rather than using dlookup or elookup four times.  This would be signficantly more efficient, especially if all four values can be produced from SQL which returns a single row.

    -Bruce

    Thursday, June 30, 2016 4:13 PM
  • Well, what I ended up doing is I moved those four boxes into a subform and then put that subform on the main form and is is linked by the main ID.  I originally just based that subform on a query (which was based on another query), but that still seemed somewhat slow.  So, now I have it where I do a make-table query (from those queries to get the data) and it created temporary / local table.  It only runs the make-table when the user clicks on the tab the subform is on (or when they go to another main record and the tab is still active).  You would think that would be slow, having to run the make-table all the time, but it is lightning fast!  So for now, that's what we're going with.  I did multi-user testing yesterday with four users and everyone was fast!  I was very happy with the performance ... finally!

    I am thinking the reason it is slow with doing queries directly on the table is because that table is somewhat large ... over 80k records and I have some expressions in the queries.  It only needs to analyze about 100 records (those related to the main table) to get what it needs for the four boxes.  So, when I do the make-tables, it only pulls those 100 records down and then does the expressions / functions on that small local table.

    But I'm open to suggestions on doing it cleaner / better.

    Thanks to everyone for their help!

    • Marked as answer by TraciMarie Friday, July 1, 2016 1:50 PM
    Friday, July 1, 2016 1:42 PM