1

I have built a small Access database with a few tables. The data is stored in several SharePoint lists linked into the database (so changes to the list are reflected in the DB), and the Access file itself is stored on a SharePoint server. The file format is .accdb.

Upon opening it, Access prompts to save changes to the server. As the data is stored outside the file itself, there aren’t normally any changes to the file content itself—unless someone were to modify a query or report, which is rare. (I am aware that the Jet database engine treats a mere open operation as a modification, thus there’s no true read-only mode.)

Originally the file was some 8–9 MB in size. Over time, the file grows in size and has at one point broken the 100 MB barrier. After running “Compact and Repair” on the database, the size is back to the original 8–9 MB.

Users have a mix of Access 2010 and 2013 running on their PCs.

What is causing Access to inflate the file like this, and how can I prevent it?

1

Taken directly from support.office.com:

Database files grow with use

As you add and update data and change its design, a database file becomes larger. Some of this growth comes from new data, but some comes from other sources:

  • Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them.

  • When you delete a database object, the disk space that the object occupied is not automatically reclaimed — the database file still uses that disk space, even though the object is deleted.

As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer.

Although you specifically state that your Access database simply "queries" an external source and is only a "front-end" then, I guess, it is still creating a lot of temporary data that is being stored to the file and not being cleansed.

I wonder if it may be worth performing a scheduled task to compact and repair the database - but you need to ensure no-one is connected to the database (e.g. leave their computer locked and the file open in Access at the end of the day) otherwise the compact and repair will not be performed.

  • You could always add to the scheduled task, to first kill of any sessions holding the db open. – djsmiley2k Aug 9 '17 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.