Creating and using Temporary Tables in Microsoft Access

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic.

I don’t intend to describe all of the uses of temporary tables in this article, but I would like to offer up a function I wrote several years ago as an efficient way to create temporary tables (either in the application front-end or in an external database, linked to the application front end).

Reasons for temporary tables

There are a number of good reasons for using temporary tables in your application, but the five that were important to me at the time I wrote this function were:

1.  Speed up queries.  This also includes cases where I wanted to replace the use of the inherently slow IN ( ) clause in a SQL query.

2.  Reduce network traffic. Pulling information that rarely changes across the network multiple times during a session just slows things down.  I do this for lookup tables that contain data that rarely change but which I use in combo boxes and lists.

3.  Stage data generated by long running queries. When you have multiple operations that use the same or similar information, and the query that generates that data takes a while to run, it is useful to stage that data in a local table.  You can update this information at regular intervals if need be.
 
4.  Provide user specific options for selecting various form and report display options.  When you want to provide a way for multiple users to make selections from a table simultaneously, you can either add a userID to the back-end table or you can stage this data in a temp table.

5.  Stage data being imported from an external source.  Whenever I'm asked to create a capability to import data from an external data source, I almost always create a staging table for the data.  This allows me to run some error checking on the data and provide my users with a way to correct errors before importing the data into my production tables.  It also allows me to do explicit data type conversions to guarantee the data that I'm importing into my production tables looks like I want it to.
When I wrote this function I was using an Access front-end and an Access back-end, and only users at the client's main site were using the application.  It wasn't long before my client decided that they wanted multiple users from throughout the United States to be able to use the application, but they were unable to provide me with a SQL Server backend for the migration.  They already had a license for and were using SharePoint (MOSS 2007) for collaboration within their organization, and Microsoft was touting the ability of Office 2007 to connect Access to SharePoint, so we decided to give it a try.  The application screamed on all of the clients local systems, and ran reasonably well at over thirty other sites spread throughout the country.  However, several of the sites had serious bandwidth issues which affected all Internet traffic and some of the queries that ran in under 10 seconds at the client's main site took up to five minutes to run at these bandwidth challenged sites, making the application unusable at those sites. So, I had to find a way to speed up the application, and using local temporary tables seemed like a good place to start.  Ultimately, the use of temp tables worked well in that application.

Methods for creating and using temporary tables

I have seen several different methods of creating and using temporary tables described in the questions and answers here on EE; I will describe three.

1.  Some experts recommend adding a permanent table to the applications front-end.  These tables generally need to be updated regularly, which may involve inserting and deleting records, causing the database size to grow over time.  To overcome this bloat, you either have implement a method that will compact and repair the database on a regular basis, or use the CompactOnClose functionality provided by Microsoft.  At the time I wrote this function, there was a warning out about not using the CompactOnClose functionality of Access 2007, and I've avoided it ever since, although I am fully aware that Microsoft published a hot-fix which resolved this issue a long time ago.

2.  Others recommend creating temporary tables in the front end, but only when you need them.  Similar to method #1, this method also causes database bloat, but at an even faster rate since the same table may be created and deleted numerous times within a single session.  I used this method for many years as it just seemed "wrong" to have completely empty tables sitting in the front-end of my applications.  It was also easier to write Make-Table queries, where the original tables primary keys would persist, than to write Append queries.  I stopped using this method about the same time that the warning regarding using CompactOnClose was announced, but I did eventually add that capability back into my UpdateTempTables function.

3.  Since I didn't want to use CompactOnClose frequently, I was left with one final option, create the tables when they are needed in another database, and link those tables into the front-end.  This method gave me the ability to create the tables when they were needed, delete them when they were no longer needed, and avoided the bloat caused by the first two methods.  Clean-up is easy; I already have a routine which drops the links to my back-end database, so I simply extended that functionality to my temp tables.  Then, it is just a matter of killing the temporary database when I close my main application.
When I originally wrote this  function, I designed it with this last option in mind, but eventually modified it to allow for method #2 as well.  You can call this function and receive a Boolean response which indicates whether the operation was successful.  Or, you can simply call the function, without assigning the return value to a variable, and assume the function was successful.

I hope you find the article and the function useful in creating and working with your own temporary tables. TempTables.accdb

Function syntax and argument description

bVariable = UpdateTempTable(TableName, TableQueryOrSQL , [InCurrentDB], [ValidMinutes], [PK Field])

TableName: Required, string. This is the name of the table that will be created by the function.

TableQueryOrSQL: This argument will accept the name of a table, query, or a SQL SELECT query string (one that contains the term “SELECT” as the first word of the query and DOES NOT include the term “INTO”).  You cannot use a make-table or an append query as the source for your temp table.

InCurrentDB: Optional, boolean, default = False.  The value of this argument determines whether the table gets created in the front-end (True) or in an external database (False).  If False, the external database will be created on the same path as the front-end and have the same file name as the front-end but have the additional “_temp” at the end of the file name.  
Example front-end filename: C:\Temp\myDb.accdb
Example temp table filename: C:\Temp\myDb_Temp.accdb

ValidMinutes: Optional, integer, default = 0.  Determines how many minutes the temporary table is valid for.  A value of zero indicates that the table should overwritten if it already exists.  Any other value will cause the function to check the DateCreated property of the table if it already exists and if the number of minutes since the table was created is less than the ValidMinutes argument, the function exits without updating the table.

PK Field: Optional, Default = NULL.  Will accept a single field and will use an “ALTER COLUMN” DDL query to set this field as the Primary key for the temp table.
14
42,088 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (9)

thanks fyed - really good article. i never thought of option 3 - but it makes complete sense now = ) thanks once again!

Commented:
I have been using the UpdateTempTable function for almost a year now.  It has been working great!  A co-worker of mine had been using it too but recently had their laptop upgraded to windows 10 (I run 7).  She keeps getting the 3035 system resources exceeded error whenever this function runs now.  We can't figure out what the problem is.  Our laptops are equivalent in installed memory (16G).  Any suggestions?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Mark,

Glad that you have enjoyed using it.

I have not updated to Windows 10 on any of my work computers, but will look into this on another computer I have access to.

Dale

Commented:
I was able to get around it by increasing the record lock limit on the windows 10 laptop.  I inserted this line of code right after the On Error statement in your function:

DAO.DBEngine.SetOption dbMaxLocksPerFile, 50000

Apparently that temporarily ups the limit from 9500 just during the execution of the function.  There are several tables made, and the process runs about 1/2 the speed it does on my laptop.  We are both using Lenovo T450 laptops - same processor etc. just different Windows versions.  Well it least I got it to work.  Let me know if you discover anything.
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<50000>>

  Bump that to 500,000....even 50,000 is way too low.

Jim.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.