Too Few Parameters error Mail Merge Access Parameter Query

[Tony_VBACoder]

Does anyone know how I can get a Word 2002 Mail Merge to
successfully connect to an Access 2002 parameter query
without getting the "Too Few Parameters" error? I have
looked at the following MSKB article relating Word 2000,
but Word 2002 is quite different:
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;214183

My system setup is:
* Word 2002
* Secured Access 2002 database
* An ODBC Entry named "SAMS" that points to my Secured MDB
using the correct "System Database" MDW file.

The problem I am having is:
1) Start Word 2002 and go through the Tools>Letters and
Mailings>Mail Merge Wizard
2) On Step #3 of the Wizard, I select "Browse". This
brings up the "Select Data Source" dialog box
3) At the top of the form in the upper-right corner, I
click on the "Tools" drop down arrow and select "MS Query"
4) This brings up the "Choose Data Source" dialog box. I
select my ODBC Entry titled "SAMS" from the "Databases"
tab, which is set to point to my secured DB
5) This loads the Query Wizard where I select my Access
Parameter Query from the "Available tables and columns"
list. When I select my parameter query, all the columns
in the query are available to select. I select my
necessary fields that I want to show in my Word Mail Merge
document and click the Next button
6) Click Next twice to get to the "Finish" screen. Here,
I select the "Return Data to Microsoft Word" radio button
and click the "Finish" button.
7) At this point I get the "Too few parameters. Expected
n." error, where n is the number of parameters in my
parameter query.

How do I get around this "Too few parameters. Expected n."
error?

 

[Peter Jamieson]

If you display the SQL using the SQL button in MS Query, do you see the
parameter placeholders? In order to get MS Query to show the data, do you
have to fill in the parameter values?

The thing is that Word simply has no mechanism to ask for the parameter
values in an Access query (or any other query type). In all probability when
you "return the data" from MS Query, what is actually happening is that the
Query with the parameter placeholders (not the query with the parameter
values filled in) is being returned to Word. If it was set up in MS Query,
Word will then try to execute the query using ODBC and will fail because
Word does not have code to provide the parameter values prior to issuing the
SQL wwith parameter placeholders.

As far as I know, the only way to get Word to execute an Access parameter
query is to connect to Access using DDE. You /might/ be able to do it by
saving the MS Query query as a .dqy and issuing an OpenDataSource that opens
the .dqy in such a way that Word always opens MS Query to get its data, but
as far as I can remember that doesn't really work either. As I suggested
before, if you can't use DDE I think the only way you will be able to do
this is to use code to get the SQL query with the parameter placeholders,
fill in the parameter values yourself, then issue that SQL, e.g. using
OpenDataSource.

 

[Tony_VBACoder]

Peter, thank you for the reply. Some answers to 2
questions you asked:

1.Q>If you display the SQL using the SQL button in MS
Query, do you see the parameter placeholders?

No. If I go back through the Wizard and at last step I
select the "View data or edit query in Microsoft Query"
radio button and click "Finish", I get the "Too few
parameters. Expected 1." error. If I click the "OK"
button it takes me back to the last step of the wizard.
Then instead of clicking the "Finish" button, if I click
the "Cancel" button and then click on "Yes" to the "Do you
want to continue editing this query in Microsoft Query?"
dialog box, I once again get the "Too few parameters.
Expected 1." error. But this time, if I click OK, I am
taken to the Microsoft Query grid, where only my query is
the only table in the grid (this is the Access Parameter
Query"). Then, if click the "SQL" button to show my SQL,
there is no parameters placeholders, just a very simple
SQL statement (not even a WHERE clause). Sample below:

SELECT <field1>, <field2>, ...etc
FROM <AccessParameterQuery>


2.Q> ...as I can remember that doesn't really work either.
As I suggested before, if you can't use DDE I think the
only way you will be able to do this is to use code to get
the SQL query with the parameter placeholders, fill in the
parameter values yourself, then issue that SQL, e.g. using
OpenDataSource.

Where would I place this code you speak of? I know how to
use QueryDefs within VBA/Access to supply the Parameter
values of a Parameter Query, but how would I get the Word
Document to bind to this? Or, is this code I would place
within the Word Document?

I cannot believe that to accomplish what I want to is this
difficult and requires this many "Hoops to jump through"
to get what I need. I wonder why Microsoft has not
updated their "http://support.microsoft.com/default.aspx?
scid=kb;en-us;214183&Product=acc2000" document for Word
2002.

 

[Peter Jamieson]

We may be talking at cross-purposes here.

I cannot believe that to accomplish what I want to is this
difficult and requires this many "Hoops to jump through"
to get what I need. I wonder why Microsoft has not
updated their "http://support.microsoft.com/default.aspx?
scid=kb;en-us;214183&Product=acc2000" document for Word
2002.

Well, that article essentially says "you have to use DDE", and in that
respect, nothing has changed in Word 2002 except the way you do that in the
user interface. You have to check Tools|Options|General|"Confirm conversions
at open", then go through the database selection process again and select
the DDE option when offered.Programmatically, you need to add the parameter

Subtype:=wdMergeSubTypeWord2000

in your OpenDataSource call.

I thought I'd said that already, but perhaps it was in answer to the very
similar query that popped up about the same time.

I cannot believe that to accomplish what I want to is this
difficult and requires this many "Hoops to jump through"
to get what I need.

If you do not want to use DDE (e.g. because of the complication that it
needs to start Access etc.) then I'm afraid it probably is that difficult. I
wish it were not, but it's certainly the only way I can think of doing it.

Where would I place this code you speak of? I know how to
use QueryDefs within VBA/Access to supply the Parameter
values of a Parameter Query, but how would I get the Word
Document to bind to this? Or, is this code I would place
within the Word Document?

You might do something like:
a. get the query text programatically using DAO or ADO
b. fill in the actual parameter values you need to use (i.e. create the
actual SQL query text you want to issue, not use the parameter-specification
facilities of DAO/ADO ). If you are getting parameters from the user you
need to be careful to avoid SQL injection hacks.
c. use OpenDataSource to open the Access database, providing the SQL you
constructed in SQLStatement and SQLStatment1 if the query is longer than 255
characters (but there may be an error in Word 2002 that limits the total
length to 255 or so anyway).

Yes, not something I would want to have to do.