Query Oracle thru Excel VBA 2003 1

[moswitch]

I'd like to query an Oracle database through my VBA code how would I go about doing that in the simplest way possible? I would also like to assign the Where statement value by means of an Input Box, in other words I'd like to pass the value of the Input Box to be the value of the "Where" query statement. Is this possible? I know the server name and the password. I've never queried before your help would be much appreciated.

 

[SkipVought]

Hi,

Are you looking for a single value or multiple rows/columns?

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

 

[moswitch]

Single value for the "Where" statement and multiple rows/columns for the output.

 

[SkipVought]

Here's a sample function, that returns ONE value...

Function GetPastDueRQ(sPN As String, Optional sNG As String = "") As Integer
'SkipVought/2007 Sep 06/
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
':this function returns Past Due Requirements for a given PN and optional NG
'--------------------------------------------------
    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
    
    sSQL = "SELECT Sum(RQQTY_275) "
    sSQL = sSQL & "FROM FRH_MRP.PSK02275_OPEN "
    sSQL = sSQL & "WHERE PARTNO_201 like '" & Trim(sPN) & "'"
    sSQL = sSQL & "  AND RQDATE8_275 <'" & Format(Date, "yyyymmdd") & "'"
    If sNG <> "" Then
        sSQL = sSQL & "  AND NETGRP_275  ='" & sNG & "'"
    End If
    
    Debug.Print sSQL
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
    GetPastDueRQ = rst(0)

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

You could modify to use the CopyFromRecordset method to return rows & columns.

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

 

[moswitch]

Thanks this helps a lot. Regarding my question as to whether or not I can use the Input Box method to assign a value to the Where statement, can it be done?

 

[HarleyQuinn]

Be careful when you're assigning the Where statement if you do decide to set it through an inputbox. If you're not (and your Oracle roles etc. aren't limiting your access) then you can leave yourself open to having some interesting things done to your Oracle DB...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

In regard to your last question, yes it can. If you are using this route and you want to use a function as Skip has you could pass the statement retrieved from the inputbox it in as a parameter and then use it when the SQL statement is built.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[moswitch]

I think I should be fine, I only have read access, basically I'm intending on using the input box as my user interface. Regarding the code Skip wrote, I going to have to study it before I start changing it. I am only trying to return columns, not rows. Thanks all, I learn a lot here.

 

[moswitch]

Gentlemen I am encountering an error (Run-time error Automation error) as I'm attempting to connect to my server. Did I change all the require variables per the code below. By the way I forgot to mention that I trying to extract the content of serveral colums in orcale and importing it into cells(1,1) or sheet1 of my workbook. What changes should I make? Thanks again.

sServer = "MYSERVER"
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=MYSERVER" & ";" & _
"Uid=lrankin/;" & _
"Pwd=xxxxxxxx"

Set rst = New ADODB.Recordset
'Column Name
sSQL = "SELECT BEGINSTATION "
'Table Name
sSQL = sSQL & "FROM RISK.RISK_REPORTING_VW "
'
sSQL = sSQL & "WHERE ROUTEID = '" & Trim(sPN) & "'

 

[SkipVought]

sServer = "MYSERVER"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" &  MYSERVER & ";" & _
               "Uid=lrankin/;" & _
               "Pwd=xxxxxxxx"

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

 

[moswitch]

Still getting the forlloing.
Run-time '2147217843 (80040e4d)':
Automation error

I will continue in my attempt to make this work.

 

[SkipVought]

Please post ALL your code.

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

 

[PHV]

Have a look here:

http://www.connectionstrings.com/oracle

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[moswitch]

Per your request.



Function GetPastDueRQ(sPN As String, Optional sNG As String = "") As Integer
'SkipVought/2007 Sep 06/
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
':this function returns Past Due Requirements for a given PN and optional NG
'--------------------------------------------------
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 = "xxxxxxxx"
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=" & "xxxxxxxx" & ";" & _
"Uid=xxxx/;" & _
"Pwd=xxxxxxxxx"

Set rst = New ADODB.Recordset

'Column to be selected
sSQL = "SELECT BEGINSTATION "
'Table containing Column
sSQL = sSQL & "FROM RISK.RISK_REPORTING_VW "
'Condition
sSQL = sSQL & "WHERE ROUTEID = '" & Trim(sPN) & "'"
'sSQL = sSQL & " AND RQDATE8_275 <'" & Format(Date, "yyyymmdd") & "'"
If sNG <> "" Then
sSQL = sSQL & " AND NETGRP_275 ='" & sNG & "'"
End If

Debug.Print sSQL

rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

rst.MoveFirst
GetPastDueRQ = rst(0)

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing
End Function

 

[SkipVought]

Still getting the forlloing.
Run-time '2147217843 (80040e4d)':
Automation error

and this is the EXACT code that you are using and that is not workgin for you???????

Common, quit wasting time!

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

 

[SkipVought]

1. In Tools > References, set a reference to the Microsoft ActiveX Data Objects m.n Library.

2. If your function does not return an INTEGER, then change the data type for the function.

3. get rid of the code that you do not intend to use.

4.

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

 

[moswitch]

and this is the EXACT code that you are using and that is not workgin for you???????

Common, quit wasting time!

Aside from the x's this is the exact code I'm using. It's kinda fustrating know because when I use the Excel wizard to connect to this server with my user name and password it works but with the code I can't the login script.

Regarding Microsoft ActiveX Data Objects m.n Library I already had it selected before you selected it.

 

[SkipVought]

Can you take the SQL in the Immediate Window and execute it sucessfully?

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

 

[moswitch]

Thanks for the help, it seems I'm going to have to back track here because I don't know how to use the immediate window, I've never had too. Up until now I've been using VBA to manipulate and compute data in Excel as a result importing to Excel from an Oracle database is foreign to me. Although this is difficult for me to do now I’m glad to know that it can be done through VBA and by George I will get.

 

[SkipVought]

View > Immediate Window


it that sinple!

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