|
|
microsoft.public.excel.querydao |
Thanks for your prompt response. I am using MS Excel 2003 querying a MS Access 2003 desktop .mdb application I have several select queries using a SQL union query as their data source. I am currently using a make table query which I run via an event on my Excel > > Unfortunately when I try this the Data Connection Wizard - Select Database > > Do you have any suggestions? > > "Rob" wrote: > > > For some years I have been using MS Access to manipulate data into a > > > However, as of today, I have discovered a solution in Excel 2003/ > > > The problem with union queries is down to the MS Access ODBC driver > > > So that's the long and short of it, here is the process to link xl to > > > 1. Create your union query in Access (called MyUnion for this > > > I knew there had to be a way to do it. Hopefully this will save > > > Rob > unfortunately, not really. If you have followed the instructions > Can you see the other select queries in your database that are based > If you give me a bit more info on your setup, I'll see what I can do. > Rob
which is sat on a network server.
Non of these select queries are visible from the connection wizard. Tables
and queries not associated with the SQL union query are visible.
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.
> > and Table screen (your step 11) does not display any select queries that
> > select from a SQL union query!
> > > Dear all,
> > > 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.
> > > 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.
> > > 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.
> > > a union query:
> > > 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).
> > > someone else 4 years of mucking about!
> 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.
> 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.