Unanswered: problem while creating/acessing temp tables through ADO
I have a project in RDO which works fine but i am now trying to upgrade it to use ADO. However, i am facing problem while accessing/creating temp tables in the project (same code which works fine with RDO). I have identified the code where the problem occurs and have created a sample code which replicates the problem.
I am using Sybase databse with Sybase ASE driver 5.0 and MDAC 2.8 on win XP
The code is as follows:
Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim sSql As String
Dim count As Integer
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Password=pass;DSN=MyServer"
For count = 1 To 4
sSql = "CREATE TABLE #tmp" & count
sSql = sSql & " ("
sSql = sSql & " Id int"
sSql = sSql & " )"
sSql = "Insert into #tmp" & count
sSql = sSql & " Values (" & count & ")"
con.Execute sSql 'THIS IS WHERE THE PROBLEM OCCURS IN SECOND LOOP
rs.Open "select Id from #tmp" & count, con, adOpenStatic
MsgBox "Table: #tmp" & count & Chr(13) & "Id: " & rs.Fields(0)
'Do some updates
sSql = "update #tmp1 set Id = 100"
rs.Open "select Id from #tmp1", con
MsgBox "Update Table #tmp1" & Chr(13) & "Now Id: " & rs.Fields(0)
'Try to update the Id does not exixts
sSql = "update #tmp2 set Id = 200 where Id = 1"
rs.Open "select Id from #tmp2", con
MsgBox "Update Table #tmp2" & Chr(13) & "Now Id: " & rs.Fields(0) & Chr(13) & "Should be same as before"
When i run this code. The For loop creates first table just fine. I am able to insert a value and then select it from the temp table. However, the second time around the select statement fails even though the second create temp table statement does not throw any error. The error i get is "#tmp2 could not be found"
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert