Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL AND ODBC AND QUERY
Hi there,
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). 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
odbc error on Excel query into database | Excel Discussion (Misc queries) | |||
Changing ODBC data source for a query embedded in Excel | Excel Discussion (Misc queries) | |||
how to access databases from excel using ms query and odbc? | Excel Discussion (Misc queries) | |||
msquery odbc -- excel repeats query on return | Excel Discussion (Misc queries) | |||
Excel query via ODBC cuts values | Excel Discussion (Misc queries) |