Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24

    Sudden performance drop on form with no apparent reason

    Recently we have started experiencing a major performance drop on certain fields of a form we use to enter data. There are 3 list boxes and one date field. We experience substantial lag when switching to or from these fields while the database runs a query. I've never seen this happen before and somewhat perplexed as to the cause. I have run an analysis on the tables with no discrepancies other than suggestions to use fewer controls and we compact and repair every morning. Below is a screenshot of the form with the problem fields highlighted.



    Any help would be greatly appreciated.

    Click image for larger version. 

Name:	Access Query lag.png 
Views:	26 
Size:	26.7 KB 
ID:	25133

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the query? Please show us the SQL.

  3. #3
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    Here is the SQL for the form query. I'm not sure what query is being run as it never says. Whenever I select or deselect one of the fields it says Calculating in the status bar and then Running Query and takes anywhere from 5 to 20 seconds to complete.

    Form SQL:

    SELECT [AUDIT NOTES].ID, [AUDIT NOTES].Followup_Date, [AUDIT NOTES].PROPERTY, [AUDIT NOTES].RESERVATION_NUM, [AUDIT NOTES].[ARRIVAL DATE], [AUDIT NOTES].CONTACT_DATE, [AUDIT NOTES].COMPLETE, [AUDIT NOTES].COMPLETION_DATE, [AUDIT NOTES].[ERROR CATEGORY], [AUDIT NOTES].[ERROR CLASS], [AUDIT NOTES].[ERROR TYPE], [AUDIT NOTES].RESOLUTION, [AUDIT NOTES].[LOGGED BY], [AUDIT NOTES].[ERROR AMOUNT], [AUDIT NOTES].[AUDITOR NOTES], [AUDIT NOTES].[Record Added Date], [AUDIT NOTES].[Source code Desc], [AUDIT NOTES].DeptResponsible, [AUDIT NOTES].[COMBINED CODE], [AUDIT NOTES].ROOM_REVENUE_AMT, [AUDIT NOTES].RESORT_FEE_AMT, [AUDIT NOTES].OTHER_REVENUE, [AUDIT NOTES].Res_Type, [AUDIT NOTES].CHARGEBACK, [AUDIT NOTES].[Not Charged at Check In]
    FROM [AUDIT NOTES];

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    is a link to an excel sheet involved?

    how many choices are there for Error Class and Error Type? - the tables that drive these one would think would be very static

    it is a correctly split database with each user have their own front end file linked to the back end file?

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It might not be MS Access causing the problem. Using task manager, did you check to see if there were any other applications running that might be tying up the CPU? I have seen that happen. To the extent that you can, try running the database with no other applications open.

  6. #6
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    There are no Excel links and none of list boxes are separate tables. They are just editable lists though I have been thinking about creating tables for them. This is a shared database among 5 users so I don't see a need to split it. Also, we are getting no support from our IT department as for some reason they do not like Access so I wouldn't be able to install the front end on the separate machines.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    This is a shared database among 5 users so I don't see a need to split it.
    even for one user, it is better to split in case of corruption to make recovery simpler. Two or more users on an unsplit database increases the chances of corruption significantly if they are using it at the same time. So a corruption as a reason for your problem is quite a possibility. Suggest compact and repair the db.

    Also, we are getting no support from our IT department as for some reason they do not like Access so I wouldn't be able to install the front end on the separate machines.
    The other users must have the access application installed on their machine otherwise they would not be able to run your db. Installation of a front end is simple - they just need to copy it to their desktop/documents wherever.

    Other issues that can cause a reduced performance include

    lack of or incorrect indexing
    use of lookup fields in table design
    use of multivalue fields
    reduced network performance

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is a shared database among 5 users so I don't see a need to split it.
    Really??

    Do a little research.
    You can start here.

    Just curious--- why are you using 64bit Access/Office?

    Can you post a copy of the database? Compact and repair, then zip. Remove confidential data.

    Good luck.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Even your description,

    Quote Originally Posted by Gryphoune
    ...I've never seen this happen before and somewhat perplexed as to the cause...
    Points to this being non-split as the cause of your problem! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

    The record, by the way, was a Non-Split db that had been working, without problems, for 12 years! It then started exhibiting the problem detailed here and continued to do so until the app was split.

    If your Database isn't really important, which is to say if data-loss isn't important, and the app being down won't cost you production time loss, then by all means leave it non-split.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    Thank you all for your input. I had considered splitting before but was not sure it could be done due to the lack of IT and network support we have. I will be doing some heavy research on splitting this so we can get back to entering data again without lag.

  11. #11
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    Just curious--- why are you using 64bit Access/Office?

    Can you post a copy of the database? Compact and repair, then zip. Remove confidential data.
    We use what IT install on our machines. We compact and repair daily with a daily backup saved on the shared drive.

    I will upload a copy of the db tomorrow. It does need a lot of work but I inherited it and cannot spend a lot of time on it as maintenance is just a side responsibility.

  12. #12
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    It's only okay for IT to 'not like Access' - if they offer an alternative. I understand, and have worked at, corporations that do not want lots of little dbs existing where there can become differing information for different people. So they want all data together to be sure everyone uses the same data - - but they then need to give you a solution using their database. As others have posted - one cannot have a multi user unsplit db. That is an incorrect installation. A single user can be unsplit - but then you must not attempt to have multiple users.

  13. #13
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    I split the database and performance has improved but the form was still acting up. I have rebuilt the form but I'm now having a problem with one combo box not retaining all the entries. Is it limited to the 225 character max? If so, is the way to get around that then to add a new table for just those entries. The field I'm having trouble with is the Property field in the AUDIT NOTES - ALL PROPERTIES form. I have have manually entered all the properties (70 total, 3 letter codes) but after saving and redistributing to a couple of the users, there are only about 20 to select from.

    Here is a copy of the front end.Night Audit_design copy.zip

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would put those values into a separate table, then use that table as the row source of the combo. But I don't see why some users do not see all of the values in the value list--they will have to scroll down because of the length of the list. According to the combo format you show 16 values.
    I recommend a naming convention with NO embedded spaces.

    I don't quite understand all of your subforms -different subform for each "area". This would seem to fit a generic subform that you would populate with values selected from some control (combo probably)???

    If this database is important to your company/organization, then it is an operational system and needs someone to have responsibility and accountability for it. If management and IT don't care, that is much like saying, we really don't care about our business -Not a very comfortable thought.

    Good luck.

  15. #15
    Gryphoune is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    24
    Thanks, Orange. I was thinking with that many items on the list it should be on a separate table. There weren't that many when the database was designed and we actually don't use most of the subforms anymore. I just haven't had time to try and clean it up.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. ACCESS and SQL SERVER the reason
    By elico in forum SQL Server
    Replies: 10
    Last Post: 02-02-2016, 02:51 AM
  2. Replies: 3
    Last Post: 06-17-2015, 05:18 PM
  3. Button to open form sudden error
    By tgunarto in forum Macros
    Replies: 2
    Last Post: 11-21-2014, 01:04 AM
  4. Replies: 2
    Last Post: 03-28-2013, 12:32 PM
  5. Replies: 3
    Last Post: 11-29-2011, 07:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums