Making Union Queries work between Excel and Access
I have just succeeded in getting Microsoft Excel 2007 to show the results of a query written in Access 2oo7.
No big deal you might think – except that the query is defined in Access as a Union Query.
Not only that but it requires parameters.
Background: Lots of people seem to be having an issue with Excel complaining that ‘Too Few Parameters. Expected <n>.
<n> here can be any number really. Depending upon what the ODBC driver determines is appropriate for the query.
And herein lies the problem. The ODBC driver for Access is not capable of doing this…
I’ve just spent about 4 hours figuring out exactly what setup does work though…so keep reading.
If you copy and paste the connection string from the wizard into some boilerplate VBA code that uses ADO to connect then when you do a cmd.Parameters.Refresh you will find these parameters have strange names such as Pa_RaM000.
Again the problem is the ODBC Driver – you need to force the Excel Data Wizard to use the OLEDB Driver.
You do this by clicking ‘Connections’ on the Data Tab. Then clicking ‘Add’, then clicking ‘Browse For More…’
Next choose: +Connect to New Data Source (or click the New Source button)
Next choose: Other/Advanced and click Next
Choose the appropriate OLEDB provider. For Access 2007 this is: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Configure the provider and choose any table you like for now (we will change it in a minute).
Once the connection has been successfully created you can edit it within Excel.
Select Connections from the Data tab again. Choose your connection. Click Properties. Select the Definition Tab.
From the CommandType: dropdown you can now select ‘SQL’.
Unfortunately the parameters button will not be enabled but you can call a parameterised Union query like so: EXECÂ MyUnionQuery “param1″, “param2″, “param3″;
And bingo – that is it.
You should now hopefully have achieved in a few minutes what took me several hours – I spent a long time searching through newsgroups and found little real help. Figured it out through trial and error in the end. I do wish Microsoft would pick this up and fix it in Office 2010 if it isn’t already. I understand that they might not be able to fix the ODBC driver for Access for various reasons but at least the Data wizard should make it a lot easier to do what I have layed out here. The original error when you try this in Excel with a predefined ODBC connection to Access is just so unhelpful to the average user.
If enough people ask me to I might write this up a little clearer with some images – it’s now very late and I’m going to go to sleep.