temporary tables

[sdh]

I would like to be able to make temp tables in access I have used the following SQL but of course with a table name like #temp it fails can somebody advise me how to do this?



Select PCFarrow.Sear as Sowid,bpfarrow.Mtattoolower as MaleL,bpfarrow.Mtattooupper as MaleU,
bpfarrow.Ftattoolower as FemaleL,bpfarrow.Ftattooupper as FemaleU,Pcfarrow.fdate,Pcfarrow.NBA,pcfarrow.nbd,
pcfarrow.mum,bpfarrow.Recordstatus,bpfarrow.unit"
Into #temp
from Pcfarrow left join bpfarrow on pcfarrow.sear=bpfarrow.sear where pcfarrow.unit='value'

 

[tlbroadbent]

You cannot create "temporary tables" in Access as you can in database systems like MS SQL Server. You can create a table and then delete the table. Remember that in so doing you increase the size of the database and deleting doesn't reduce the size. You'll want to compact often if you frequently create and delete objects. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[MichaelRed]

Hoever, except for th "#" part of your dest table name, the statement looks "O.K." for the table creation.

A different approach for Ms. Access would be to just use the plain vanilla select version of the query as a recordset. this is easily done by asinging the statement to the SQL property of an undamed querydef object and instantiating the querydef as a recordset. this is pretty well documented in the Help screens.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over