SQL combo
 

To All:


Option #1)
I would like to query an Access db via a combobox in
Excel. Is there any way to put a SQL Statement in Excels
comboboxes?


Option #2)
And if I can't do the above extenal data query...can I
put a SQL statement in a Excel combobox that queries a
table within Excel?


thanks much
Matt
Matt

SQL combo
 

You can't put an sql query in a combobox property if that is what you mean.
You can query the access database and put the information in the dropdown
list. See Mr. Erlandsen's site for sample code on using ADO

http://www.erlandsendata.no/english/...odao/index.php

also:

http://support.microsoft.com/default...61&Product=xlw
XL2000: How to Use ADO to Return Data to a ListBox or ComboBox



--
Regards,
Tom Ogilvy

Matt wrote in message
...
To All:


Option #1)
I would like to query an Access db via a combobox in
Excel. Is there any way to put a SQL Statement in Excels
comboboxes?


Option #2)
And if I can't do the above extenal data query...can I
put a SQL statement in a Excel combobox that queries a
table within Excel?


thanks much
Matt
Matt

SQL combo
 

-----Original Message-----
You can't put an sql query in a combobox property if
that is what you mean.
You can query the access database and put the
information in the dropdown
list. See Mr. Erlandsen's site for sample code on using
ADO

http://www.erlandsendata.no/english/...odao/index.php

also:

http://support.microsoft.com/default.aspx?scid=kb;en-
us;244761&Product=xlw
XL2000: How to Use ADO to Return Data to a ListBox or
ComboBox



--
Regards,
Tom Ogilvy

Matt wrote in
message
...
To All:


Option #1)
I would like to query an Access db via a combobox in
Excel. Is there any way to put a SQL Statement in
Excels
comboboxes?


Option #2)
And if I can't do the above extenal data query...can I
put a SQL statement in a Excel combobox that queries a
table within Excel?


thanks much
Matt
Matt


.
Tom:

I appreciate your help, but unfortunately I am not very
good at VB and neither of the references allow me to
copy/paste and put in my parameters and have it populate
the combo.

Matt

SQL combo
 

I don't know about copy and paste, but they certainly let you specify the
sql statement to use and that is where you would put your parameters.

--
Regards,
Tom Ogilvy

Matt wrote in message
...

-----Original Message-----
You can't put an sql query in a combobox property if
that is what you mean.
You can query the access database and put the
information in the dropdown
list. See Mr. Erlandsen's site for sample code on using
ADO

http://www.erlandsendata.no/english/...odao/index.php

also:

http://support.microsoft.com/default.aspx?scid=kb;en-
us;244761&Product=xlw
XL2000: How to Use ADO to Return Data to a ListBox or
ComboBox



--
Regards,
Tom Ogilvy

Matt wrote in
message
...
To All:


Option #1)
I would like to query an Access db via a combobox in
Excel. Is there any way to put a SQL Statement in
Excels
comboboxes?


Option #2)
And if I can't do the above extenal data query...can I
put a SQL statement in a Excel combobox that queries a
table within Excel?


thanks much
Matt
Matt


.
Tom:

I appreciate your help, but unfortunately I am not very
good at VB and neither of the references allow me to
copy/paste and put in my parameters and have it populate
the combo.

Matt

SQL combo
 

Here's something I contributed to another post last week. It relates
to a combobox on an Excel userform.

--
Excel's combobox controls link to Excel ranges and don't have a
DataSource property for binding a recordset. Therefore you have to
handle the binding yourself. This is easy enough if you just want use
the combobox to display a column of data but is a little bit more
involved if you want to write back to the database changes made by the
user. Here's a demo:

1. Add a new userform
2. Add a combobox called ComboBox1
3. Paste the code below into the .
4. Edit the constant strPATH for your database; you will also need to
amend strCONNECTION if your DB has protection/security: for details
see
http://www.able-consulting.com/MDAC/...orMicrosoftJet

5. Edit the SQL to return a key column (e.g. RefID) and a data column
(e.g. Surname) respectively from your table (e.g. PersonalDetails).
6. Run the userform, drop down the combobox and view the data column.
When an item is selected, the hidden bound (key) column will be
returned by the combobox's Value property, the visible text (data)
column by its Text property.

I haven't provided code to write back changes to the database because
it's difficult to generalize e.g. do you want to update the database
on the KeyDown event, the AfterUpdate event or the UserForm_Deactivate
event? The general approach is to propagate the change from combobox
to the recordset and invoke its Update or BatchUpdate method as
appropriate.

'---------------------------------
Option Explicit

Private m_oConn As ADODB.Connection
Private m_oRS As ADODB.Recordset

Private Const strCONNECTION As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Private Const strPATH As String = "C:\Tempo\New_Jet_DB.mdb"
Private Const strSQL As String = "SELECT RefID, Surname FROM
PersonalDetails"

Private Sub UserForm_Initialize()

Dim vntArray As Variant

Set m_oConn = CreateObject("ADODB.Connection")
m_oConn.Open strCONNECTION & strPATH

Set m_oRS = CreateObject("ADODB.Recordset")

With m_oRS
.CursorLocation = 3 ' adUseClient
.CursorType = 3 ' adOpenStatic
.LockType = 4 ' adLockBatchOptimistic
.ActiveConnection = m_oConn
.Open strSQL

End With

With ComboBox1

.ColumnCount = 2
.BoundColumn = 1
.TextColumn = 2
.ColumnWidths = "0;" ' first column invisible

vntArray = m_oRS.GetRows
.List = Application.Transpose(vntArray)

End With

End Sub

Private Sub UserForm_Terminate()

Set m_oRS = Nothing
Set m_oConn = Nothing

End Sub
'---------------------------------


"Tom Ogilvy" wrote in message ...
I don't know about copy and paste, but they certainly let you specify the
sql statement to use and that is where you would put your parameters.

--
Regards,
Tom Ogilvy

Matt wrote in message
...

-----Original Message-----
You can't put an sql query in a combobox property if
that is what you mean.
You can query the access database and put the
information in the dropdown
list. See Mr. Erlandsen's site for sample code on using
ADO

http://www.erlandsendata.no/english/...odao/index.php

also:

http://support.microsoft.com/default.aspx?scid=kb;en-
us;244761&Product=xlw
XL2000: How to Use ADO to Return Data to a ListBox or
ComboBox



--
Regards,
Tom Ogilvy

Matt wrote in
message
...
To All:


Option #1)
I would like to query an Access db via a combobox in
Excel. Is there any way to put a SQL Statement in
Excels
comboboxes?


Option #2)
And if I can't do the above extenal data query...can I
put a SQL statement in a Excel combobox that queries a
table within Excel?


thanks much
Matt
Matt


.
Tom:

I appreciate your help, but unfortunately I am not very
good at VB and neither of the references allow me to
copy/paste and put in my parameters and have it populate
the combo.

Matt