Re: problem while creating/acessing temp tables through ADO

Ritesh Sharma wrote:
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.


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?


I'm thinking the problem was related to the "Dim...As New ..."
statement. Because of that declaration, the recordset never got
de-referenced between passes through the loop, and, since the connection
was still "busy" with the prior resultset, a new implicit connection
would be created when attempting to open the recordset on the second
temp table. Of course, that temp table is now out of scope of the new
connection. I would suggest using SQL Profiler to verify this behavior.

Regarding the lack of "control" over the recordset properties due to
using Execute rather than Open: you are certainly correct. Unless
adExecuteNoRecords is specified, Execute forces the creation of a new
recordset object using the CursorLocation setting of the Connection
object as well as the default settings for CursorType and LockType.

If all you want is a server-side, forward-only cursor, then Execute is
probably the way to go.
If you want a client-side static cursor, you can still get one by
setting the connection's cursorlocation to aduseClient and using
Execute, although I would be inclined to instantiate my own recordset
object and use Open.
If you want any other cursor or lock type, then you need to use Open.

The success of my suggestion was not due to using Execute: I'm thinking
it was more due to eliminating "Dim...As New.." You should certainly try
using

Set rs = New ADODB.Recordset
rs.Open
....
rs.Close: Set rs=Nothing

within the loop to verify my theory.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: ADO Performance
    ... sections (this will vary a little depending on cursor location): ... Find out how long it takes to run the query and get the recordset ... place some logic on the server side to reduce the execution time. ... loop with oRS.MoveNext command. ...
    (microsoft.public.vb.general.discussion)
  • Re: using adOpenDynamic
    ... Are you sure a default forward-only cursor won't do the job for ... Set objRS = CreateObject ... Loop ... response.write "Recordset was empty" ...
    (microsoft.public.inetserver.asp.db)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Engpass ist vor allem das LAN und der Server selbst. ... ob man mit einem SQL-Server oder der Jet-Engine ... Cursor und statischen Recordsets, egal welches Datenbanksystem ... dass eine Bewegung im Recordset eben auch ...
    (microsoft.public.de.vb.datenbank)
  • RE: Please help with a query/recordset operation
    ... Once access let me execute a simple do - until loop. ... "nick" wrote: ... >> do this is to loop through the recordset. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Gebundene Controls aus ADO-Recordset aktualisieren
    ... Wozu ein serverseitiger Cursor? ... Ich kann also z.B. bei adOpenKeyset im Programmcode ... CursorLocation adUseServer bei Access sinnlos, ... wenn Du in Deinem Recordset zu einem anderen ...
    (microsoft.public.de.vb.datenbank)