• 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

    p45cal

    Need help with total monthly hours

    Enter times as follows: If it's 2:05 in the afternoon enter as 2:05 PM or 14:05.
    The attached should handle times across midnight, but check....

    p45cal Yesterday, 11:22 PM Go to last post
    dcope7

    Need help with total monthly hours

    I took the 12 out and if you key in 0:30 it row 12 it still subtracts from row 16 which is what it is supposed to do. I've tried every format I could...

    dcope7 Yesterday, 07:05 PM Go to last post
    p45cal

    Need help with total monthly hours

    It does not! it adds the value 12 and doesn't even look at row 12! In this context it adds 12 days (=288 hours). Barmy.
    Until you get formulae right...

    p45cal Yesterday, 06:02 PM Go to last post
    dcope7

    Need help with total monthly hours

    Ok row 16 calculates rows 11 and 15, plus it subtracts row 12 and yes I know I have it adding but if you actually add 0:30 in row 12 it will subtract...

    dcope7 Yesterday, 05:23 PM Go to last post
    p45cal

    Need help with total monthly hours

    I'm talking about row 16, NOT ROW 12!
    The 12 is a hard-coded 12 in the formula, and it adds (not subtracts) 12. The formula in cell D16 (yes, the...

    p45cal Yesterday, 05:10 PM Go to last post