In fact I use the code by Ken, as I cannot paste link right now
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
'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