I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...

When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".

I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?

Perhaps I should insist that our IT department upgrade. :p

Thanks in advance for any assistance.

- Matt

Can you use the union to drive a make table query and base the pivot on that? Or append the union data to an existing table might be a more elegant solution.

Thanks for the response! I've been using the "make table" work around for a number of years... Due to the nature of these reports pivot table reports, which are viewed by a number of various users, I needed a way to avoid that process.

In speaking with our IT folks, we'll attempt to add the 4.0 Jet OLEDB Driver to the server (don't know if that works with Office 2000 yet). But hopefully, this will make the final case for finally upgrading to Office 2003.

Thanks again for the help!

- Matt

Ok, can you base a select query on the pivot and base the pivot on that?

One would think that would be possible. What I have found, is that if you have a union query referenced anywhere in a set of multiple queries, the Access ODBC Driver fails to pull in the data. So, making a standard SELECT query from a union query won't trick it. :( But thanks for the suggestion.

From what I now understand, it's essentially a driver issue...

I have had success using union queries as the data source for excel pivot tables. This success came after much trial and error. I hope my experience is able to help anyone else who needs this functionality.

Rule# 1- Do not use double quotes in your union query anywhere.
It may work in access but it will screw things up in the Jet data engine. It will also cause the "too few parameters" error.

Rule #2- Use ms query to create the pivot table. To do this go to the data tab--> from other sources --> from ms query. Choose ms access database. Select your query and data fields. Import Data into a pivot table.

For some reason union queries are not visible using the insert-->pivot table--> use external data range method.

Tip #1 - you can add your union query to a select query and then add other related data fields to the data set. If you have calculations to make, I advise you to do them in the select query. Calculated fields created inside the pivot table tool are subject to more restrictions.

Good luck

Toom21 (using excel2007+access2003)