Querying An Oracle Database In A Function

DonLennon · Jan 6th 2007

Hi All,

I am trying to create an Excel function that can query an oracle database, using the data in a cell in Excel. For example:

I have an oracle database with a table CustDetails containing all customer names and their addresses.

I then have an excel spreadsheet containing various customer names in column B. I want to be able to enter in column C "=GetPostcode("B2"), and then this will pull back the information from the Oracle database using SQL such as

Code

Select PostCode From CustDetails Where CustomerName = [the data fed to function]

Is anyone able to point me in the direction of some code to help with this? My general VBA skills are pretty good, but i just have no knowledge of ADO, and not much SQL.

Any help would be much appreciated.

Thanks,

Don

gollem · Jan 15th 2007

Re: Querying An Oracle Database In A Function

Hi,

example of an ado-connection to oracle:

Code

dim cnn As ADODB.Connection
dim rst As ADODB.Recordset
dim strSQL as string


Set cnn = New ADODB.Connection
cnn.Provider = "msdaora"
cnn.Open "Data source=database.test.com;User ID=test;Password=pwd;"


strSQL = "Select PostCode From CustDetails Where CustomerName = 'Gollem'"    


Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenDynamic, adLockReadOnly
if rst.eof = false then msgbox rst.fields!postcode


rst.close
set rst = nothing 


cnn.close
set cnn = nothing

Display More

Hope this helps.

Mavyak · Jan 23rd 2007

Re: Querying An Oracle Database In A Function

You just made my life a lot easier.

My sincerest thanks! :music:

Querying An Oracle Database In A Function

Participate now!

Share