Re: Temporary table with SQL Server 7.0

Here rst.Recordcount has a value of -1 but should be 20
If rst.RecordCount > 0 Then

RecordCount will _NEVER_ be 20.
It will _ALWAYS_ be -1
Did you read what was responded and why that is?

It is because

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

is the same as

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

because the Recordset is defaulting to server-sided.

Several choices:

1. Add
rst.CursorLocation = adUseClient
before rst.Open
You can then replace the loop with
intTotal = rst.RecordCount
Note: It is now a client-sided cursor and all cursortypes are adOpenStatic
which _GUARANTEES_ RecordCount is not -1.

2. Change the existing loop to

Set rst = New ADODB.Recordset
rst.Open "select * from #tmp1", cnn
intTotal = 0
Do While Not rst.EOF
intTotal = intTotal + 1
rst.MoveNext
Loop

3. Do what Bob says. Best practice is to get the SQL engine to do the
counting for you.
Far faster than messing around with recordsets. Less error-prone as well.

Stephen Howe


.

Relevant Pages