PDA

View Full Version : Lookup data in Oracle database



SQUASH JUNKI
10-21-2008, 02:57 AM
Hi
I am trying to lookup data in an oracle table based on a value in my excel spreadsheet. I am using the DBVLOOKUP function that I found on Google, and am trying to convert the connection string to use an ORACLE connection.
The code I have is as follows...


Dim adoCN As ADODB.Connection
Dim strSQL As String

Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
Set adoCN = New Connection
adoCN.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=myTNSname;User Id=myuser;Password=mypass;"
adoCN.Open

Set adoRS = New ADODB.Recordset
strSQL = "SELECT DISTINCT" & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "=" & LookupValue & ";"

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Value not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close
End Function

The module does not show any errors, the SQL statement is correct, and it does seem to be connecting to the data. However I only get #value showing in my spreadsheet.
The function on the spreadsheet looks like..

=DBVLOOKUP("TableName","LookUpFieldName", $F$23,"ReturnField")
where $F$23 is the cell that is being referenced for lookup in the Oracle DB.

I cant see what's going wrong :wot , if someone could point me in the right direction that would be a great help.

Thx

Bob Phillips
10-21-2008, 03:39 AM
Surely, the column in the db that you are looking up is not really called ReturnField?

SQUASH JUNKI
10-21-2008, 03:46 AM
errr...no.
The VB code (Public Function DBVLookUp....) is to create a function "DBVLookup" which you can in turn use within the worksheet.

Bob Phillips
10-21-2008, 04:00 AM
Yeah but it gets the values in a column that you supply via the ReturnField value, so passing the value ReturnField seesm wrong to me, I would expect something likeName.

Ditto Tablename.

SQUASH JUNKI
10-21-2008, 05:11 AM
When the function is used in the spreadsheet itself, the format is



=DBVLOOKUP("TableName","LookUpFieldName", $F$23,"ReturnField")


but of course these are replaced by the actual table/field names when the function parameters are filled in.

=dbvlookup("dw.customer","sap_number_conversion",F23,"customer_name")