How should I populate a MS Access ListBox by querying Sybase database?

Question

I'm trying to populate a ListBox on MS Access Front end by querying Sybase.

I think setting the RowSource will not work here.

Will be very helpful if someone can provide me with sample code.

Many thx.

Answer 1

You have 3 main options

1) Link the table and then set the row source

2) Open the table using VBA, loop through and add the records to the list of values separated by a ;

3) Change it from a normal listbox to an ActiveX one that has a collection to control the items in the listbox

Option 1 is the easiest and would be the one to go for in 99% of situations. Option 2 is a good one but remember that the list is then limited to I thing 1024 characters. Option 3 I use a fair amount and can be very good. Below is some code I made that takes a SQL string and a list box and does the rest.

This is how to call it

Assign_SQl_to_listview “SELECT * FROM tblFoo”, me.lstMyListBox

And now the fucntion

Public Function Assign_SQl_to_listview(strList_SQL As String, lstTarget As ListView) As Long
'open up the SQL as a recordset and lop through adding the rows as items
If Len(strList_SQL) <= 2 Then Exit Function
Dim bFields As Byte
Dim i As Byte
Dim rst As DAO.Recordset
On Error GoTo Error_trap
Dim lstItem As ListItem
DoCmd.Hourglass True
lstTarget.ListItems.Clear
Set rst = DBEngine(0)(0).OpenRecordset(strList_SQL, dbOpenForwardOnly)

With rst
    'see how many fields this SQL statement has, then only go through that many sub items-1
    bFields = .Fields.Count
    Do Until .EOF

        ' Add items and subitems to list control.
            Set lstItem = lstTarget.ListItems.Add()
            lstItem.Text = .Fields(0)
            'loop through the fields adding them
            For i = 1 To bFields - 1
                lstItem.SubItems(i) = Nz(.Fields(i), "")
            Next i

        .MoveNext
    Loop
Assign_SQl_to_listview = lstTarget.ListItems.Count

End With

rst.Close
Set rst = Nothing

DoCmd.Hourglass False


Exit Function

Error_trap:

DoCmd.Hourglass False
Set rst = Nothing
Debug.Print strList_SQL
MsgBox "An error happened in sub Assign_SQl_to_listview, error description, " & Err.Description, vbCritical, "Tracker 3"
Debug.Print strList_SQL
End Function