> I am using MS Excel 2003 querying a MS Access 2003 desktop .mdb application
> which is sat on a network server.

> I have several select queries using a SQL union query as their data source.
> Non of these select queries are visible from the connection wizard.  Tables
> and queries not associated with the SQL union query are visible.

> I am currently using a make table query which I run via an event on my Excel
> file to update a table prior to refreshing the Data Query on the worksheet
> which is pointed at the table.  This works but is not very elegant hance my
> interest in your break through. I can understand your satisfaction.

> "Rob" wrote:
> > On 28 Mar, 02:59, Kevin N <Kev...@discussions.microsoft.com> wrote:
> > > Rob,
> > > This holy grail has been quest so I am really greatful for your posting.

> > > Unfortunately when I try this the Data Connection Wizard - Select Database
> > > and Table screen (your step 11) does not display any select queries that
> > > select from a SQL union query!

> > > Do you have any suggestions?

> > > "Rob" wrote:
> > > > Dear all,

> > > > For some years I have been using MS Access to manipulate data into a
> > > > required format and then displaying it using Pivot tablesin Excel .
> > > > The thorn in my side with this approach has always been union queries
> > > > which, except in certain circumstances, will throw an error (too many
> > > > parameters.  expected n) when you try to connect using ODBC and MS
> > > > Query.  I have used the workaround that union queries are allowed if
> > > > all the fields are numbers, i.e. no text fields.

> > > > However, as of today, I have discovered a solution in Excel 2003/
> > > > Access 2003 (which possibly also works in Excel XP/Access XP but I
> > > > haven't checked) which allows you to connect directly to a union query
> > > > and display the results in a pivot table.  This is my own personal
> > > > holy grail so I thought I'd share it.

> > > > The problem with union queries is down to the MS Access ODBC driver
> > > > not dealing with them correctly, so the solution is to avoid using
> > > > ODBC.  In its place we can use OLEDB, but that throws up the problem
> > > > of which OLEDB driver to use.  The Access OLEDB driver doesn't support
> > > > union queries either BUT the Jet 4.0 OLEDB driver does.  However, in
> > > > order for the OLEDB driver to be able to see the union query we have
> > > > to dress it up in a simple select query because it ignores everything
> > > > but tables and select queries.  The final part of the problem was how
> > > > to get the data straight into a pivot table.  This bit is simple
> > > > enough just by following the instructions in XL - rather than using
> > > > the pivot table wizard, use the get external data wizard and then
> > > > select to show the results in a pivot table.

> > > > So that's the long and short of it, here is the process to link xl to
> > > > a union query:

> > > > 1.  Create your union query in Access (called MyUnion for this
> > > > example)
> > > > 2.  Create a select query along the lines of 'Select * from
> > > > MyUnion' (called SelectMyUnion)
> > > > 3.  In Excel, select Data\Import External Data\Import Data...
> > > > 4.  Select '+Connect to New Data Source.odc'
> > > > 5.  From the list of data source types, select 'Other/advanced'.
> > > > Click next.
> > > > 6.  From the list of providers, select 'Microsoft Jet 4.0 OLE DB
> > > > Provider'.  Click next.
> > > > 7.  Click the ... button to select the Access database that contains
> > > > SelectMyUnion.
> > > > 8.  Enter any details you need to log in to the database, or just
> > > > leave it blank if there aren't any.
> > > > 9.  Click 'test connection' to check it's working.  If not, check your
> > > > account details and the db
> > > > 10. Click OK. (you may have to wait a while for the next dialog box to
> > > > appear - not sure why)
> > > > 11. Select 'SelectMyUnion' from the list of specific tables OR just
> > > > untick the box. Click Next
> > > > (note that if you untick the box, you will be prompted to select a
> > > > table whenever you use the connection)
> > > > 12. Give it a filename, e.g. MyUnionQuery.odc, and anything else you
> > > > want . Click Finish.
> > > > 13. If you didn't select a table in step 11, do so now.  Select
> > > > 'SelectMyUnion', click OK.
> > > > 14. On the import data dialog, select 'create a pivottable report'.
> > > > 15. You are now magically at step 3 of the pivot table wizard where
> > > > you can carry on as normal
> > > > 16. Click Finish and hey presto, there is your union query data in a
> > > > pivot table.
> > > > 17. If you need to use the connection again, you can just select the
> > > > saved .odc file from the Import Data bit (step 3).

> > > > I knew there had to be a way to do it.  Hopefully this will save
> > > > someone else 4 years of mucking about!

> > > > Rob

> > unfortunately, not really.  If you have followed the instructions
> > above and are using XL 2003 with Access 2003 then I'm really not
> > sure.  I've had a look through the process again and there are no
> > settings I can find that would hide the queries.

> > Can you see the other select queries in your database that are based
> > on local tables?
> > Is the 'select query' that looks at the 'union query' a 'pass-through
> > query' because I'm not sure p-t queries show up.

> > If you give me a bit more info on your setup, I'll see what I can do.

> > Rob