Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
Getting data efficiently
I've written some VBA code that gets data from an Oracle database. So for
each VisitID in the excel sheet starting from C10 it runs a query in a loop and gets the date of visit ( dated) and puts that next to the visitID. heres the code col = "C" row = 10 cell = col & CStr(row) Do While Range(cell) < "" visitID = Range(cell) sql = "select dated " & _ "from visit v, patient p " & _ "where v.patient_id = p.patient_id " & _ "and visit_id = " & visitID rs.Open sql If Not rs.EOF Then Range(cell).Offset(0, 1) = rs("dated") Else Range(cell).Offset(0, 1) = "" End If rs.Close row = row + 1 cell = col & CStr(row) Loop So the same query gets compiled and executed for each visitID. This is slow and there must be a better method to get such data. any ideas appreciated. thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use a pivot table with this efficiently? | Excel Discussion (Misc queries) | |||
Efficiently Generate 88 Chartsheets | Excel Worksheet Functions | |||
How to efficiently declare variables | Excel Discussion (Misc queries) | |||
How to get 500 hyperlink address efficiently? | Excel Discussion (Misc queries) | |||
Group and Count Text Data More Efficiently | Excel Worksheet Functions |