Pulling data from Oracle tables

[danny]

I have a list of about 300 unique ID's in an excel worksheet. What I want to do is pull all the related records (with all the associated fields) out of an ORACLE database. The Oracle database has about 250000 records so pulling all the records and then picking out the records I need will grind the machine to a halt, and using the exceptions and entering each unique ID seperately is very labourous as well. Is there an easy way to pull the records listed in the excel file quickly and easily?

BTW, I'm using Excel 2002

 

[DonkeyOte]

Not tested BUT DSN is ODBC connection name. Prefer to connect using DSN as opposed to lengthy connection string. Choice is yours.

Also - this requires the ActiveX Data Objects Library (highest number) to be referenced in VBE References.

Sub GET_DETAILS()

Dim Conn as ADODB.Connection
Set Conn = New ADODB.Connection

Conn.Open "DSN=ORACLE_DB"

Sheets("sheet1").Select
n = 1
Do Until n > 300
      Dim RS as ADODB.Recordset
      SQL = "Select * From Oracle Table where ID = '" & Cells(n,1) & "'"
      RS = Conn.Execute(SQL)
      If RS.EOF = False then
            Cells(n,2).CopyFromRecordset RS
      End If
      RS.Close
n = n + 1
Loop

Conn.Close
End Sub

 

[jpettit]

Could you elabotate slightly?

1. "DSN=ORACLE_DB" This is the DSN ODBC Connection name? (i.e. i could have "Remedy", or would i need "Remedy=ORACLE_DB"?

2. ActiveX Data Objects Library? Is this an installed Add-in? or how do i enable it (reference it)?

ahhh...just found this webpage:
http://msdn.microsoft.com/library/d...gdatasourceusingactivexdataobjectslibrary.asp

I'll start here.
Thanks.