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.

 

[moswitch]

Okay so I've finally got this thing connected to my database. Now how to I modify the Where statement sSQL = sSQL & "WHERE ROUTE_CODE = '" & Trim(sPN) & "'"?
I would like to say Where ROUTE_CODE = 'CAL0002'. Also I'm trying to return the value of my query to sheet1 of my workbook by calling the function from cells(1,1) of that sheet, is this Okay?

Sub GetSub()

GetPastDueRQ (Cells(1, 1))

End Sub

I'm also trying to return multiple columns from my database instead of rows, should I modify anything from your original code to accomplish this task?



Full Code:

Sub GetSub()

GetPastDueRQ (Cells(1, 1))
End Sub


Function GetPastDueRQ(sPN As String, Optional sNG As String = "") As Integer

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 = "sdeloophole"

cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=" & sServer & ";" & _
"Uid=Myusername;" & _
"Pwd=MyPassword"
'M5C9861BL
'CAL0002

Set rst = New ADODB.Recordset

sSQL = "SELECT C_MATRIX "
sSQL = sSQL & "FROM REPORTING_VW "
sSQL = sSQL & "WHERE ROUTE_CODE = '" & 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]

Lets say that you're calling from A1 and the CAL0002 value is in B1...
[tt]
A1: =GetPastDueRQ(B1)
[/tt]
just like any other spreadsheet formula. Or it could be hard coded...
[tt]
A1: =GetPastDueRQ("CAL002")
[/tt]


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

 

[SkipVought]

To complete the picture, lets say that in column B, rows 1 to 3 you have
[tt]
ROW B
1 CAL001
2 CAL002
3 CAL003
[/tt]
and supposing that each of these values in column B, supplied to your function, would each return a corresponding value.

Take my FIRST example
[tt]
A1: =GetPastDueRQ(B1)
[/tt]
and auto fill it down.

Now you have an answer of each of those values, right on your sheet.

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

 

[moswitch]

Great it worked but it's only export one value instead of the whole column and header. By the way please delete my previous post.

 

[SkipVought]

Fo you want multiple values, you must use the CopyFromRecordset method in a Sub procedure.

    rst.MoveFirst[s]
    GetPastDueRQ = rst(0)[/s]
    YourSheetObject.RangeObject.CopyFromRecordset rst

might look something like this...

    rst.MoveFirst[s]
    GetPastDueRQ = rst(0)[/s]
    Sheet2.Cells(1,1).CopyFromRecordset rst

where you would make the call...

Sub GetSub()

GetPastDueRQ Sheet1.Cells(1,2).Value  'this is B1 that contains the value CAL002

End Sub

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

 

[moswitch]

By auto fill I take it you mean in the cell itself? I looking to hard code this whole process.

 

[SkipVought]

Using a User Defined Function (UDF) is not what you are looking for.

You want to return multiple values to a sheet, so the auto fill thing does not apply.

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

 

[moswitch]

Okay this is starting to look even better, I'm now return the values I wanted except for the column headers.

 

[SkipVought]

Function GetPastDueRQ(sPN As String, Optional sNG As String = "") As Integer

   Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection[b]
    Dim fld as ADODB.Field, iCol as integer, lRow as long[/b]
    
    Set cnn = New ADODB.Connection
    
    sServer = "sdeloophole"
    
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
                "Server=" & sServer & ";" & _
                "Uid=Myusername;" & _
                "Pwd=MyPassword"
     'M5C9861BL
     'CAL0002
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT C_MATRIX "
    sSQL = sSQL & "FROM REPORTING_VW "
    sSQL = sSQL & "WHERE ROUTE_CODE = '" & 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
[b]
    On error resume next
   [/b]       
    rst.MoveFirst
[b]
    if err.number = 0 then
      icol=1
      for each fld in rst.fields
         sheet2.cells(1,icol).value = fld.name
         icol=icol+1
      next
      lrow = sheet2.[A1].currentregion.rows.count + 1
      Sheet2.Cells(lrow,1).CopyFromRecordset rst
    else
       err.clear
       msgbox "no rows"
    end if

    on error goto 0
[/b]
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

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

 

[moswitch]

WOW, WOW, IT WORKED! Thanks I've learned a lot.

 

[SkipVought]

Glad to step you thru it.

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

 

[moswitch]

In case you were wondering why I was having a difficult time yesterday, it was because there was a "/" in the User Id portion of the connection string which I did not know to exclude.

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

 

[SkipVought]

Oh yes, I never have to contend with that in our environment as the configuration gets the system info behind the scenes. Sorry.

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

 

[moswitch]

Thanks again.

 

[moswitch]

If I'd like to access my data from an Access database instead of an Oracle database would I need to change the subject function drastically? Or will I just need to change the connection string?

 

[SkipVought]

You will need a connection string for MS Access.

The FROM syntax may change from

Group.Table

to

Group_Table

Your Qracle functions may have to be replaced with Access function...
SUBSTR - MID
CASE - IIF

etc.

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

 

[moswitch]

Okay, thanks I'll try it.