Database Bloating Problem

[jfriedm]

I'm working in Access 2010, but with a 2002-2003 database file, as my client computers all have Access 2003. I've already split my database into a front-end and back-end, but am having major bloating issues on the back-end.

Basically, the data in this database is refreshed from 4 text files every day. (there's no actual user input)... so on my back-end database, I have these text files as linked tables, and then run a simple set of delete & append queries of all records to the actual tables that are used by the clients.

My original db size is about 37 megs, however every time I do this, it adds another 37 megs to the file size. Compacting / repairing solves this issue, but isn't practical in a live environment as the database would always be in use.

In an attempt to debug, I discovered that if I just run the delete queries, my database size doesn't shrink at all.

Any thoughts?

[John_G]

I'm not much of an expert in the technical stuff - but Access is like that. Deletes will never cause the size of the database file to decrease - you have to compact & repair to recover the space. Significant appends will cause a file size increase - Access is not good at re-using space, it seems. You will see the same "bloat" in the development version of the front end, if you frequently modify forms and reports and queries, and your deployed copies can have the same issues as well. Queries with multiple joins can be really nasty at times; I have seen one cause the file to actually exceed the 2GB limit - you might want to implement Compact on Exit for the front end if is a serious issue.

For the back end, you might have to implement a policy whereby the database is "unavailable due to maintenance" for a brief period every week, so you can do the Compact and Repair. It's just something we have to put up with.

John

[JoeM]

Another option may be looking at using something besides Access for the back-end, i.e. SQL, MySQL, Oracle, etc.

[jfriedm]

I'm not much of an expert in the technical stuff - but Access is like that. Deletes will never cause the size of the database file to decrease - you have to compact & repair to recover the space. Significant appends will cause a file size increase - Access is not good at re-using space, it seems. You will see the same "bloat" in the development version of the front end, if you frequently modify forms and reports and queries, and your deployed copies can have the same issues as well. Queries with multiple joins can be really nasty at times; I have seen one cause the file to actually exceed the 2GB limit - you might want to implement Compact on Exit for the front end if is a serious issue.

For the back end, you might have to implement a policy whereby the database is "unavailable due to maintenance" for a brief period every week, so you can do the Compact and Repair. It's just something we have to put up with.

John

Thanks John,

I'm not experiencing too many problems on the front-end of the DB... it's all in the back-end.

In terms of having the database "unavailable due to maintenance", this is certainly something I'd like to explore. However, how does one go about deleting the record-locking files and/or forcing the database to be inaccessible for that defined time?

Joe -- A MySQL version was actually the preferred back-end, but none of my clients have the MySQL ODBC drivers installed which basically put that out of contention. Resources put Oracle and SQL Server out as well.

[JoeM]

I think you will need to go with John's suggestion, as you will need to run this Compact and Repair.
As long as users are all out of the database, there shouldn't be any remaining locks on the database. However, if there are, you may want to refer to this article for options: http://support.microsoft.com/kb/304408