Excelguru Help Site - 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!