problem while creating/acessing temp tables through ADO

Hi,

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:

Code:
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 & " )"

con.Execute 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)

rs.Close

Next count

End SubWhen 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"

help!!!!

.

Relevant Pages

  • problem while creating/acessing temp tables through ADO
    ... I have a project in RDO which works fine but i am now trying to ... Dim con As New ADODB.Connection ... Dim sSql As String ... I am able to insert a value and then select it from the temp ...
    (microsoft.public.vb.database.ado)
  • Re: problem while creating/acessing temp tables through ADO
    ... RDO). ... Dim rs1 As New ADODB.Recordset ... I am able to insert a value and then select it from the temp ... the second time around the select statement fails even ...
    (microsoft.public.data.ado)
  • Re: problem while creating/acessing temp tables through ADO
    ... I have a project in RDO which works fine but i am now trying to ... Dim con As New ADODB.Connection ... The For loop creates first table just ... I am able to insert a value and then select it from the temp ...
    (microsoft.public.data.ado)
  • Re: Scripting with Databases
    ... SELECT sIP, SUM(dblIn), SUM, SUMFROM tblTraffic GROUP BY sIP ORDER BY CINT) ... Dim nRec, nIn, nOut, nTot ... Dim oTS, sTxt, oMTS, oMT, nBytes ... Dim sSQL: sSQL = Join(Array(_ ...
    (microsoft.public.scripting.vbscript)
  • RE: FileSearch to locate the latest (last saved) file
    ... created an array to put all this inrformation so you can perform a sort. ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ...
    (microsoft.public.excel.programming)