Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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.

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, VBA

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores