temp files as part of sql string

[Guest]

Is it not possible to create a temp table while running through a procedure?
I want to create a temp table to use as for the next portion of code which
isn't here. I can create a table without any problem but can't create a temp
table.

Private Sub import_Click()

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDb()

strSQL = "SELECT t.id, t.acctnum,(select count(*) from (select distinct
y.acctnum from tbl_Claim_Lines y) z where z.acctnum <=t.acctnum) as claimid"
& _
" into temp from tbl_Claim_Lines t"

dbCurr.Execute strSQL

End Sub

Any thoughts would be appreciated.

 

[Guest]

It is possible to create a table programmatically, but not worth the effort.
It would be just as effective to create the table the way you want it to look
in design mode. When you are ready to use it, delete all the data in it,
then you can do with it what you want.

If it is absolutely necessary to create a new table, like maybe the
structure is not always the same, then look in VBA Help at the CreateTableDef
method.

 

[Guest]

Klatuu,

Thanks for your reply. It works fine with a regular table. I just didnt'
want a table sitting out there in plain view if it is only serving a
temporary purpose within a procedure.

Thanks again

 

[Guest]

The hide it. right click on the table name in the database window. select
properties,and click hidden. Nobody can see it, but you can still use it.

 

[Guest]

It depends what you are trying to do. One common approach
is to link to temp tables in a temp database - which you can
delete after.

Another approach is to wrap your code in a transaction, and
rollback the transaction after displaying or copying the data
out of the transaction.

(david)