ACC: "Too few parameters" When Using MS Access Data in MS Query

• Microsoft Access versions 7.0, 97
• Microsoft Query for Windows 95, versions 2.0, 8.0

SYMPTOMS

When you create a parameter query based on a Microsoft Access database in Microsoft Query version 2.0, you receive the error message "Too few parameters. Expected <n>." If you click Help in the error message box, the topic states, "This error was returned by the driver. For more information, check the documentation for the driver you are currently using."

This error also occurs in any version of Microsoft Query when you open a parameter query created in Microsoft Access version 7.0 or later.

NOTE: This behavior also occurs using the Mail Merge Wizard to merge a Word 95 document with a parameter query in Microsoft Access 95.

CAUSE

This is a limitation of the Microsoft Access ODBC driver. Prompting for query parameters is a functionality of the Microsoft Access user interface. However, the Microsoft Access ODBC driver doesn't have this capability; therefore, it simply generates an error message.

WORKAROUND

Parameter Specified in Microsoft Query Version 2.0

Although the prompt for criteria is not available in Microsoft Query version 2.0, you can click Criteria on the View menu, and then change the contents of the Criteria field's Value row to view different query results.

Parameter Specified in Microsoft Access

In Microsoft Access, create a make-table query based on the parameter query; run the make-table query, and then use the resulting table in Microsoft Query.

If you are using Microsoft Query to make the results of a Microsoft Access parameter query available to Microsoft Excel, you may want to consider exporting the parameter query to a Microsoft Excel file from within Microsoft Access.

MORE INFORMATION

Steps to Reproduce Behavior

Example 1: Parameter Query Created in Microsoft Query

1. Start Microsoft Query version 2.0. On the File menu, click New Query.

2. Select MS Access 7.0 Database or MS Access 97 Database in the Select Data Source dialog box and click Use.

3. In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.

4. Select Current Product List in the Add Tables dialog box and click Add; then, click Close.

5. Add the ProductName field to the query.

6. On the View menu, click Criteria.

7. Select ProductName from the list in the Criteria field and type "[Enter a Product Name]" (without the quotation marks) in the Value row.

8. On the Records menu, click Query Now.

   You receive the following error message:

         Too few parameters. Expected 1.
   

Example 2: Parameter Query Created in Microsoft Access

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Open the Current Product List query in Design view.

3. On the Criteria line of the ProductName field type "[Enter a Product Name]" (without the quotation marks).

4. Save and close the query; quit Microsoft Access.

5. Start Microsoft Query.

6. On the File menu, click New (or New Query in Microsoft Query version 2.0).

7. If you are using Microsoft Query version 8.0, select MS Access 7.0 Database or MS Access 97 Database in the Choose Data Source dialog box, and then click OK.

   If you are using Microsoft Query version 2.0, select MS Access 7.0 Database or MS Access 97 Database in the Select Data Source dialog box, and then click Use.

8. In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.

9. Select Current Product List. If you are using Microsoft Query version 8.0, click Next twice, and then click Finish.

   If you are using Microsoft Query version 2.0, click Add in the Add Tables dialog box and click Close; then add the field ProductName to the query.

   You receive the error message:

         Too few parameters. Expected 1.
   

REFERENCES

For more information about parameters, search the Microsoft Access Help Index for "parameter queries."

For more information about make-table queries, search the Microsoft Access Help Index for "make-table queries, creating," and then display the topic "Create a new table from the results of a query with a make-table query."

For more information about exporting data from Microsoft Access to Microsoft Excel, search the Microsoft Access Help Index for "Excel, exporting Microsoft Access data to Microsoft Excel," and then display the topic "Export a table or query to a spreadsheet."

Keywords          : OdbcProb kberrmsg kbinterop
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided