Macro Purpose:

1. Retrieves data from an Access database, and fills it into a userform listbox.

Examples of where this function shines:

1. The Access database is in an Access 2000 format, but the code can be run from Excel 97-2003 with no issues.

Macro Weakness(es):

1. Does not populate listbox with column header names.
2. 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

VBA Code Required:

1. A reference must be set to the "Microsoft ActiveX Data Objects Libary"
2. You will need to make sure that the path to your database is set correctly
3. You will need to update the listbox name in the code from "lbSuppliers"
4. 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.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
   
   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 a DBMS other than Access:

1. In order to use this routine with a DBMS other than Microsoft Access, the Provider must be changed to match the DBMS that you want to use
   
   1. Specifically, this section of the above code:
      

      Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"
      

   2. needs to be updated to reflect the proper OLE Database Provider engine from Microsoft.Jet.
2. More information on a huge variety of OLE Database Providers can be found here.