• 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:

    Code:
    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
        .Clear
        .ColumnCount = 2
        .List = Application.Transpose(rcArray)
        .ListIndex = -1
    End With
    
    'Close ADO objects
    rst.Close
    cnt.Close
    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:
        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!

  • MVP Logo
  • Recent Forum Posts

    Logit

    unduplicated list

    If you also include the "Check Run Date" column in the table as well, that will provide a means of selecting which month.

    Code:
    
    
    ...

    Logit Today, 04:35 PM Go to last post
    BuddyGSD

    unduplicated list

    Is there a way for excel to gather the names of the families from the month of April and May to appear on the Undup Clients tab then?...

    BuddyGSD Today, 04:08 PM Go to last post
    NoS

    Sum cells in each generated row

    Along with writing the form data to the sheet you could write a formula into the O column

    Code:
    Cells(whateverrow, 15).FormulaR1C1="=SUM(RC[-9]:RC[-1])"
    ...

    NoS Today, 02:27 PM Go to last post
    p45cal

    If overide formulae

    Array-enter (Ctrl+Shift+Enter, not just Enter) and copy down:
    =INDEX({"Fail";"Risk";"Pass"},MIN(MATCH(T2:U2,{"Fail","Risk","Pass"},0)))...

    p45cal Today, 12:14 PM Go to last post
    jonespandrew

    If overide formulae

    Sorry I have read you last response wrong Write

    Fail Risk would be a fail
    Risk pass would be a Risk
    Risk Fail would be a fail...

    jonespandrew Today, 11:14 AM Go to last post