Compacting solves performance issue but.......

[TOPSie]

I support a database for a distribution company - two identical accdb PCs and a backend mdb.
Each evening when the vans return the customer payments are processed.
A screen with a list box of customers with an outstanding balance - populated with a fairly simple SQL join is presented. The paying customer is selected from the list and the payment recorded.
The the next customer is selected (after the listbox is refreshed) and so on for all paying customers.



If the database is "fresh" the process is very quick, however as time goes on it gets slower and slower.
If the whole system is shut down and the backend is compacted then all is quick again.

But this is operationally undesirable. Is there any "garbage collect" or "refresh" facility to get things moving quicker without a full compact??

[CJ_London]

Not clear of your setup? Sounds like you have two users but is the app split? Tables only in the back end, everything else in the front end. The back end located on a server and a copy of the front end on each users machine.

Other thoughts- are tables normalised and properly indexed?

[Welshgasman]

Look to see how much the BE size is each day.
I had to import data from a spreadsheet each day when I worked in a bank and it caused bloat a lot. That is just a by product of how Access works.

[Gicu]

You could try to setup a compacting schedule at night when the files are not in use, here is a link to my free utility that can be scheduled to run by the built in Windows task scheduler.
https://forestbyte.com/ms-access-uti...end-compacter/
Cheers,

[TOPSie]

You could try to setup a compacting schedule at night when the files are not in use, here is a link to my free utility that can be scheduled to run by the built in Windows task scheduler.
https://forestbyte.com/ms-access-uti...end-compacter/
Cheers,

The (backend) db is compacted each night. In operation it does not grow very much (less than 5% in a day).
The issue is that some processes (e.g. recording incoming payments) get slower and slower VERY QUICKLY, e.g. after doing 20 or so transactions. It is impractical to do a full compact every 20 minutes.
This seems to be a recent turn of events - though other similar transactions exhibit this behaviour - its just that the other operator doesn't complain as much.

In other systems there are things like garbage collection and cache clearing - does Access have anything like this???

[Minty]

Does the front end size change dramatically during this operation?

I noticed a problem with a clients system, where a temporary table was being used to speed up a complicated series of manipulations.
It had inadvertently included a logo image as one of the fields, and was pulling in 25000 records which meant it grew alarmingly, and eventually ground to halt.

Have you double checked you are explicitly closing all the objects, and setting them to nothing when the process is finished?

[orange]

In other systems there are things like garbage collection and cache clearing - does Access have anything like this???

In Access that is Compacting.

[Micron]

IIRC, from what I've read there is such a thing and it happens in the background. It's why some people think you don't need clean up by setting object variables to Nothing in code. I would not follow that practice, preferring to be sure memory is released by cleaning up.