Discussion Locked

slow Access database

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
My new database is slower than others I have written. The slowness shows itself at opening of forms and reports. Once a form is open the user can move from table row to table row, with the associated information, quite quickly. My suspicion is that the forms and reports are slow to get set up because they have many subforms and subreports, sometimes nested, and combo boxes. I notice that a few small changes(format in reports) to the database increase its size from 5.3 meg to 7.5 meg or so, before I compact it. I wonder whether this is connected with the slowness. Any suggestions?

3 replies

by shanghai sam

MS Access app's can only be slower for a number of fields and values in combo boxes as well as sub forms/reports.

Some suggestions:
- I always try to re-engineer the process of how a user can best get their job done and showing them (almost) the minimum number of fields/values to get it done. Extra information can be viewed with a report OR edited with an extra screen.
- I always keep my Add, Edit, Read-only screens separate and users only get access to a minimum number of records (usually just one after a search) to view at once.
- Don't show a field/form, unless you have to.
- Always provide a custom search screen to provide an accurate search (this reduces the work that your back end DB does in determining records (and tables) to copy to your front end app'.
- Always specify that a report's (and form's, if applicable) dataset is read-only.

To reduce the size of your front end application, consider using a decompile, before you compact. In short, the decompile will clean out dirty (old compiled versions) code. But look at the following link (remove spaces) BEFORE you decompile as there are further considerations to know about. For more decompile info:
http://www.trigeminal.com/usenet/usenet004.asp?1033

I think your biggest issue is the one that you have already identified -> Number of fields, nested subforms and combo boxes.

Hope it helps
TimFox

by timfox

sorry - don't know why my login is having troubles... (above)
- Hope this one works ;-)

Additional note - I believe the reason why the speed picks up after the forms/reports have loaded is because the data now resides on the local machine.
TimFox

This discussion is closed to new replies.