Open Oracle Connection in Excel using VBA 1

[gwoman]

I haven't worked with Excel very much .... and I have inherited an Excel workbook that collects and displays data form an Oracle database (several different tables) using Microsoft query. Currently this process takes an extremely long time because it has to go through a list of items (approx 200) and the way it's set up it creates a new connection for every item in the list. Is it possible in Excel to create one connection and leave it open while it cursors through the list of items .... therefore creating only one connection instead of 200+?

Thanks much ...

gwoman

I posted this in the Microsoft Office Forum ans SkipVought suggested I post it in the VBA Forum.

 

[SkipVought]

Hi,

Does the data from Oracle get into your sheet via a user-defined function?

I need to see the VBA Code that is doing the Open, Query, write, Close. Please post.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gwoman]

Hi ...

The click event on the update button calls the following Sub Update ... and there are 2 other calls within the Sub Update that are listed below as well ...

Thanks

Sub Update()

startpoint = Range("startrow").Value
Sheets("Results").Select

' clear all the data from the current month and start from there to get new data

Range("A" & startpoint & ":G" & startpoint & "").Select
Range("G" & startpoint & "").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H3:K3").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Sheets("Completed Steam Jobs").Select

' get completed job data from the data warehouse, uses beginning of the first
' month without data as a start date


Call Completed_Steam

' find the last row with data and start filling in new data at the next row

maxindex = Range("J1").Value
fillto = maxindex + startpoint - 1

' j is index for the completed steam jobs sheet

j = 2
For i = startpoint To startpoint + maxindex - 1

' get completed job information: start date, end date, recommended volume

Sheets("Completed Steam Jobs").Select
wellname = Range("B" & j & "").Value
leasename = Range("C" & j & "").Value

steamto = Range("D" & j & "").Value
steamfrom = Range("G" & j & "").Value
reqvolume = Range("F" & j & "").Value
jobtype = Range("E" & j & "").Value



Sheets("DSS Steam Data").Select
Range("G2").FormulaR1C1 = steamfrom
Range("G3").FormulaR1C1 = steamto
Range("F2").FormulaR1C1 = wellname

Call steam_from_DSS
actsteam = Range("E2").Value

' paste all the values into the results page

Sheets("Results").Select
Range("A" & i & "").FormulaR1C1 = wellname
Range("B" & i & "").FormulaR1C1 = steamto
Range("C" & i & "").FormulaR1C1 = steamfrom
Range("D" & i & "").FormulaR1C1 = jobtype
Range("E" & i & "").FormulaR1C1 = reqvolume
Range("F" & i & "").FormulaR1C1 = actsteam
Range("G" & i & "").FormulaR1C1 = leasename


j = j + 1
Next i

' fill down the formulas

Range("H2:K2").Select
Selection.AutoFill Destination:=Range("H2:K" & fillto & "")

' refresh the pivot tables

Sheets("Spec Pivot").Select
Range("A4").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("SM outside Spec Pivot").Select
Range("A1").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("Moco Spec Pivot").Select
Range("A1").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh


' Application.ScreenUpdating = True

Sheets("Spec line Chart").Select


End Sub


Sub Completed_Steam()

Sheets("Completed Steam Jobs").Select


startdate = Format(Range("startdate").Value, "yyyy-mm-dd")

Range("A1").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN= my dsn goes here
.CommandText = Array( _
"SELECT DISTINCT DSS_WELL_MASTER.FLD_NME, DSS_WELL_MASTER.NAME, DSS_WELL_MASTER.STRG_NME, CYCLC_STM_REQT_FACT.CMPL_DTTM, CYCLC_STM_REQT_FACT.STRT_DTTM, CYCLC_STM_REQT_FACT.STM_DLVY_TYPE_ID, CYCLC_STM_R" _
, _
"EQT_FACT.TGT_STM_VOL_QTY" & Chr(13) & "" & Chr(10) & "FROM DWRPTG.CMPL_DMN CMPL_DMN, DWRPTG.CYCLC_STM_REQT_FACT CYCLC_STM_REQT_FACT, DSS.DSS_WELL_MASTER DSS_WELL_MASTER" & Chr(13) & "" & Chr(10) & "WHERE CYCLC_STM_REQT_FACT.CMPL_FAC_ID = CMPL_DMN.CMPL_FAC_" _
, _
"ID AND CMPL_DMN.WELL_API_NBR = DSS_WELL_MASTER.WELL_API_NBR AND ((CYCLC_STM_REQT_FACT.CMPL_DTTM>={ts '" & startdate & " 00:00:00'}) AND (DSS_WELL_MASTER.FLD_NME='MWSS'))" & Chr(13) & "" & Chr(10) & "ORDER BY CYCLC_STM_REQT_FAC" _
, "T.CMPL_DTTM ASC")
.Refresh BackgroundQuery:=False
End With
End Sub

Sub steam_from_DSS()

well = Sheets("DSS Steam Data").Range("well").Value
steamto = Format(Sheets("DSS Steam Data").Range("steamto").Value, "yyyy-mm-dd")
steamfrom = Format(Sheets("DSS Steam Data").Range("steamfrom").Value, "yyyy-mm-dd")
Range("A1").Select


With Selection.QueryTable
.Connection = Array(Array( _
"ODBC; my dsn goes here
.CommandText = Array( _
"SELECT distinct CMPL_DMN.CMPL_NME, CMPL_DLY_FACT.EFTV_DTTM, CMPL_DLY_FACT.ALOC_CYCL_STM_INJ_VOL_QTY" & Chr(13) & "" & Chr(10) & "FROM VOLRPTG.CMPL_DLY_FACT CMPL_DLY_FACT, DWRPTG.CMPL_DMN CMPL_DMN" & Chr(13) & "" & Chr(10) & "WHERE CMPL_DLY_FACT.CMPL_FAC_ID = CMPL_D" _
, _
"MN.CMPL_FAC_ID AND ((CMPL_DMN.CMPL_NME='" & well & "') AND (CMPL_DLY_FACT.EFTV_DTTM between {ts '" & steamfrom & " 00:00:00'} And {ts '" & steamto & " 00:00:00'}))" _
)
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

the code you posted makes 2 queries, not 200+.

Where does the 200+ come from?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gwoman]

The user explained to that depending on what Lease it is would determine how many wells are in the results list ... and it makes a new connection for each well that is in the results list as it cursors through them ... the max being over 200 wells.
Is that not what you see happening?

 

[SkipVought]

I guess you did not post the code that loops thru the list.

This can be done using ActiveX Data Objects (ADO).

Before you start your loop, you open a connection, assuming that both queries access the same DB.

Within the loop, you can open 2 resultsets, process each resultset and close the resultsets.

When the loop is done, you close the connection.

Sample Oracle procedure...

Function GetNomen(sPN As String) As String
'SkipVought/2006 Mar 7
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
'this function returns nomenclature for a given part number
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "dwprod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
[b]
 '>>Your loop STARTS here[/b]
    sSQL = "SELECT PM.Nomen_201 "
    sSQL = sSQL & "FROM FRH_MRP.PSK02101 PM "
    sSQL = sSQL & "WHERE PM.PARTNO_201 like '" & Trim(sPN) & "%' "
    
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst

    Do Until rst.EOF
        If err.number=0 Then
            'process your data here
        else
            'problem to clear
            err.clear
        End If
        rst.MoveNext
    Loop

    rst.Close[b]
 '>>Your loop ENDS here[/b]
   cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

You must have a reference set for Microsogt ActiveX Data Objects n.m Library

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gwoman]

I apologize ... I didn't realize I left out the loop code.

This is excellent ... thanks so much!

gwoman