PRB: Can Not Create temp Table Using ODBCdirect

RESOLUTION

If you need to refer to a temporary table created with ODBCdirect, send the SQL statement in one transaction statement or use the <dbExecDirect> option with ODBCDirect Connection and QueryDef objects.

Note You must change UID <username> and PWD <strong password> to the correct values before you run the following code snippets. Make sure that UID has the appropriate permissions to perform this operation on the database.

1. Create an ODBC DSN called <Pubs> with the ODBC32 administrator.
2. Open Visual Basic and create a new Standard.exe project.
3. On the Project menu, click References, and select the Microsoft DAO 3.5x Object Library.
4. In the <Form-Load> event, paste and run the following code:

         Dim ws As Workspace
         Dim cn As Connection
         Dim strConnect as string
         Dim strSQL as string
   
         Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
         Set cn = ws.OpenConnection("", , ,
                  "ODBC;DSN=Pubs;Database=Pubs;UID=<username>;PWD=<strong password>")
   
         strSQL = "CREATE TABLE #test (field1 VARCHAR(20) NULL)" & vbCrLf & _
            "CREATE TABLE TestTemp(field1 VARCHAR(20) NULL)" & vbCrLf & _
           "INSERT INTO #test(field1) SELECT fname FROM Employee" & vbCrLf & _
            "INSERT INTO TestTemp(field1) SELECT field1 FROM #test"
   
         cn.Execute strSQL
   						

5. Start ISQL_W and run the following statement:

         SELECT * FROM TestTemp
         DROP TABLE TestTemp
   						

RESULTS: You should see rows returned in the results windows. If the #temp table was not created in step 4, TestTemp would not exist.

This example demonstrates that the #temp table is created during step 4 but pausing/stopping the code at any point does not reveal that fact. The SQL statement is being executed and the connection is closed immediately, so you do not see the details within ISQL_W or Visual Basic.

NOTE: The <DROP TABLE> statement deletes the table from the database so you may run the example again.

To refer to the temporary table at some other point in the code, use the <dbExecDirect> option with the Connection or QueryDef object as in the following example.

Follow steps 1 through 3 as in the previous example. Then paste the following code in the <Form-Load> event and run the project:

   Dim ws As Workspace
   Dim cn As Connection
   Dim strConnect as string
   Dim strSQL as string

   Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
   Set cn = ws.OpenConnection("", , ,
            "ODBC;DSN=Pubs;Database=Pubs;UID=<username>;PWD=<strong password>")

   strSQL = "CREATE TABLE #test (field1 VARCHAR(20) NULL)" & vbCrLf & _
            "INSERT INTO #test(field1) SELECT fname FROM Employee"
   cn.Execute strSQL, dbExecDirect

   strSQL = "CREATE TABLE TestTemp(field1 VARCHAR(20) NULL)" & vbCrLf & _
            "INSERT INTO TestTemp(field1) SELECT field1 FROM #test"
   cn.Execute strSQL, dbExecDirect
				

You should see rows returned in the results windows. This demonstrates that the temporary table was created and its scope was not limited to a prepared statement (temporary stored procedure) but the database connection.