linking a listbox to sql table
 

hey,

I'm trying to get a listbox on my form to display query results from
SQL server that I have connected to via VBA. Any suggestions? thanks in
advance.


--
marky88
------------------------------------------------------------------------
marky88's Profile: http://www.excelforum.com/member.php...o&userid=37538
View this thread: http://www.excelforum.com/showthread...hreadid=571787

linking a listbox to sql table
 

marky88 wrote:
hey,

I'm trying to get a listbox on my form to display query results from
SQL server that I have connected to via VBA. Any suggestions? thanks in
advance.




Function GetList() As Variant
GetList = Array()
Dim RecordsIdx As Integer
Dim Records() As String
RecordsIdx = 0
Dim DBRst As New ADODB.Recordset
DBRst.Open "select key, v1,v2 from Table", conString, adOpenStatic,
adLockReadOnly
ReDim Records(DBRst.RecordCount - 1, 2)
While DBRst.EOF = False
Records(RecordsIdx, 0) = DBRst!key
Records(RecordsIdx, 1) = DBRst!v1
Records(RecordsIdx, 2) = DBRst!v2
RecordsIdx = RecordsIdx + 1
DBRst.MoveNext
Wend
DBRst.Close
Set DBRst = Nothing
If RecordsIdx = 0 Then Exit Function
GetList = Records
End Function

-----------------------

and now

listbox.List = GetList()



1. conString is a connectionString. It can be global string, function etc.

2. you can play with number of columns in listbox etc, by changing
propierties in listbox, and modyfiing redim instruction.


3. I wrote it from memory, so check errors.

linking a listbox to sql table
 

thanks...how would you convert the Access syntax to SQL SERVER 2000
syntax ?


--
marky88
------------------------------------------------------------------------
marky88's Profile: http://www.excelforum.com/member.php...o&userid=37538
View this thread: http://www.excelforum.com/showthread...hreadid=571787

linking a listbox to sql table
 

marky88 wrote:
thanks...how would you convert the Access syntax to SQL SERVER 2000
syntax ?


writing once again from sratch ? :)