ExcelBanter - EXCEL AND ODBC AND QUERY

Hi Gary B,

I have an Excel file 2002 format that currently has a
query extracting data from a mainframe file using an ODBC
Connection. All works fine. Now time to progress to the
next level, and have some parameters, to limit the data
coming from the mainframe file.

I have a field on a sheet called "tables" that contains a
department code "001".

Q1. How do I limit the ODBC connection to obtaining only
those records with the department code "001", without
hard coding that into the query (the department may
change).

Hopefully this will get you started

Sub UpdateExternalQuery()
Dim str_SQ1 As String
Dim str_SQ2 As String
Dim str_deptCode As String

' str_deptCode is parameter in query
str_deptCode = "001"

' build a SQL statement
str_SQ1 = "SELECT myqry.* FROM mydb.dbo.myqry myqry "

str_SQ2 = "WHERE myqry.dept_code = '" & str_deptCode& "' "

' handle missing parameters by changing the SQL string
If str_deptCode = "" Then
str_SQ2 = ""
End If

' data_dump is the named range of the query table in Excel
With Range("data_dump").QueryTable
.Connection = _
"ODBC;DSN=myDSN;UID=login;PWD=password;APP=Microso ft® Query;DATABASE=mydb"
.CommandText = Array(str_SQ1, str_SQ2)
.Refresh BackgroundQuery:=False
End With

End Sub

Q2. How can I programatically create an ODBC connection
to the mainframe table without having to configure the
ODBC driver on each user's PC ?

Thanks in advance.

You will notice that the above example uses a predefined DSN. You can use a
file DSN and distribute it with your application. Or you can ask this group
to see if anyone can build DSN connections programmatically :)

--
Ed Ferrero
http://edferrero.m6.net