Access 2000 & SQL Server 7.0 (I have also put this question into the SQL Section)
I am using an MDB/MDE as a front end, data is stored in tables in SQL & Access. I want to create a working table behind a from to mangle data in.
I am issuing the code from behind an Access Form 'passing' the table to a Report.
stSQL = "create Table #CallHist (field1 int, field2 text)"
Set cnCurrent = CurrentProject.Connection
Set rsData = New ADODB.Recordset
cnCurrent.Execute stSQL ' Create temporary table
The syntax according to SQL should prefix the table name with a '#', this gives a (ADO) syntax error with the '#'. Without it, it creates the table fine (but of course it permanent then).
If you look at the syntax in the MSAccess Help it suggests that the syntax should be CREATE TEMPORARY TABLE xxxx. I have tried most combinations of #, ## TEMPORARY and so on that I can think of.
So I am stuck here help would be appreciated. (Of course if anyone can tell me how to make a temporary table by not using SQL commands that would be good too.)