Get Items in a ComboBox from MSAccess Data

[bjoshi]

Ok. I think this should be pretty simple for most people. Hopefully one of you will help me out.

Lets say I have a Msaccess file (containing a single column table), from which I need the data to be added as the list in my combobox which is placed in excel.

I know we can query it with an ADO, but my question is, what is the line of code that actually gets the data into that combobox?

Best Regards,
BJoshi

[Kenneth Hobs]

Sub FillCBControl(theControl As MSForms.ComboBox, mdbName As String, sSQL As String)
  'Requires Reference to Microsoft ActiveX Data Objects 2.8 Library
  'Used for Listbox control for one column only.
  Dim cnt     As New ADODB.Connection
  Dim rst     As New ADODB.Recordset
  Dim rcArray As Variant
  Dim sConnect As String
  sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbName & ";"
'On Error GoTo CloseADO
'Open connection to the database
  cnt.Open sConnect
'Open recordset and copy to an array
  rst.Open sSQL, cnt
  rcArray = rst.GetRows
'Place data in the Control
  With theControl
    .Clear
    .ColumnCount = 1
    .List = Application.Transpose(rcArray)
    .ListIndex = 0
  End With
CloseADO:
   'Close ADO objects
  rst.Close
  cnt.Close
  Set rst = Nothing
  Set cnt = Nothing
End Sub

Here is a listbox example which would be similar.

Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
    With lb
    .Clear
    For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
        .AddItem
        For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
            .List(r, c) = RecordSetArray(c, r)
        Next c
    Next r
    .ListIndex = -1 ' no item selected
    End With
End Sub

[bjoshi]

Hi Kenneth,

Thank you for your reply. Havent tried it yet, but I'll let you know once I do. Thanks again.

Regards.
BJoshi

[bjoshi]

Thanks Kenneth, had to modify a few things to suit me, but now its done. Thanks again.