Sudden performance drop on form with no apparent reason

[Gryphoune]

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.

[orange]

What is the query? Please show us the SQL.

[Gryphoune]

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];

[NTC]

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?

[John_G]

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.

[Gryphoune]

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.

[CJ_London]

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

[orange]

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.

[Missinglinq]

Even your description,

...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)>

[Gryphoune]

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.

[Gryphoune]

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.

[NTC]

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.

[Gryphoune]

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

[orange]

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.

[Gryphoune]

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.