Connect to Oracle Database through ODBC with VBA
 

Hi

Could anyone give me some pointers as to how I can, through VBA,
connect to an oracle database, run a query on the database, and then
return one column of the results from the query to populate a listbox
on a VBA Userform in an Excel Spreadsheet?

Can I use DAO to connect to the database? and somehow place the
queryresults into a recordset which can then be placed into the
listbox.

Any help would be be appreciated.

Regards

J

Connect to Oracle Database through ODBC with VBA
 

Cnn="DSN=mysysdsn;UID=mydb;PWD=mypwd"
Sql="Select distinct(mycol) as mycol from mytable
Set ADORS=CreateObject("ADODB.RecordSet"
ADORS.Open Sql,Cn

MyValues = ADORS.GetRow

MyValues now is a array (0 based): you can loop through lbound(myValues) to ubound(myValues) to populate the list box

NOTE: The Cnn and Sql strings will be specific to your PC/Database: change these as appropriate. If you needed to, you can use the MSDAORA provider instead of the DSN.