MS Query Alternative

[mr tom]

Hi,

I'm bound to Excel for purposes of making my output usable by normal human
beings and I generally use MS Query to query our data warehouse (SQL Server).

The thing is that Microsoft has totally neglected MS Query for well over a
decade. It is primitive, inflexible and just doesn't do the job.

Presuming that nobody has heard MS saying that they're going to either dust
it off or replace it, so failing that has anybody found a better alternative
as an excel add in?

Happy Christmas all.

Tom.

 

[AFSSkier]

I notice no one has reponded to your request. So, here's my attempt to help.

Have you tried using MS Access unstead of MS Query. MS Access in far more
superior than MS Query. I'm running 2007 & use it all the time.

If you've never used Access, LINK to your SQL tables though an ODBC
connection. Create (or copy/paste) your queries in Access, then import your
data into Excel using the From Access connection wizard. The same way your
importing the data now with MS Query.

If you haven't already, try using the SQL server wizard (From Other Sources
in 2007)? My not sure how it works, I don't have direct access to the SQL
server.

 

[Marcotte A]

If you've never used Access, LINK to your SQL tables though an ODBC

connection.

Hi,
Could you give detailed instructions on how to do this? There is File ->
Import Data -> Link Tables but I don't think this is what you mean.

I use MS Query all the time from Excel 2003 to query our ERP database
(Informix SQL) through an ODBC connection. While I have not run into the
limitation the OP talks about, I'm always open to new alternatives that may
be better.

 

[AFSSkier]

Marcotte,

1st thing you need to do is go to your MS Access data:
• Link (do not import) to your data source tables through your odbc
connection.
• Create your query in Access.

2nd, in Excel:
• Go to Data, Import External Data, Import Data.
• File name: Access Databases (*mdb; *.mde) or **(use alternative below)
• Find & select you Access Database file.
• Select your query or table within Access.
• Select the cell in Excel where you want to import the data

** An alternative to the File name process above, you can also select "MS
Access Database (not sharable).dsn. The rest is the similar to above.

Create your cell reference filtering criteria the same way you do with MS
Query.
• Edit Query, Add Criteria, Enter the user parameter prompt like [Enter
Customer ID].
File, Return Data to MS Office Excel, Parameters.
• Get value from the following cell, choose cell in Excel, Ok.

If you choose "Refresh automatically when cell value changes", the Excel
spreadsheet will be refreshed with filtered data based on the cells criteria.

I hope this help you, Kevin
--