MS Query Datasource with Parameters

[G_D_Roberts]

Quick system summary
Windows XP Pro, Word 2003, MS Query, MS SQL Server 2000 database

Problem
I have a database containing ~150,000 customer records. Each week we complete transactions with ~1000 customers. Our customer service department needs to send "Thank You" letters to the customers
Using MS Query I have written a query to retrieve all customers within a user specified date range. ( WHERE ClosedDate Between [Start Date] and [End Date] ) When the merge connects to the Datasource, the saved query, I am prompted for the Start and End Dates. After entering the dates word displays an error that it was "Unable to connect to the datasource"

Can I use a parameterized query as a data source for mail merge? How do I accomplish it

Thanks for any help
Greg

 

[Peter Jamieson]

I've never been able to make this work with MS Query. As far as I know the
problem is that some MS Query features only work with Excel, and that is one
of them.

The other approaches I would try in this case (and I cannot tell you right
away which, if either, will definitely work) are:
a. if your users also have MS Access, create a database that links to the
SQL Server tables your queries get their data from (or create a SQL Server
View and link to that). Create an MS Access parameter query. Then ensure
Word Tools|Options|General|Confirm conversions at open is checked, set up
the Access database as the data source, and select DDE as the connection
method when prompted. Then select the query as the data source. Problems
with this are likely to be:
- this method starts Access, which creates user interface problems
- performance (which will be lousy if Access does not pass the query to
SQL Server to execute).
b. Create a UserForm in WOrd VBA that prompts for and validates the start
and end dates. Then open the data source using the OpenDataSource method,
constructing the SQLStatement parameter "on the fly", e.g.

Dim StartDate As String
Dim EndDate As String
Dim SQLString As String

' have some code to get the start and end date and
' ensure they are in the string format you need here
' - probably "YYYY-MM-DD" format
...

' Then construct the string and open the data source

SQLString = "SELECT * FROM MyView WHERE ClosedDate Between '" _
& StartDate & "' And '" & EndDate "'"

' then issue the OpenDataSource with a suitable ODBC connection string etc.

(Sorry, I can't remember exactly how you would need to quote the data
parameters in this case).

--
Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

Quick system summary:
Windows XP Pro, Word 2003, MS Query, MS SQL Server 2000 database.

Problem:
I have a database containing ~150,000 customer records. Each week

we complete transactions with ~1000 customers. Our customer service
department needs to send "Thank You" letters to the customers.

Using MS Query I have written a query to retrieve all customers

within a user specified date range. ( WHERE ClosedDate Between [Start Date]
and [End Date] ) When the merge connects to the Datasource, the saved query,
I am prompted for the Start and End Dates. After entering the dates word
displays an error that it was "Unable to connect to the datasource".