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.