Re: Temporary table with SQL Server 7.0

The error happened on this line of code :

rst.Open "select * from #tmp1"

I changed it to this one :

rst.Open "select * from #tmp1", cnn

If you checked my original code, I should get 20 records from #tmp1 but I
get no records at all

Here's part of the code :

cnn.Execute "select * into #tmp1 from table1", lngRecords
---->Here lngRecords has a value of 20

Set rst = New ADODB.Recordset
rst.Open "select * from #tmp1", cnn
intTotal = 0
----->Here rst.Recordcount has a value of -1 but should be 20
If rst.RecordCount > 0 Then


"Stephen Howe" wrote:

my goal is that I want to use the content of #tmp1. When I try to access
it
with a recordset, i get the error 3709 :

Unable to use this connection to do this operation. It is closed or not
valid in this context.

Is this from the original code that you posted?
If yes, could you indicate at what line this error is raised?
If no, could you post the code that raised this error and clearly mark the
line that raises this error?

BTW, on your original code, you are _ALWAYS_ better off if ADO methods are
full.
They execute a fraction faster.
This

cnn.Execute "select * into #tmp1 from table1", lngRecords

should be

cnn.Execute "select * into #tmp1 from table1", lngRecords, adCmdText OR
adExecuteNoRecords

and

rst.Open "select * from #tmp1", cnn

should be

rst.Open "select * from #tmp1", cnn, adOpenForwardOnly, adLockReadOnly,
adCmdText

Thanks

Stephen Howe



.

Relevant Pages

  • Re: Temporary table with SQL Server 7.0
    ... valid in this context. ... BTW, on your original code, you are _ALWAYS_ better off if ADO methods are ... rst.Open "select * from #tmp1", cnn, adOpenForwardOnly, adLockReadOnly, ...
    (microsoft.public.data.ado)
  • Re: Temporary table with SQL Server 7.0
    ... rst.Open "select * from #tmp1", cnn ... If you checked my original code, I should get 20 records from #tmp1 ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)