Excel and SQL....

[jag108]

I was wondering if it is possible to populate a drop down box, from data retrieved by SQL?

 

[dk]

Yes it is. Create a recordset using the method of your choice (I'd use ADO or DAO), loop through the recordset and use the AddItem method of the ComboBox e.g. here's an example that uses ADO to connect to an Access database. You will need to reference the Microsoft ActiveX Data Objects library by clicking Tools, References and looking for it in the list.

Private Sub UserForm_Initialize()
    Dim adoRS As ADODB.Recordset, adoCN As ADODB.Connection
    Dim strSQL As String


    'Open connection to an Access database
    Set adoCN = New ADODB.Connection
    adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"
    adoCN.ConnectionString = "H:\temp\db1.mdb"
    adoCN.Open


    'Open a recordset
    strSQL = "SELECT DISTINCT MPCUnit FROM FTEData;"
    Set adoRS = New ADODB.Recordset

    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly




    'Now populate the combobox
    With Me.ComboBox1

        .Clear

        While Not adoRS.EOF

            .AddItem adoRS.Fields(0).Value
            adoRS.MoveNext

        Wend

    End With


    'Close recordset, connection objects and clean up
    adoRS.Close
    adoCN.Close
    Set adoRS = Nothing
    Set adoCN = Nothing


End Sub

 

[jag108]

Cool thanks...

Next silly question?

I do not connect to an MS Access DB, I connect to an Oracle DB, which has its own domain, what would be the connection string for something like that.

I have tried the Excel ODBC connetion string which is generated by the macro code generator, but this fails with an error. I have added the Active X reference. Error "Could not find installable ISAM."

Connection:="ODBC;DSN=xxx.xxx.xxx.xxx;UID=myID/psswd;DBQ=xxx.xxx.xxx.xxx;ASY=OFF

Sory to be perdantic.

Once again thanks for your help.

 

[dk]

Hi,

You could take a look at http://www.able-consulting.com/ADO_Conn.htm which has all sorts of ADO connection string examples, including Oracle.