First up -- don't ask me why I have a seven-database Access system with 50-100 concurrent users. I'm stuck with it....
I have a front end database that links to six other databases. One of the backend database bloats horribly, and seems to be getting exponentially worse. It bloats from about 170MB to 2GB in about 4 hours (and then 'crashes' because it's reached its size limit). None of the other databases bloat like this, although we do a nightly compact and repair.
This particular database is used for dual keying data. One set of users grabs a handful of records at a time from multiple tables from Backend #1 and appends them into Backend #2 (the problem database). Another set of users does the same but appends to Backend #3, and is limited to what the first group already grabbed. There is a matching process to check their work done on the front end, and if it's OK, then Backend #1 is updated based on what #2 and #3 did.
Now, what little research I've done so far tells me that all the append queries going on could be causing the bloat, but why only from #1 to #2 and not #1 to #3?
I was hoping for a handy list somewhere of possible reasons so I could check through them as I step through the code but I couldn't find it. I couldn't get to the "Advanced Search" option on this website to see what was already posted. Anyone know of this list? Anyone able to create one pretty quickly, or have some ideas for me?
In Access there's a tool you can use to analyze the database.
I think if you look under the tools menu then analyze - this might help you find the answer - it might also give you some ideas of how to improve the way things are working. Without actually looking at the database it's hard to diagnose.
Also, an access database for 50+ users? If I were you I would think about moving to MS SQL Server 2k5.
Which database is bloating? How many records are you updating in 4 hours? I have a database that we use to print direct mail pieces from. So we will dump 1 million records in and then break that 1 million records into groups of 500. The process of assigning a group to each record will cause the database to bloat from 700 Mb to 2Gb.
I spent several hours trying to figure out how to prevent the bloating so the operation could complete successfully. The first thing I did was to swithc from DAO to ADO. The switch worked for awhile, then I started putting in 1.5 million records in and it would blow up.
My next approach was to use SQL exclusively (no recordsets) when I figured out a solution using UPDATEs I have almost zero bloating. I don't know why it worked but it worked.
If you are updating a number of records using recordsets then I would imagine this is a potential bloat problem.