PRB: Can't Read Temporary Table Created Using # Sign in RDO (160168)


The information in this article applies to:

  • Microsoft Visual Basic Learning Edition for Windows 6.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic Control Creation Edition for Windows 5.0
  • Microsoft Visual Basic Learning Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Standard Edition, 32-bit, for Windows 4.0
  • Microsoft Visual Basic Professional Edition, 16-bit, for Windows 4.0
  • Microsoft Visual Basic Professional Edition, 32-bit, for Windows 4.0
  • Microsoft Visual Basic Enterprise Edition, 16-bit, for Windows 4.0
  • Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows 4.0
This article was previously published under Q160168

SUMMARY

When using Remote Data Object (RDO) to create a temporary table with a single pound sign (#) in the SQL statement, it appears that the temporary table is not created in SQL server. When an attempt is made to open this table within the same connection, RDO returns a run-time error.

CAUSE

When an action query is prepared in RDO and passed to SQL server, a stored procedure is created. However, this stored procedure is dropped after it is executed. Therefore, temporary tables created within the action query are destroyed once that stored procedure ends.

RESOLUTION

To work around this behavior, create a global temporary object by using a double pound sign (##). For example: 
   rdoConn.Execute "Select * Into ##Temp1 From Authors"
        Set rs = rdoConn.OpenResultset("Select * from ##Temp1")
Another workaround is to create temporary tables by using stored procedures in SQL server. For implementation details, please see the following article in the Microsoft Knowledge Base:

147938 RDO: Getting Data from Temp Tables Created by Stored Procedure

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start a new project in Visual Basic. Form1 is created by default.
  2. Add a CommandButton, Command1 to Form1.
  3. Paste the following code into the General Declarations section of Form1: 
             Private Sub Command1_Click()

      Dim rdoEnv As rdoEnvironment
      Dim rdoConn As rdoConnection
      Dim strConn As String
      Dim ps As rdoPreparedStatement

      Set rdoEnv = rdoEngine.rdoEnvironments(0)
         rdoEnv.CursorDriver = rdUseOdbc

         '***You need to change the SERVER, UID, and PWD parameters here.
      strConn = "driver={SQL Server};server=myserver;" & _
      "database=pubs;uid=<username>;pwd=<strong password>"
         Set rdoConn = rdoEnv.OpenConnection( _
           dsName:="", _
           Prompt:=rdDriverNoPrompt, _
           ReadOnly:=False, _
           Connect:=strConn)

      rdoConn.Execute "Select * into #Temp1 From Authors"
           Set rs = rdoConn.OpenResultset("Select * from #Temp1")
           Do Until rs.EOF
              Debug.Print rs.rdoColumns(0) & ", " & rs.rdoColumns(1)
              rs.MoveNext
           Loop
           rs.Close
           rdoConn.Close
           rdoEnv.Close

         End Sub
  4. Youmust change UID =<username> and PWD =<strong password> to the correct values before you run this code. Make sure you have an appropriate ODBC data source and that UID has the appropriate permissions to perform this operation on the database. Start the program by pressing the F5 key.
  5. Click the Command1 button to execute the code. You will receive the following error message indicating that table #Temp1 in SQL server cannot be found:
    Run-time Error '400002':
    S0002: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
    '#Temp1'.
Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbprb KB160168
©2004 Microsoft Corporation. All rights reserved.