Populate listbox from MYSQL datasource w/o linked tables (1 Viewer)

[Dave_cha]

Hi folks,

I'm trying to establish a connection to a MySQL db and populate a listbox with data from a table in the db. I'm trying to trigger this on loading the form.

I haven't done this before as I've previously used linked tables. This time around I've been asked to avoid DSN linked tables.

Any help here would really be appreciated.

Thanks,

Dave

Private Sub Form_Load()

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=SERVERNAME;DATABASE=DBNAME;UID=pcuser;PWD=pcuser;OPTION=3"
conn.Open

rs.Open "SELECT FirstName,Surname FROM EMPLOYEES_Registered", conn, adOpenStatic, adLockReadOnly

Me.Listbox.RowSource = ??

End Sub

 

[Dugantrain]

You can build a value list String from your Recordset object and then set the listbox rowsource equal to that String:

dim strList as String
Do Until rs.EOF
     strList=strList & rs!FirstName & ";" & rs!SurName & ";"
Loop
'I believe you'll need to remove the final ";":
strList=Left(strList, len(strList)-1)
Me.Listbox.RowSource =strList

 

[Dave_cha]

Hi Dugantrain,

Sorry for not replying until now....I've been out of the office.
I haven't had a chance to try the code you suggested yet but at first glance it looks like it will do the job.

Anyway, just wanted to say thanks for the help.

Rgd's,

Dave