Re: problem while creating/acessing temp tables through ADO

Ritesh Sharma wrote:
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 ... As New is bad practice which may be contributing to your problem.
Switch to Dim ... As ... followed by Set ... = New ...

Dim sSql As String
Dim count As Integer

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

Very strange: you instantiated the objects with your Dim statements and then
re-instantiated them here. That makes your task even easier: simply remove
the "New" keyword from your Dim statements.

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"

Hmm, the first thing I would try is eliminating the "Set rs ... " statement
entirely (keep the "Dim rs As ADODB.Recordset" statement) and using this to
open your recordset:

Set rs = con.Execute("select Id from #tmp" & count,,adCmdText)

Add a Set rs=Nothing line to your loop:

rs.Close: Set rs = Nothing

If that does not help, I'm afraid I have grasped my last straw. If it does
help, let me know and I'll explain what my theory is.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Man in the middle
    ... If dVar1> dVar2 Then ... temp = dVar2 ... Create in inner loop that loops from 0 to the value of the outer loop ... Dim dVar1 as Double ...
    (microsoft.public.excel.programming)
  • Re: Help !, to find last text date in text string
    ... In an effort to loop through my cells containing the date values, ... Dim Temp As Integer ... Dim LastDate As Date ...
    (microsoft.public.excel.programming)
  • 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: ADODB to RDO
    ... Dim rsMy As ADODB.Recordset ... 'Open the RDO Resultset ... 'Loop through each record in the MySQL table and copy it to RDO ... I have an ADODB table that I need to copy into an RDO table. ...
    (microsoft.public.vb.database.ado)
  • CR9, RDC and RDO recordset => Crash
    ... All tutorials and documentation i found refers to RDO ... and ADO, but examples are only with ADO. ... Create a simple report file report.rpt based on the above TTX. ... | Dim conn As ADODB.Connection ...
    (microsoft.public.vb.crystal)