Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Populate a Userform Listbox using ADO

written by Will Riley - Last updated Oct 2004

The following code shows how we can use ADO to populate a Listbox positioned on an Excel Userform with data from an MS Access Database.

Required:

Access Database "Example.mdb"

Excel Book containing Userform1 and Listbox1

Userform code:

Private Sub UserForm_Initialize() 
    Call Populate_Listbox 
End Sub

Standard Module Code:

Sub Populate_Listbox() 
     '*************************************************************************
     '* The following code populates a listbox on "Userform1" with data from  *
     '* an MS Access Database ("Example.mdb")using ADO.                       *
     '*                                                                       *
     '* Requires references to the following:                                 *
     '* 1. Microsoft ActiveX Data Objects 2.5 or greater Library              *
     '*                                                                       *
     '* Special thanks to XL-Dennis for code & idea sharing ("2 heads > 1")   *
     '*************************************************************************
    Dim cnADO As ADODB.Connection 
    Dim rstADO As ADODB.Recordset 
    Dim strCon As String, strSQL As String 
    Dim vaData As Variant 
    Dim lCols As Long 
     
     'Set the ADO connection
    Set cnADO = New ADODB.Connection 
     'Specify the connection string.
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _ 
    & "C:WillExample.mdb;Persist Security Info=False" 
     'Specify your SQL statement.
    strSQL = "SELECT * FROM tblContacts" 
    With cnADO 
        .CursorLocation = adUseClient 
         'Client-side cursor location (as opposed to server-side)
         'required as we are going to disconnect the recordset
         'in order to populate the listbox.
        .Open strCon 'Open the connection.
         'execute the SQL statement.
        Set rstADO = .Execute(strSQL) 
    End With 
     
    With rstADO 
        Set .ActiveConnection = Nothing 
         'Disconnect recordset.
        lCols = .Fields.Count 
         'Populate a variant array with the recordset.
        vaData = .GetRows 
    End With 
     'Close the connection.
    cnADO.Close 
     'Populate the Listbox.
    With UserForm1 
        With .ListBox1 
            .Clear 
            .ColumnCount = lCols 
            .BoundColumn = lCols 
            .List = Application.Transpose(vaData) 
            .ListIndex = -1 
        End With 
    End With 
     'Release objects from memory.
    Set rstADO = Nothing 
    Set cnADO = Nothing 
End Sub