If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Query Datasource with Parameters
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 |
#2
|
|||
|
|||
MS Query Datasource with Parameters
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) a 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/ "G_D_Roberts" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|