Hello,

I want to be able to query an ODBC database through a Combobox on a
VBA form. I can do it if the data is stored in cells on the
spreadsheet but I have other info that is on the mainframe system that
I need to access.

Any help would be appreciated.

Thanks
Dean

Hi Dean,

Use the macro recorder to find the appropriate source and connection type.
After turning on the recorder from the Tools/Macro menu, go to the Data menu
and choose Get External Data / New Database Query. Choose the data source
type from the first dialog. If it is not in the list, you will have to try
something else. If it is, another dialog will appear asking for the location
of the file. Find it and create a simple query and end the macro recording.
View the recorded macro for the details you seek.

HTH, Greg

"Dkso" wrote in message
om...
Hello,

I want to be able to query an ODBC database through a Combobox on a
VBA form. I can do it if the data is stored in cells on the
spreadsheet but I have other info that is on the mainframe system that
I need to access.

Any help would be appreciated.

Thanks
Dean

Greg,

Thanks, it does help but not with this problem.
What you have below will bring back everything on the table, in the instance
I need to use it that is not possible as there are over 172,000 records on
this table (and obviously only 65,532 will fit onto an Excel spreadsheet).

I have used a ComboBox in MS Access to achieve what I want to do but don't
know how to do it in Excel.
I want to click on the ComboBox or start typing an item code and the item be
displayed in the dropdown box.

Dean

"Greg Koppel" wrote in message
...
Hi Dean,

Use the macro recorder to find the appropriate source and connection type.
After turning on the recorder from the Tools/Macro menu, go to the Data
menu
and choose Get External Data / New Database Query. Choose the data source
type from the first dialog. If it is not in the list, you will have to try
something else. If it is, another dialog will appear asking for the
location
of the file. Find it and create a simple query and end the macro
recording.
View the recorded macro for the details you seek.

HTH, Greg

"Dkso" wrote in message
om...
Hello,

I want to be able to query an ODBC database through a Combobox on a
VBA form. I can do it if the data is stored in cells on the
spreadsheet but I have other info that is on the mainframe system that
I need to access.

Any help would be appreciated.

Thanks
Dean

"Dkso" wrote ...

172,000 records on
this table (and obviously only 65,532 will fit onto an Excel spreadsheet).
I have used a ComboBox in MS Access to achieve what I want to do but don't
know how to do it in Excel.
I want to click on the ComboBox or start typing an item code and the item be
displayed in the dropdown box.

This in a userform with a combo named ComboBox1 (check the connection
string for northwind):

Option Explicit

Private Sub UserForm_Activate()

Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String

' Amend the following constants to suit
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\NWIND.mdb"

strConJet = CONN_STRING_JET

' Build sql statement
strSql1 = "SELECT LastName FROM Employees"

' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)

' Write data to combo
ComboBox1.List = _
Excel.Application.Transpose(rs.GetRows)

.Close
End With

End Sub

Jamie.

--

Worked great guns at home, on the Northwind DB; boy was it fun trying it on the ODBC db at work. Any way I’ve got down to the bottom and now got a Type mismatch error.
Private Sub UserForm_Activate()

Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
' Amend the following constants to suit
Const CONN_STRING_JET As String = "DSN=MySN;" & _
"UID=MyUSID;" & _
"PWD=MyPWD;" & _
"SERVER=MySEVER;"

strConJet = CONN_STRING_JET
' Build sql statement
strSql1 = "SELECT LOC.LOC FROM DC5999D.LOC LOC"
' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)
' Write data to combo
ComboBox1.List = _
Excel.Application.Transpose(rs.GetRows)
.Close
End With
End Sub

Error message is: -

Run time error ‘13’:
Type Mismatch
at Line "ComboBox1.List=_
Excel.Application.Tanspose(rs.GetRows)"
The LOC fieldof the LOC table is a STRING so I don't understand how it's a type Mismatch!
I have used the Record Macro in Excel to get the details for the SERVER, DSN etc. so those details should be correct.DC5999D is the database and LOC is the table.
Can anyone tell me where I'm ing wrong.

Thanks in advance

Dean

"Jamie Collins" wrote in message om...
"Dkso" wrote ...

172,000 records on
this table (and obviously only 65,532 will fit onto an Excel spreadsheet).
I have used a ComboBox in MS Access to achieve what I want to do but don't
know how to do it in Excel.
I want to click on the ComboBox or start typing an item code and the item be
displayed in the dropdown box.

This in a userform with a combo named ComboBox1 (check the connection
string for northwind):

Option Explicit

Private Sub UserForm_Activate()

Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String

' Amend the following constants to suit
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\NWIND.mdb"

strConJet = CONN_STRING_JET

' Build sql statement
strSql1 = "SELECT LastName FROM Employees"

' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)

' Write data to combo
ComboBox1.List = _
Excel.Application.Transpose(rs.GetRows)

.Close
End With

End Sub

Jamie.

--

"Dkso" wrote ...

Worked great guns at home, on the Northwind DB; boy was it fun trying it
on the ODBC db at work. Any way I ve got down to the bottom and now
got a Type mismatch error.
Private Sub UserForm Activate()

Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
' Amend the following constants to suit
Const CONN STRING JET As String = "DSN=MySN;" &
"UID=MyUSID;" &
"PWD=MyPWD;" &
"SERVER=MySEVER;"

strConJet = CONN STRING JET
' Build sql statement
strSql1 = "SELECT LOC.LOC FROM DC5999D.LOC LOC"
' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)
' Write data to combo
ComboBox1.List =
Excel.Application.Transpose(rs.GetRows)
.Close
End With
End Sub

Error message is: -

Run time error 13 :
Type Mismatch
at Line "ComboBox1.List=
Excel.Application.Tanspose(rs.GetRows)"
The LOC fieldof the LOC table is a STRING so I don't understand how it's
a type Mismatch!
I have used the Record Macro in Excel to get the details for the SERVER,
DSN etc. so those details should be correct.DC5999D is the database and
LOC is the table.
Can anyone tell me where I'm ing wrong.

I can't see anything that would give that particular error. The SQL
looks odd (a table named LOC with a column named LOC and you are
aliasing the table as LOC?) but I guess it must be valid because you
are not getting an error on the Execute line. Perhaps post some more
details e.g. which database product, table schema, sample data, etc.

Jamie.

--