This is really ridiculous. I have a 3.4 meg access 2002-2003 format database. It is used by approx 6-8 people in our company to enter and track manufacturing jobs (approx 30 per day) as well as automate reports about said. It also collects data from smaller databases that are updated via barcode Pocket PCs. So far so good.
The problem is, throughout the day the database grows...and grows...and grows. By the end of the day, it is 1.3 GIGS in size?! If I compact it at that point, it goes back to approx 3.5-3.6 (allowing for small growth due to data entry etc).
What gives? I can run compacts, but I have to kick everyone out of the system (which is being used at all times during the business day) which is highly annoying for them and inconvenient. We also have a user that connects via VPN and the enormous size is going to create issues there as well.
I am not using any Make Table queries, and primarily use recordsets to get data and populate the forms and list boxes.
I can't fathom what is making it grow so huge every day. We had Access 97 databases at my last company (also designed by me) that were horridly designed (by me - lots of bound forms and awkward code), way more users and data, that never got above 400 megs at the worst of times.
Is this database the back end or front end? Or do you have all your tables, forms, reports, etc. in the same MDB file?
If you do, moving the tables out to a back end database may help. One caveat the front will run best if on users lYocal machine.
Also have you normalized your tables -- or is everything in one massive table or two?
You said you're only adding about 30 manufacturing jobs per day to the database. How many fields are involved, how much data is a "job"?
If you have large tables that get a lot of data added each day you can also set up a separate "back end" mdb file for each of these tables.
I have done this with some success, I recommend naming the back end similar to the table named, but with a prefix to indicate it only holds one table.
The table DailyWidgetProductionDetails could go by itself in tblDailyWidgetProductionDetails.mdb
The table DailyWidgetSalesDetails can go into a separate mdb file
This way (starting with the same prefix) you always know where on the directory to look for those tables, and just by seeing the file name you know what table it is. ideally you might put these tables into SQL server but this can be a quick interim solution.