1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Chris Melville
  • 1,476
  • 1
  • 14
  • 30

3 Answers3

1

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.

Community
  • 1
  • 1
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks for your answer, Parfait (and thanks also for the tip about commenting on another answer below - I must've missed that option. Changed now!) Anyway - to reiterate, yes I know that I can create and manipulate databases entirely independently of the Access application. In VBA/ADO, I am using the Jet engine to create an .mdb (and the Ace engine to create an .accdb). You asked when I witnessed the change in file size: it is at the very instant I try to open it in Access. Yes, I know I don't *need* to use Access, but I want to inspect the tables. No other files do this, Excel or otherwise. – Chris Melville Oct 08 '15 at 19:55
  • 1
    Also, I *do* have to present the user interface in Excel. Not only does Excel do a lot of things that Access doesn't, but on my office network they don't have licences for MS Access anyway! The users have MS Word/Excel/Outlook 2007, but Access is not present. Excel is the only front-end interface possible. – Chris Melville Oct 08 '15 at 20:00
  • I understand. You are basically building a database through code like one would do with the black box MySQL client without phpMyAdmin or SQL Server's Management Studio, or Postgre's phpPgAdmin, or other consoles. MS Access would sort of be the console for Jet/ACE showing you all tables, schema, relationships, and stored queries. Just don't get too caught up with sizes at least during design phase as many things happen under the hood. – Parfait Oct 08 '15 at 20:37
  • 1
    Also, if you as have Access, you can build your app in Access and then have users download the **free** [2007 runtime](http://www.microsoft.com/en-us/download/details.aspx?id=4438) which allows any PC user to use accdb files but cannot design. Also, you can create workbooks, Word docs, PDFs and run entire Excel VBA inside Access VBA. At the end of the day, a spreadsheet is really a flat file end-use document and not a relational database. I worked with many clients who use Excel as an app and eventually it becomes unstable and un-maintainable. I would be remiss if I didn't warn you. Good luck! – Parfait Oct 08 '15 at 20:40
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.

david
  • 2,435
  • 1
  • 21
  • 33
0

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

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Thanks, but no.. that's not the case. As I said, the file immediately reduces in size as soon as it's opened in Access (not just on exit). Also, I thought that the purpose of compacting was to clean up space used by records that had been deleted (therefore leaving blank space that was still marked out as in use). But in this case, the database is freshly created (by ADO) and nothing has been deleted, so there should be no tidying necessary. Any more ideas? :) – Chris Melville Oct 08 '15 at 19:46