Can't create Temporary table....

Chatbox

Registered User.
Local time
Yesterday, 22:14
Joined
Aug 30, 2004
Messages
68
I have a command button with the onclick() action as below

Dim curDB As Database
Set curDB = CurrentDb
curDB.Execute "CREATE TEMPORARY TABLE test10 (ID INT)"
curDB.Execute "Insert into test10 (ID) values ('001')"
curDB.Close

The create statement gives me an error....
How do I create a temporary table?
 
Temp Table?

;) No temp table in Access that I know about... closest thing is a query.

To create a non-temp table you can:

Public Function CreateTable()
Dim curDB As Database
Dim strSQL As String
On Error Resume Next

DoCmd.SetWarnings False

Set curDB = CurrentDb

strSQL = "SELECT '0001' AS ID INTO TempTable;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

curDB.Close

End Function :cool:
 
Thanks for your reply...I found that out just moments before I got your reply.

Here's my new situation:
Access now only serves as a frontend, the backend is a MS SQL 2000 server.

My tables had been upsized and linked to the SQL server.

However, how do I specify VBA code to create a temporary table in/on the SQL 2000 server? I looked up Opendatabase() with the use of ODBC string, Opendatabase() executed without error, but when I then try db.execute('CREATE Temporary table (ID int)'), it then thrown me an error (can't remember what). I then ran the CREATE statement on the SQL server (via SQL's Query Analyzer), and it ran fine.

Any suggestions?
 
Not sure why this needs to be created from the front end...

Not sure how you might do that, or if you can do that from Access with VBA.

Might want to explore a pass-through Query...

Maybe :o it would be better to do it at the server.

To create a temp table in SQL:

SELECT
field1
, field2
, field3

INTO
#temptable1 'or ##temptable1 if global

FROM
tblOrig

GO

Another suggestion is to create a View which can also be linked within Access.
 
Your create query MUST be run as a pass-through query if you want it to create a table in your SQL db. Its syntax MUST be compatable with SQL server rather than Jet if there is a difference.
 

Users who are viewing this thread

Back
Top Bottom