Oracle connection module 2

[paulpayne]

Hi,
I am trying to create a generic module to take an SQL statement as an argument and connects to an oracle db, which then returns the resulting recordset back to the calling module for processing. I have managed to create the connection to retrieve a recordset, but I have to repeat the code in every module and I am trying to reduce the amount of code for ease of support. Any ideas????

Thanks
Paul

 

[xlbo]

If this needs to be used in multiple workbooks, you should look at creating it as an add-in (.xla) file.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[paulpayne]

Thanks Geoff, I could look at putting this in an add-in, but I need to first find out how to get the recordset returned from the called module???

 

[PHV]

Can't you have a function returning a recordset and then:
Set yourLocalRS = yourFunction(strSQL)

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

 

[SkipVought]

Hi,

Here's an example of a function that I created that queries an Oracle table and returns a value based on a supplied argument. modify to suite...

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
    
    sSQL = "SELECT PM.Nomen_201 "
    sSQL = sSQL & "FROM FRH_MRP.PSK02101 PM "
    sSQL = sSQL & "WHERE PM.PARTNO_201 like '" & Trim(sPn) & "%' "
    
'    Sheets("sysParms").Range("SQL_Code").Value = sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
    GetNomen = rst("NOMEN_201")

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

Skip,

_[red][/red]

 

[paulpayne]

Thanks for the replies, Skip the code you posted works, but it only returns one field, I need to return a recordset of data from different tables i.e one call could be to the client table and another could be to suppliers table. I am not sure how to return a recordset???

Thanks
Paul

 

[PHV]

I am not sure how to return a recordset
Public Function yourFunction(your parameters list here) AS Recordset
...
Set yourFunction = yourRecordset
End Function

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

 

[SkipVought]

As I stated, "MODITY to suite".

Declare the function as a Recordset as PHV has been stating all along.

Skip,

_[red][/red]

 

[lmbayley]

I did something similar to the function that Skip posted, except that my stuff is all in a class. I created a method to open a recordset that takes the SQL string as a parameter, a method to open the connection, and so on. My methods that return a bunch of different values involve public variables, but I'm pretty sure that's not the most efficient method. I'll have to try that "as Recordset" idea that PHV posted.

Once you create a class to hold the methods to connect with and deal with your database, you can just import this into whatever project you're working on and modify as required.

-LMB

 

[paulpayne]

Thanks for all the replies, I am will get this working in the module first, then I will add it to either a class or an add-in. Thanks for your help.

PP