I have a backend data base with all users linked to tables via a frontend. Each user has the front-end on their desktop.
lately my 120mb backend has been bloating at infrequent times right now over the last 3 days it is holding at 230mb. It has jumped as high as 520 mb and even gotten to 900mb!
I have no open record sets - I close and set every one to nothing (although it would be nice if someone had a snippet to check this from the vbe window.
I can tell you at any given time I can have up to 20 to 25 users in the system (in fact right now there are 15). There is no importing of text files or excel files taking place - which from my experience can cause bloat.
There are export utils but that should have no effect, and they are generated from the front end.
Now I have simulated network drive on my PC (subst command) and linked to the back end that way and it never grows or bloats -
This problem has just recently started after being on that network for 12 months.
I have imported the DB into a new container - multiple times
I do have a couple of theories that I need a second opinion on
1. I link to several excel sheets via the backend and subsequently that link is mimicked through the front end.
2. I though maybe connection drops copuld do it but generally you would get a network error and Access would not persist the connections.
This is making me nervous and any suggestions from experience would be welcome.
please do not say corruption and reimport and re-compact. Recordests are closed and set to nothing. I had this app on a different network in Houston (I am offshore in Africa with this version) and never had the problem
I do have a couple of users that terminal services in but that is just remote controlling a local pc
If you already tried to compact/repair the database as well as importing the objects to a new one... to no avail, we can assume that the problem does not come from a corrupted db.
I would try to start with a "deflated" database and monitor its size (it would be easier in a single operator mode, i.e. only one FE in use) and try to find which operation increases its size the most (I suspect the linked Excel worksheets). From there, it would hopefully be possible to find another way of doing whatever is done in the aforementioned operation without causing such an increase in size.
As for identifying the RecordSets that are left open, the Database objects has a Recordsets collection that could probably be scanned for this purpose. See: Database.Recordsets Property (DAO)
excellent advice - and I have already monitored the "deflated" DB. Interestingly I tried an old DOS trick when a file seems to consistently elicit irregular behavior I would rename the file and copy a known good version back onto the drive. This allowed me to "freeze" an area of the drive that could have bad sectors, corruption, etc. And now the file has not grown (since I did that about 5 hours after I made this post. LOL )
But I do agree with the excel files and as we speak I am providing a solution to this problem