Populate listbox from Recordset

[PATRICK]

So Im trying to populate the listbox using string connection and having this error: "the object you entered is not a valid recordset property"

here's the code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;Port=xxx;USER=xxx; PASSWORD=xx;OPTION=3;"

cnn.Open

Set rst = cnn.Execute("SELECT date, timein, timeout FROM table")


Me.List5.RowSourceType = "Table/Query"


Set Me.List5.Recordset = rst

rst.Close
cnn.Close

Set rst = Nothing


Set cnn = Nothing


Where is the error??? am doing right or is there any better way to populate listbox from string connection.

Thanks a lot...

[RuralGuy]

I would guess that this line:
Set Me.List5.Recordset = rst
should really be
Me.List5.Rowsource = rst

[ItsMe]

Select queries are assigned to the Rowsource of listboxes.

I have never tried to assign a recordset to a list box.

My listbox properties usually look like this

me.lstTemp.RowSource = strSQL





Set Me.List5.Recordset = rst

[PATRICK]

RuralGuy, I tried your suggestion but then another error is happening: "Error 91 - Object variable or With block variable not set"

[ItsMe]

Did you compile your code from the debugger? Did it stop on a variable that was not declared?

[PATRICK]

if I put the line like ruralguy said : "Me.List5.Rowsource = rst" then when I debugger I got error Mismatch"......but if I the line is .recordset = rst, then it's debugging but then I have the error "the object you entered is not a valid recordset property"

[ItsMe]

Well, mismatch is telling you it does not like the fact that you are assigning a recordset to the rowsource of the listbox.

Maybe using your "connection" you can build an SQL string that will SELECT a table or query object. Maybe a query def or an ADOX table or something.

I don't know exactly how. I just have never been able to get a string directly out of a recordset.

[PATRICK]

ok, I've got this code working, but it is too slow. I don't have even 2.000 rows and it takes almost 50 seconds to populate my list box !!!

code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Dim strSQL As String

Set cnn = New ADODB.Connection
cnn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;Port=xxx;USER=xxx; PASSWORD=xxx;OPTION=3;"

cnn.Open

strSQL = "SELECT date, timein, timeout FROM table"

Set rst = cnn.Execute(strSQL)

Do While Not rst.EOF
Me.List5.AddItem rst!date
rst.MoveNext
Loop


rst.Close
cnn.Close


Set rst = Nothing
Set cnn = Nothing
''

Any idea what is making this to running so slow???!!

[ItsMe]

Try doing something else with your connection. You are creating a recordset and I don't think that is what you need.

Check out this thread. Post #5 talks about ADOX. Looks like there is some code there to get a string you can pass to the listbox. There is another method discussed in the thread too.
http://www.dbforums.com/microsoft-ac...ef-object.html