Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
Multiple queries under same connection | Excel Discussion (Misc queries) | |||
Saving Recordsets | Excel Programming | |||
Merging two och more recordsets | Excel Programming | |||
ADO Recordsets | Excel Programming |