Temporary Database

In an earlier blog, I mentioned about writing an article on how to use a temporary database for manipulating data that does not cause the front end to increase in file size. Unfortunately, I got side-tracked and forgot about it until recently when someone reminded me about what I said and requested that I write the article now. I apologize for the long delay and hope that this article and the attached demo will help clarify what I meant back then.

To recap, it is sometimes unavoidable to use temporary tables as a working space to manipulate the data before it can be consumed to represent its final form. However, doing so results in the size of the database file to increase almost exponentially each time this happens. It then becomes necessary to perform a compact and repair at a regular interval to keep the file size to a minimum and prevent reaching the 2GB limit.

As an alternative, the working data can be moved to a separate and temporary database file, so the size of the front end file will not be affected. That is what this demo file will try to illustrate. Please click on the image below to download the file.

Click here to download the demo...



How it works.

The demo contains a table with the data that is required to be manipulated. Instead of creating a temporary table, the demo creates a temporary database in the same folder as the front end file. It then copies the data from the local table into the temporary database. A linked table is then created to establish a connection between the front end and the temporary database. As the linked table is created, a security warning may display. This can be avoided by creating the temporary database in a Trusted Location. When the user closes the database, the temporary database file can be manually deleted. The demo automatically deletes the temporary database file prior to creating a new one.

Please let me know if you have any questions. Thank you.

Original post date: July 31, 2015

Should I use Make-Table or Append Query?

I have been involved in a few discussions over what causes database bloat more when it comes to using temporary tables: using a Make-Table query or just deleting the records and appending new ones?

If you are highly concerned about database bloat (the database file size steadily increases with each use) caused by using temporary tables, then the best approach is to avoid using temporary tables altogether. However, the need to use temporary tables cannot be avoided sometimes. In those cases, there are two main approaches to creating and populating a temporary table.

Make-Table Query

The simplest approach is to use a Make-Table query to dynamically create the temporary table. Of course, you will have to delete the temporary table after you're done with it because Access will throw an error when it tries to create a table that already exists.

Delete and Append Query

The other approach is to create your temporary table's structure beforehand and just populate it at runtime. With this approach, you will first run a Delete query to empty out any remaining data in the temporary table before you execute an Append query to populate the table with the necessary data.

So which one causes more bloat?

I have always thought that creating and recreating database objects over and over would cause database bloat more than just deleting and adding new data. However, some experts swear that it's actually the opposite. So, to settle the score, I have decided to create a little experiment. To see the result of my little experiment, skip to the end. To try it out for yourself, click on the image below to download the demo file I used for this experiment. By the way, the demo version was created using Access 2007.

Click here to download the demo...



Is there a better approach?

Actually, there is... If you regularly use temporary tables in your database projects, you can avoid the issue of database bloat by using a temporary database instead of temporary tables. I will elaborate more on this approach in my next blog; but essentially, you can create a temporary database with all the temporary tables in it and just link to them from within your current database. Once you are done processing the data, you can simply delete the temporary database file since you will create a new one next time you need it.

My test results

Based on my little experiment, I was able to confirm that both methods (make-table and append query) result in database bloat. And what's interesting to me was that both methods produced about the same amount of bloat, as you will see in the results table if you run the experiment yourself. What I did find significant in my experiment was that using a make-table query ran so much faster than using an append query. So, based on this experience, I will probably change my tune and recommend using the make-table approach over the append query.

Additional info

Please note that I used the term "database bloat" very loosely in this article. Some experts do not consider creating records on purpose as database bloat because we all know that adding records is supposed to increase the file size of the Access database.
If you have any comments or questions, please let me know.

Original post date: June 24, 2013