Go to Top

Use temp tables in your code for quick and easy analysis

*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 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:

 

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:

 

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

7 Responses to "Use temp tables in your code for quick and easy analysis"

  • J Swope
    August 24, 2016 - 4:00 am Reply

    Can someone help me to understand how to modify the code to insert it into my access application? What in the code represents the “new” temp table created, what represents the table from which data is extracted, and so on? Sorry if this seems simple to others. I am new to this. trying to use this to create a temp table as a work around for the issue related to using a linked CSV and not being able to edit queries created with it.

  • Shannon D
    April 4, 2016 - 5:01 pm Reply

    So I have queries saved in my Dbase that perform mathematical functions. The query is then the source for a report.

    Do you recommend I copy the SQL from there huge query into vba and run it into a temp table?

    • Juan Soto
      April 11, 2016 - 12:10 am Reply

      Hi Shannon,

      In general, I only recommend using this technique if it will speed up the process, that is the only advantage, further, creating and deleting temp tables will mean your system will bloat over time unless you do regular company and repairs.

      Thanks for being a blog reader!

  • Sukmini Kombi
    February 3, 2015 - 7:38 am Reply

    Thank you very much. The code that you wrote answered my question. I am using it to add in more codes

  • Rx_
    September 27, 2013 - 2:40 am Reply

    Thanks, I am using this as an explanation for someone else. His question was how his database really compresses (like into half).
    Your last paragraph might be related to his question.

    Often, I see temp tables as now well formulated. The text fields might be the default (max size). After all, Temp tables as you say are to grab info quickly. Not to organize it efficiently for the long term.
    So, if there were for example 20 temp tables of 1 MB each, someone running the Repair / Compress might just see a big reduction. It is not big deal. It is just the reason.

    Temp Table… Like me taking out the tools I need from my toolbox and putting them on the workbench. It is easier to use, but not necessarily that organized.

  • laura
    June 28, 2013 - 9:19 pm Reply

    Hi,

    I’ve included the code above into my code and it works really well but I also have a for loop in my code…and I cannot keep recreating the same table for different values. Especially since I want to include all the values going through the for loop into the same table. I guess my question would be: How do I execute strSQL and return its values in table strTable but without recreating strTable every single time my code goes into a for loop? Thank you,Laura

  • Viktor Gaidai
    May 29, 2013 - 7:53 pm Reply

    Thanks very much for the very convenient code!
    v.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.