Pls recommend a replacement for Microsoft Query

  • Thread starter Thread starter Vadim Rapp
  • Start date Start date
V

Vadim Rapp

Hello:

could anyone recommend a third-party solution to query sql server database
from Excel, a replacement for the stinking Microsoft Query?

thanks,

Vadim Rapp
 
Vadim

I don't have a suggestion, but is there anything specific you can't do with
the sql/msquery combo?

I've not used sql, but regularly use Client access with AS400 and Access.
Mostly flawlessly. I tend however to write the query in the server tool,
rather than try and query in MSQuery

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hello Nick:
You wrote on Mon, 19 Jul 2004 20:34:49 +0100:

NH> I don't have a suggestion, but is there anything specific you can't do
NH> with the sql/msquery combo?

If the sql query is any less than trivial, MS Query says that it can't be
displayed graphically. And if it can't be displayed graphically, then it
can't have parameters. Which means that in a sales report, for example,
there's hardcoded dates' range, instead of a prompt or reference to the
cell. Which means that the next year either the user forgets that she needs
to send the report to the developer, and gets wrong data; or she remembers
it and there's one more "project" to change 2004 to 2005 for couple of
hundred dollars. I would rather pay them for a more capable query tool.

Vadim
 
Vadim

I'm not questioning your right to have a 'more capable' query tool, but for
reports I use Pivot tables all the time 'through' MSQuery. These can very
simply be asked to include or exclude any dates contained in the underlying
data, either through inclusion/exclusion in a column or row field or
selection in a page field. Granted, sometimes getting the user to accept the
'usability of these tools is a 'step too far', but those provided in the
setups mentioned earlier have served me well for summarising sales reports
with many hundreds of thousands of underlying records with 30-40 fields.

Additionally, with a query set up as a parameter query, it is relatively
simple to retrieve a year for example in the external data, either from a
cell or user input to filter the output, both in the user interface and
programmatically

As I say, the tools are probably adequate for most uses, but I allow someone
else to offer a better 'tool'. There undoubtedly will be but I doubt it too
will be able to be easily worked by the user. (SQL for example has an OLAP
interface, but I would struggle to get it working, without the models being
pre-built)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Vadim Rapp said:
If the sql query is any less than trivial, MS Query says that it can't be
displayed graphically. And if it can't be displayed graphically, then it
can't have parameters.
Click to expand...

That doesn't sound correct. If the sql is any less than trivial, MS
Query passes it to the DB server for execution, so it is a question of
whether your DBMS supports parameters. SQL Server certainly does.

The best approach is to create a stored procedure, with input
parameters, on the server and call the stored proc from Excel. You can
use MS Query to call the proc (even create it if you have the
permissions!). However, for a more capable data access technology,
which you will undoubtedly already have, check out ADO.

Jamie.

--
 
I find MS Query very good with just one major loop hole... outer joins with multiple tables.

Not sure if this is on Microsoft's radar...

For now, we have to create a view (not the end of the world) but adhoc flexibility is lost.

Any other ideas, world?
 
Back
Top