*Update*: For a similar discussion using SQL Server tables please click here.

I use temp tables all of the time in my code, usually when I need to perform a multi-step analysis that can’t be done with just one query. This article will explain some tips on how to use temp tables in your application.

I don’t use a temporary database
For purists out there I want to head you off now, I agree with you that temp tables should be in their own database, ideally. I don’t do it that way because a) I need a quick and dirty solution and I just don’t have the patience in doing multi-database joins or linking and b) It makes my code needlessly complex. Will it bloat my Microsoft Access database? Yes, nothing a little compact and repair can’t fix.

Learn to use SQL in your code

The techniques in this post all have to do with using SQL in your code, if you havn’t learned now is the perfect time. There are two reasons why you should execute SQL in your code rather than using queries: Queries can be deleted/changed by you and your users and it’s easier to manage and debug your code. For a more stable environment I recommend placing your action queries in your code, thus keeping functionality and related items grouped together. No one wants to stop reading your code to go hunting for a query located somewhere else.

Temp Tables are just like any other Table

I call them temp tables because they are not permanent to my application, but to Access the’re just another table. There is no special designation or storage location in Access for temp tables. I name my tables tblTempName but in theory you can use any naming convention.

I also advocate deleting your temp tables and recreating them to avoid issues with users changing or removing them from your app.

Use INTO to create table in SQL
I create temp tables by using SQL in my code and the keyword INTO, which will output the query results into a temporary table in my database. Here’s an example code block:
Private Sub TestTemp()
On Error GoTo ErrorHandler
Dim strSQL as String
Dim strTable as String
strTable = "tblTempTest"
'Delete the table if it exists
DoCmd.DeleteObject  acTable, strTable
strSQL = "Select * INTO " & strTable & " FROM tblCustomers " & _
"Where CustomerState = 'ILL'"
Currentdb.Execute strSQL
'Insert more code here to do something with temp table
Exit Sub
ErrorHandler:
IF Err.Number = 7874 Then
Resume Next 'Tried to delete a non-existing table, resume
End If
End Sub
Adding New Columns

Sometimes you need to add a temporary column or change the formatting on a column in your temp table. You can add temporary columns by using constants in your SQL code, for example:

strSQL = “Select *, True as SelectedCustomer INTO tblTempTest From tblCustomers”

The query engine will then add a new column called SelectedCustomer and a value of True for all records. It works but I don’t like the judgement call it may do in picking your column type. Better to use the Alter statement instead:

ALTER your approach
Use the ALTER keyword to add a primary key to your temp table, add new columns or change the column type on a existing column:
   strSQL = "ALTER TABLE " & strTable & " ADD CONSTRAINT KeyName PRIMARY KEY(ID)"
strSQL = "ALTER " & strTable & " ADD COLUMN TotalDollars Currency"
strSQL = "ALTER " & strTable & " ALTER COLUMN TotalDollars Currency"
In conclusion, use SQL in your code instead of queries that may get altered or deleted; use temp tables to store intermediate results and learn to modify them on the fly for optimum performance by adding indexes and keys.