How to populate a vba listbox or a vba comboBox with data from Access?

[Kai Zane]

I'm trying to build a very large database that is calling on elements of Microsoft word, excel, infoPath, and access.

The main program that is controlling everything is excel, which I'm very experienced in, using VBA and VB forms. Unfortunately, I don't have a lot of experience in access, and I believe I need it now.

I'm storing information about users of the system in an access file that consists of three vertical fields: Username, Password, and System Rights.

How can I program VBA to read information from the database file {called UserAccess.accdb} and process it into a comboBox {called userComboSelect} and a listBox {called listBoxDisplay}? I thought it would be similar to referencing data in excel, but I'm sadly mistaken.

[MikeTheBike]

Hi

Below is an extract of code to do just what you want although this is a Combobox, but it works for s listbox.

Sub UserForm_Initialize()
    Dim i As Integer
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim ConnectionString As String
    Dim WIPArray() As String
 
    ConnectionString = "File Name=H:\My Data Sources\CES Maintenance KPI DB DEV.udl"
 
    cn.Open ConnectionString
    rs.Open "SELECT ID, ClientName FROM tblClients ORDER BY ClientName", cn, adOpenStatic, adLockReadOnly
 
 
    ReDim WIPArray(rs.RecordCount - 1, 1)
    For i = 0 To rs.RecordCount - 1
        WIPArray(i, 0) = rs(0)
        WIPArray(i, 1) = rs(1)
        rs.MoveNext
    Next i
    rs.Close
 
    Me.cboClient.List = WIPArray
 
End Sub

All you need now is a UDL file to gererate the connection string. This need to be in a system directory that anyone using the spreadsheet has access to.

To create a UDL file :-

1. Create a blanf txt file,(with notepad or simlar).
2. Change the extention from .txt to .udl (ignore any warnings!).
3. Double clic the udl: a dialogue box opens.
4. elect the Prover (Provider Tab)
5. Browse to the .accdb file ans select.
6. Check connection ans save (OK).

This is the file/loction to be used for the connection string in the obove code.

I assume you will be able to mod the SQL string to suit your DB.

My only disclaimer to all this is I do not use Access 2007 (or late).

HTH, but if any probs, plaes post back.

There is an article on UDLs by ADezii here
http://bytes.com/topic/access/insigh...data-link-file
There a other ways of specifying a connection string, but, IMHO, this the easiest, paticularly for a the inexperianced


MTB

ps: you will also need to set a reference to Microsofts ActiveX Data Object library. (Tools -> References...).