Use same connection for multiple recordsets
 

Hi all,

I loop through some cells and each cell value is part of SQL string that I
query database with.

When it loops a second time on IIF clause, a Run-time error of 3021 'Either
BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current reocrd."

Here is the code:
---
Dim adoActiveConn As ADODB.Connection
Dim rngAccount As Range
Dim rsCParty As ADODB.Recordset

Set adoActiveConn = DBConn()

For Each rngAccount In rngEntries
Set rsCParty = New ADODB.Recordset

With rsCParty
.ActiveConnection = adoActiveConn
.Open "Select c.name from MyTable where price = " &
rngAccount.Offset(0, 1).Value)

rngAccount.Offset(0, 2).Value = IIf(.EOF = False, .GetRows,
"Unknown")
.Close
End With
Next rngAccount
---

Thanks for your time.

Cheers,

Use same connection for multiple recordsets
 

IIF unconditionally evaluates the true and the false part even though
it will pick only one. If .GetRows requires that .EOF be false then
IIF won't work for you. Use the more elaborate IF.

If not .EOF then
rngAccount.Offset(0, 2).Value = .GetRows
else
rngAccount.Offset(0, 2).Value = "Unknown"
end if

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "=?
Utf-8?B?VGV0c3V5YSBPZ3VtYQ==?=" <Tetsuya
says...
Hi all,

I loop through some cells and each cell value is part of SQL string that I
query database with.

When it loops a second time on IIF clause, a Run-time error of 3021 'Either
BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current reocrd."

Here is the code:
---
Dim adoActiveConn As ADODB.Connection
Dim rngAccount As Range
Dim rsCParty As ADODB.Recordset

Set adoActiveConn = DBConn()

For Each rngAccount In rngEntries
Set rsCParty = New ADODB.Recordset

With rsCParty
.ActiveConnection = adoActiveConn
.Open "Select c.name from MyTable where price = " &
rngAccount.Offset(0, 1).Value)

rngAccount.Offset(0, 2).Value = IIf(.EOF = False, .GetRows,
"Unknown")
.Close
End With
Next rngAccount
---

Thanks for your time.

Cheers,

Use same connection for multiple recordsets
 

"Tetsuya Oguma" wrote:

Hi all,

I loop through some cells and each cell value is part of SQL string that I
query database with.

When it loops a second time on IIF clause, a Run-time error of 3021 'Either
BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current reocrd."
"Tetsuya Oguma" wrote:

Hi Tetsuya

You need to move to the first record in the recordset.

To test and set the pointer you can use somthing
like the following,

If rsCParty.BOF And rs.RecordCount 0 Then
rsCParty.MoveFirst
or:
rsCParty.MoveFirst "this can fail if recordset is empty"

Good Luck
TK