Access Backend grows rapidly

[D000]

I have an access database split into the back end and front end. It's was growing, but it worked for one and half years, and once it reached the two GB limit, we repaired it using the compact and repair option. After that, the backend grows almost 200 MB for each entry and reaches the limit every day.
Both the back and front ends are in a shared network, and each user has a copy of the front end.

I am new and have limited knowledge in access; Please need your help. Thanks

 

[theDBguy]

Hi. Welcome to AWF!

There are several things that could make a database file grow in size. For example, are you using any Attachments or temporary tables?

 

[Ranman256]

don't save photos/images, instead save the PATH to the photo/image.

 

[Cotswold]

I would check if you actually need all of the data in the live database. Is some of it years old historical data? Can some be archived into a what would be a little used Archive database? You can open many databases at the same time, so if someone needs archive data it could be opened, accessed and then closed. But with editing prevented of course.

Maybe export all of what is considered current data to a new Database.accdb and rename the existing as the archive. Maybe yearly you can then export a years data and delete from the "live" database. As a 'for instance'; how often does anyone need to access a three or four year old invoice? Out of many thousands would it be once a year, or less? So why keep them in a live database?

What businesses need is to know what is happening now and in the next few weeks and months. And how they are managing that work. That is the important bit. Which customers are increasing their purchases and why? Is it because their business is growing, or are they being refused credit from their usual suppliers?....etc, etc.

So often 'management' claim to need access to data but in reality never actually use it or even look at it. For more years than I care to remember, I supplied software to the waste management industry and in 99.99% of the time they had neither the time, inclination nor a practical use of historical data. The systems would also transmit huge amounts of data to government quangos and departments, none of which saw the light of day afterwards. All they needed was to see the issued totals once and then basically forget it for ever. Mind you, the extraction of complex reports from masses of data was always helpful to the impoverished software developer. So I never discouraged the practice

Maybe not relevant but take a look at this, as bloat was also an issue :

Access bugs and Anti-Virus

Regarding the various problems with Access bugs caused by the Office updates, I think you should also be aware of the effect of (some/all?) the current anti-virus software. I recently had an issue where the FE I was working on that would not compact and gave the error "C:\.........accdb...

www.access-programmers.co.uk