Please can someone tell me how to populate a combo box from a SQL query against SQL2000
Im using XL2000 and W2K. And should I be using activex or userform controls €“ is there a difference
Thanks for any hel

Use ADO to query SQL2000, and populate an array with the recordset, and then
load the combo from that array.

As to whether activex or userform, it depends what else you want. If it is
only a combobox, activex should be enough, if you want more controls related
to the combo, you would probably be better with a userform.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mandy" wrote in message
...
Please can someone tell me how to populate a combo box from a SQL query
against SQL2000?
I'm using XL2000 and W2K. And should I be using activex or userform
controls - is there a difference?
Thanks for any help

Sorry Im a bit dense. (At the moment I am testing against Access and will change to SQL later). I have managed to run the query against SQL2000 and I have populated a recordset. I have used the code below to make sure I am pulling data back from the database. This code copies the recordset into a spreadsheet, but I cant see how to populate a combo box. Please could you tell me what I am missing? Many Thanks

Sub Import_AccessData(

Dim cnt As ADODB.Connectio

Dim rst1 As ADODB.Recordse
Dim stDB As String, stSQL1 As String, stSQL2 As Strin
Dim stConn As Strin
Dim wbBook As Workboo
Dim wsSheet1 As Workshee
Dim lnField As Long, lnCount As Lon

Set cnt = New ADODB.Connectio
Set rst1 = New ADODB.Recordse
Set rst2 = New ADODB.Recordse

Set wbBook = ThisWorkboo
Set wsSheet1 = wbBook.Worksheets(1

stDB = "c:\ExcelTest.mdb

stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" & stDB & ";

stSQL1 = "SELECT DeptNo FROM NewTable

wsSheet1.Range("A1").CurrentRegion.Clea

With cn
..Open (stConn)
..CursorLocation = adUseClien
End Wit

With rst
..Open stSQL1, cnt
Set .ActiveConnection = Nothing
End Wit

With wsSheet
..Cells(2, 1).CopyFromRecordset rst
End Wit

rst1.Clos
Set rst1 = Nothin
cnt.Clos
Set cnt = Nothin

End Su


----- Bob Phillips wrote: ----

Use ADO to query SQL2000, and populate an array with the recordset, and the
load the combo from that array

As to whether activex or userform, it depends what else you want. If it i
only a combobox, activex should be enough, if you want more controls relate
to the combo, you would probably be better with a userform

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

"Mandy" wrote in messag
..
Please can someone tell me how to populate a combo box from a SQL quer
against SQL2000
I'm using XL2000 and W2K. And should I be using activex or userfor
controls - is there a difference
Thanks for any hel

You are doing most of what I was suggesting and as you are copying the
recordset to a worksheet range, you could pick it up from there.

Just change this

With wsSheet1
..Cells(2, 1).CopyFromRecordset rst1
End With

to

With wsSheet1
..Cells(2, 1).CopyFromRecordset rst1
..ComboBox1.ListFillRange = .Range("A2:A100").Address
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mandy" wrote in message
...
Sorry I'm a bit dense. (At the moment I am testing against Access and
will change to SQL later). I have managed to run the query against SQL2000
and I have populated a recordset. I have used the code below to make sure I
am pulling data back from the database. This code copies the recordset into
a spreadsheet, but I can't see how to populate a combo box. Please could
you tell me what I am missing? Many Thanks.

Sub Import_AccessData()

Dim cnt As ADODB.Connection

Dim rst1 As ADODB.Recordset
Dim stDB As String, stSQL1 As String, stSQL2 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long

Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)

stDB = "c:\ExcelTest.mdb"

stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

stSQL1 = "SELECT DeptNo FROM NewTable"

wsSheet1.Range("A1").CurrentRegion.Clear

With cnt
.Open (stConn)
.CursorLocation = adUseClient
End With

With rst1
.Open stSQL1, cnt
Set .ActiveConnection = Nothing
End With

With wsSheet1
.Cells(2, 1).CopyFromRecordset rst1
End With

rst1.Close
Set rst1 = Nothing
cnt.Close
Set cnt = Nothing

End Sub


----- Bob Phillips wrote: -----

Use ADO to query SQL2000, and populate an array with the recordset,
and then
load the combo from that array.

As to whether activex or userform, it depends what else you want. If
it is
only a combobox, activex should be enough, if you want more controls
related
to the combo, you would probably be better with a userform.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mandy" wrote in message
...
Please can someone tell me how to populate a combo box from a SQL
query
against SQL2000?
I'm using XL2000 and W2K. And should I be using activex or userform
controls - is there a difference?
Thanks for any help

Bob
I get a compile error: Method or data member not foun
and the word '.ComboBox1' is highlighted
Am I able to just add your line of code to mine, or do I need to make some other changes
Mand
ps can I get the data to go straight to the combo box list and not so the sheet first?

That probably means that your combobox is not named ComboBox1.

Is it an ActiveX combo or a forms combo?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mandy" wrote in message
...
Bob,
I get a compile error: Method or data member not found
and the word '.ComboBox1' is highlighted.
Am I able to just add your line of code to mine, or do I need to make some
other changes?
Mandy
ps can I get the data to go straight to the combo box list and not so the
sheet first?

Hi Mandy,

Mandy wrote:
Bob,
I get a compile error: Method or data member not found
and the word '.ComboBox1' is highlighted.
Am I able to just add your line of code to mine, or do I need to make
some other changes?

Instead of ComboBox1, you need to use the name of the ComboBox on your
worksheet.

ps can I get the data to go straight to the combo box list and not so
the sheet first?

Yes, you can do it directly. Here's one way (make sure you replace
"cboDeptNos" with the actual name of your ComboBox):

Sub test()
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rst As ADODB.Recordset
Dim stDB As String

stDB = "c:\ExcelTest.mdb"
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=Microsoft.Jet" & _
".OLEDB.4.0;Data Source=" & stDB & ";"
.CursorLocation = adUseClient
.Open
End With

Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
'CommandText = "SELECT DeptNo FROM NewTable"
.CommandType = adCmdText
Set rst = .Execute
End With

With rst
If .State = adStateOpen Then
If Not (.BOF And .EOF) Then
Sheet1.cboDeptNos.List = _
Application.Transpose(.GetRows)
End If
.Close
End If
End With

Set rst = Nothing
Set cd = Nothing
Set cn = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Thank you both so much
With your help I finally worked out what to do, and found some other errors in my thinking, which should make the rest of the programming easier

Thanks again
Mand

----- Mandy wrote: ----

Please can someone tell me how to populate a combo box from a SQL query against SQL2000
Im using XL2000 and W2K. And should I be using activex or userform controls €“ is there a difference
Thanks for any hel