Correct syntax for a temporary table in Access 2007

[SimonG via AccessMonster.com]

Hi

I'm trying to use a temporary table in an Access 2007 procedure (the
temporary table is only required during the life of the procedure).

Currently I am using a command in this format:

SELECT
fieldnames
INTO
#TempTableName
FROM
tablerelationship
WHERE
conditions
;

This is assigned to a variable, strSQL, and executed via
CurrentDb.Execute strSQL


When the code is ran, Access returns an error about the inclusion of a
reserved word.
If I remove the # from the start of the temporary table name the code
completes without error; although it does create a permanent table within the
database.

Can someone advise me where my syntax is going wrong (I have tried several
variations, but nothing that has worked to-date)?

Many thanks,
Simon

 

[Dale Fye]

Simon,

I'm not aware of a way to create a true Temp table (ala SQL Server) in Access.

I generally like to create these tables in an external temporary database,
then link the table to my application. That way, it does not bloat my
application.

Take a look at the CreateDatabase method to create the temp database (I
generally do this when the application loads, and put it in the applications
currentpath). The use the format:

SELECT fieldnames
INTO TableName IN CurrentPath & "\TempDbName.mdb"
FROM tablerelationship
WHERE conditions

Then use the docmd.TransferDatabase command to link the table to your
application.

The other option, and I use this whenever I have several temptables, is to
just create the TempDb the first time the application is run, build the
tables programmatically, and then link them to my application. Then, each
time I close the application, I delete the contents of all the temp tables
and compact the TempDb.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[JoshD]

You can not start a name with other than alpha-numeric character.
Do your process without the '#' which will create a real table and then
delete it in code.

another solution is to create a query that creates a recordset which serves
as a base for the other process to get values from.