Select records into a tempory table 1

[TooNew]

I'm running Access 2002. In VBA how do I select records into a temorary table to use in the next sql query instead of creating an actual table that will need to be deleted? I saw in one of my searchs that using the "#" as the first character of the table name will do this, but I get an error.

strSQL = "SELECT [PR DC Alloc Hrs].[Employee Number], '000001' AS DC INTO #EMP FROM [PR DC Alloc Hrs] " & _
"GROUP BY [PR DC Alloc Hrs].[Employee Number], '000001';"
DoCmd.RunSQL strSQL

Thanks for any help.

 

[lestatdelioncourt]

Th simple way is to create a recordset in memory.

Here is an example using your SQL:

Function getSysVal(sysVal As String) As Variant
' This routine returns any system value specified

Dim strsql As String

strsql = "SELECT [PR DC Alloc Hrs].[Employee Number], '000001' AS DC FROM [PR DC Alloc Hrs] " & _
"GROUP BY [PR DC Alloc Hrs].[Employee Number], '000001';"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strsql)

With rst
.MoveFirst
While .EOF = False
'here is where you use the results
' for example if you want the value of the
Employee Number field
variable = rst.Fields(0)
' or use rst.Fields("Employee Number&quot onstead of rst.Fields(0)
.MoveNext
Wend
End With
Set rst = Nothing

End Function