Re: problem while creating/acessing temp tables through ADO

On May 15, 6:38 pm, "Bob Barrows [MVP]" <reb01...@xxxxxxxxxxxxxxx>
wrote:
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"- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

hey!

that works!!!!!!!!!!!!!!!

i believe it has something to do with "Set rs = nothing".....let's
hear your theory :-))

also, "Set rs = con.Execute ......" doesn't give me much control over
my resultset. how can i overcome that limitation?

thanx again!

.

Relevant Pages

  • 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)
  • RE: FileSearch to locate the latest (last saved) file
    ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ... For i = 1 To NewestFile ...
    (microsoft.public.excel.programming)
  • RE: FileSearch to locate the latest (last saved) file
    ... Dim sReport As Workbook, sDashboard As Workbook ... Dim fLdr As String, Fil As String, FPath As String, x As String, _ ... FileDates= temp ... For i = 1 To NewestFile ...
    (microsoft.public.excel.programming)
  • Re: Checkboxes are duplicated over and over as emf in the Temp fol
    ... The filesystemobject has a folder.delete method, but if any file in a folder ... Dim cFilesToDelete As Collection ... you are doing it seems you can stay focused in the temp root. ... I created an app with some checkboxes. ...
    (microsoft.public.excel.programming)
  • Re: Problems with Bubble Sort on 2D Array
    ... Function BubbleSort2D(PassedArray As Variant, col As Long) ... Dim i As Integer, j As Integer, k As Integer ... Dim Temp As Variant ... ' Sorts an array using bubble sort algorithm in descending order using ...
    (microsoft.public.excel.programming)