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
|
|||
|
|||
Too Few Parameters error Mail Merge Access Parameter Query
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 ToolsLetters and MailingsMail 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? |
#2
|
|||
|
|||
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. -- Peter Jamieson "Tony_VBACoder" wrote in message ... 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 ToolsLetters and MailingsMail 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? |
#3
|
|||
|
|||
Peter, thank you for the reply. Some answers to 2
questions you asked: 1.QIf 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. -----Original Message----- 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. -- Peter Jamieson "Tony_VBACoder" wrote in message ... 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 ToolsLetters and MailingsMail 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? . |
#4
|
|||
|
|||
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. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for the reply. Some answers to 2 questions you asked: 1.QIf 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. -----Original Message----- 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. -- Peter Jamieson "Tony_VBACoder" wrote in message ... 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 ToolsLetters and MailingsMail 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? . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
How do I run a query in ACCESS within restricted parameters? | Sarah (Sustainable Food Center) | Running & Setting Up Queries | 1 | September 29th, 2004 06:56 PM |
slow merge Access query into Word | Mad Mo | Running & Setting Up Queries | 1 | September 12th, 2004 11:30 PM |
Using a query with a replaceable parameter as the source of a control | Hoo | Running & Setting Up Queries | 1 | May 30th, 2004 07:07 AM |