• Populate Multi-Column Listbox With Data From Access

    Macro Purpose:
    • Retrieves data from an Access database, and fills it into a userform listbox.
    Examples of where this function shines:
    • The Access database can be in a *.mdb or *.accdb format, and the code can be run from Excel 97 or higher with no issues.
    Macro Weakness(es):
    • Does not populate listbox with column header names.
    • There is no error handling in this routine.
    Versions Tested:
    This function has been tested with Access & Excel 97, and Access & Excel 2003, and should also work with Access and Excel 2000 and 2002 (XP) without any modifications. To use it with an Access 2007 or higher format (*.accdb files), you must update the connection strings (at the very top of the code -- just uncomment the *.accdb line and remove the *.mdb line). If you wish to use another Database Management System (DBMS), see "Adapting this to a DBMS other than Access" below.

    VBA Code Required:
    • A reference must be set to the "Microsoft ActiveX Data Objects Libary"
    • You will need to make sure that the path to your database is set correctly
    • You will need to update the listbox name in the code from "lbSuppliers"
    The code following should be placed in a userform and called from the Userform_Initialize event:

    Option Explicit
    'Set reference to the Microsoft ActiveX Data Objects x.x Library!
    'Global constants required
    Const glob_sdbPath = "C:\Temp\FoodTest.mdb"
    'Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & glob_DBPath & "';"     'For use with *.accdb files
    Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"     'For use with *.mdb files
    Private Sub PopulateSuppliers()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Populate the listbox with all values from the Access database
    Dim cnt     As New ADODB.Connection
    Dim rst     As New ADODB.Recordset
    Dim rcArray As Variant
    Dim sSQL    As String
    'Set the location of your database, the connection string and the SQL query
    sSQL = "SELECT tblSuppliers.SupplierName, tblSuppliers.SupplierNumber " & _
        "FROM tblSuppliers ORDER BY tblSuppliers.SupplierName;"
    'Open connection to the database
    cnt.Open glob_sConnect
    'Open recordset and copy to an array
    rst.Open sSQL, cnt
    rcArray = rst.GetRows
    'Place data in the listbox
    With Me.lbSuppliers
        .ColumnCount = 2
        .List = Application.Transpose(rcArray)
        .ListIndex = -1
    End With
    'Close ADO objects
    Set rst = Nothing
    Set cnt = Nothing
    End Sub
    Adapting this to other DBMS systems:
    • In order to use this routine with a different DBMS than Microsoft Access, the Provider must be changed to match the DBMS that you want to use
      • Specifically, this section of the above code:
        Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
      • needs to be updated to reflect the proper OLE Database Provider engine from Microsoft.Jet.
    • More information on a huge variety of OLE Database Providers can be found here.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!