Thread
:
Use same connection for multiple recordsets
View Single Post
#
2
Posted to microsoft.public.excel.programming
Tushar Mehta
external usenet poster
Posts: 1,071
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,
Reply With Quote
Tushar Mehta
View Public Profile
Find all posts by Tushar Mehta