Database created by ADO (from VBA) reduces file size after opening in MS Access. Why?

Question

I'm currently learning how to create databases & tables using ADO from VBA (running from Excel). I am able to create a database either of format .mdb (using connection "Microsoft.Jet.OLEDB.4.0;") or .accdb (using connection "Microsoft.Ace.OLEDB.12.0;") - and I am able to create tables and insert records. So far so good.

I notice, however, that after I create such a database via ADO, if I open it in MS Access, the file size reduces dramatically. Note: just the action of opening it in Access has this effect. I'm not making any changes to the data there.

An example: a freshly-created database from ADO is 1304k; then after opening in Access it's 816k.

As far as I can see, no data is being lost when it is opened in Access. I can only guess that Access is somehow automatically optimising the database when it's opened, or otherwise getting rid of unnecessary stuff which was inserted by ADO. Obviously, I would prefer the file size to be as small as possible. So I'm wondering:

1) Does anyone know what's going on here?

2) If ADO is inserting unnecessary data, is there any way I can optimise it programmatically? My end users will be running Excel only, so I can't ask them to periodically open the database in Access just to optimise the data.

Answer 1

When you open an "Access database file" in Access, it adds/creates/updates a "Access application project" inside the database file. It's basically just adding some extra blobs/tables/data inside the database if they are/it is missing. And it checks the file and truncates the file length to the correct file length.

When it does this, it may be correcting a file length error, or discarding some unused cruft, or just adjusting the amount of space that was reserved for just this purpose. But whatever, it is actually doing something that can't be done any other way, and it is using different defaults than you get any other way.

In older versions, when it did this, it always made the file bigger. Now, on your computer, using your versions, it's making the file smaller. This may be because of bugs in the original file creation, or it may be "behavior is by intent". You can't change that, so you shouldn't worry about it.

But it's not always a good idea to truncate unused file space: if you are going to add data to the file, you just have to request more file space. And the different version libraries for mdb files had different ideas about what the "best" way to compact a database file was, and would give different file lengths.

Having said that, it used to be possible to compact & repair a database file (mdb) using JRO or DAO. That would discard unused objects (compact) and correct the file length and delete hanging references (repair). I'm not aware of any similar functionality for ACE, but I haven't looked.

Answer 2

Without knowing specifically how you are creating tables, importing records, structures and schema; or your environment 2000/02/03 mdb or 2007/10/13 accdb version, machine (32-bit/64-bit, laptop/desktop), Windows OS (XP, Vista, 7, 8, 10), single CPU or LAN, one can only speculate.

First it is important to understand the strange, hard to define program that is MS Access. In essence, MS Access is a suite of connected objects: Jet/ACE SQL engine, GUI front-end application, report generator, and IDE coding interface. It is not actually a database but ships by default to the ACE engine which by the way is not restricted to MS Access but is wholly a Windows technology (.dll files) available for all Office and other PC applications. Excel can run accdb/mdb files without Access even installed! When compared to other databases such as another file-server (popular, open-source) counterpart, SQLite, and client server (SQL Server, MySQL, PostgreSQL) it is really the Jet/ACE component that is the relational engine being compared. Interestingly, Access can connect to all aforementioned RDBMS's by switching out its default.

With that said, the multifaceted nature of MS Access makes use of temporary objects in both SQL backend engine and VBA frontend, interacting with both hard disk and memory, especially in importing records and various queries. Even the MSN website mentions it on its Compact and repair page.

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

Furthermore, make-table and action queries (append/update/delete) actually copies the entire resultset before committing final changes. Hence, users are prompted about the number of records added before changes are finalized with opportunity to rollback the action. So after migrating data, your database may have returned slightly to former state. Then, there's the garbage collection in VBA that releases memory when objects are no longer in use and OLEDB driver discontinued connection. It may be you witness some difference in file size as some space was recovered after your VBA processing finalized and went out of scope. I wonder at what instance you viewed the larger sized Access file? Would a refreshed Windows Explorer or CPU restart change the view? Did you read properties of file (from right-click) or details section of Explorer which differ in approximation? Do you witness other file size changes like in Excel workbooks? Is this a regularly occurrence or anomaly?

Managing the database creation process purely in code may the most efficient way to use the database instead of using Access' graphical user interface as multiple-user access locking and application objects add some overhead.

All in all, it is not likely ADO adds any data or components without your development knowledge. Regularly, decompile, compact, and explicitly remove VBA objects (i.e., set obj = Nothing). See helpful performance tips. Also, don't focus too much on file size but performance and integrity as over the course of app, file size will be a fluctuating metric. One final note, though Excel is very popular and easy to use, as mentioned about the powerful native components of MS Access, consider developing your end use needs in Access (free runtimes are available via Microsoft, allowable since Jet is a Windows technology). In every aspect, Access provides a more stable multiple user application and automation environment.

Answer 3

Access might be compacting the database when you close it. There is a setting called Compact on Close that determines whether the database will automatically be compacted whenever you exit out of it.

Microsoft Office Support - Compact and repair a database