Solved: Populate a comboxbox and Listbox in a excel userform with an sql table data

[abhay_547]

Hi,

I have the below code so far. Following are things which I am looking for :

1) While establishing connection to sql server database I want to enter User ID and password in my code.
2) My Combobox gets populate with the data but it shows all instances of each region. For e.g. America is reflecting 4 times in my combo dropdown same in case of other regions. It should take only one instance of each region.
3) As mentioned earlier I want to add a list box to my userform which will get populated on the basis of the value which i had selected in my combo box for example If I select America in my combo box my list box should get populated with the countries which are mapped against America in my sql server table and they should get populated with check boxes in listbox.

Private Sub UserForm_Initialize()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stConn As String, stSQL As String
Dim xlCalc As XlCalculation
Dim vaData As Variant
Dim k As Long
Set cnt = New ADODB.Connection
stConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sap_data;Data Source=DB-77716EFB0313\SQLEXPRESS"
cnt.ConnectionString = stConn
'your  SQL statement
stSQL = "SELECT Region FROM Region_Mapping"
With cnt
    .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
    .Open stConn 'Open connection.
    'Instantiate the Recordsetobject and execute the SQL-state.
    Set rst = .Execute(stSQL)
End With
With rst
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    k = .Fields.Count
    'Populate the  array with the whole recordset.
    vaData = .GetRows
End With
'Close the connection.
cnt.Close
'Manipulate the Combobox's  properties and show the form.
With frmdata
    With .ComboBox1
        .Clear
        .BoundColumn = k
        .List = Application.Transpose(vaData)
        .ListIndex = -1
    End With
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub

Please expedite.