Query Editor Add-In

Query Editor is something which I’ve commented about but never really made a main post on.
It’s an add-in I made for grouping all of the great External Data features into one convenient User Interface.

It’s like a little party where all the Excel database goodies are invited… except Microsoft Query.

Query Editor handles OLEDB and ODBC QueryTables.
OLEDB has things which ODBC doesn’t and vice versa. For example, OLEDB QueryTables can be saved as ODC files. ODBC supports the use of Parameters.
I favour ODBC QueryTables, but only because it supports Parameters. If OLEDB QueryTables (the newer technology) supported Parameters then I’d be there in a shot.

If ever you’ve struggled with Microsoft Query, Multiple QueryTables, Parameter Queries, or External Data in general, you’ll probably find this Add-In handy.

So break out your favourite SQL Editor, crank out some SQL and have fun.

Download Query Editor

Click for Full_Sized Images


ODBC Parameter Query

ODBC Parameter Query to a Stored Procedure
Posted in Uncategorized

19 thoughts on “Query Editor Add-In

  1. Rob,

    Excellent to see this post about Your nice utility :)

    It works well with my Swedish version as well as Finnish version.

    Kind regards,
    Dennis

  2. Looks very good Rob.

    What SQL editors are poeple useing? Last time i did any SQL work (a long time ago) i use a thing called “TOAD”

  3. Oracle is case sensitive, picky and sluggish, a real annoyance.

    Just for the balance :-)
    Best wishes Harald

  4. Thanks Harald :)

    I believe that Oracle is like other databases & softwares/tools, some people love it while other hate it and the largest group is between these two points ;)

    Kind regards,
    Dennis

  5. I like =SQL.Request( ). I used it tonnes a while back when I needed a lot of little bits of data like a summary sheet.

    You can use it for building a report with all the yucky formatting that users tend to like and the SQL is there in the formula for all to see.

    I knock together a quick little xlPasteSpecial and I have an instant batch of reports for the morons to read.

    (needless to say you have to run the ODBC addin)

    P.S. I always try to write my own SQL by hand. That way you remeber which tables to join and can speed up the efficiency.

  6. I tried to download the Query editor and ended at Van Gelder webstite which is teh web site of one of the people that have left comments.

  7. I’ve been getting a bit of mail recently about compatibility with Office 2010.

    The deal with Office 2010 is that it appears to break the Common Controls library.
    The fix is to run cmd as Administrator, and execute the following command:
    REGSVR32 MSCOMCTL.OCX

    It is important that you run it as Administrator.
    That gets it running under Office 2010 for me.

    Cheers

  8. Rob, as of yesterday when the latest round of MS patches was installed, QueryEditor stops working, and causes the following “Microsoft Visual Basic” error message every time Excel 2003 is launched or exited: “Object library invalid or contains references to object definitions that could not be found.”. I am on WinXP SP3 and running Office 2007 SP2. I suspect MS12-027, since it monkeyed somehow with MSCOMCTL.OCX.

    Any chance you could work around this issue for us somehow? Or is there anything I might be able to do to work around it on my PC?

  9. Quick update – I tried re-registering MSCOMCTL.OCX, and that cleared up my problem. I had read Rob’s entry from 8/25/10, but since it referenced Office 2010 I figured it wouldn’t apply in my situation. I was wrong!

  10. Same thing happens this morning. Thanks Kevin for finding the solution. I would have been desparate!

  11. I’m recently having some issue with the parameters list. Can’t right click! Really strange. Is someone had that problem? i’m on Office 2007.
    Rob, if your still in the loop, are you willing to make a update to the tool? If not, would you need a hand? This add-in is priceless, use it everyday! Nothing else i know that compare to it, would be sad to loose it! Thx

  12. Dear Alex,

    This is one great tool. Really Rob did a fantastic job. I am using this tool for quite some time and I am able to achieve so much of success with this tool. Starting from 2007 version adding second parameters is not possible and hence once you create the first parameter then you use the Excel Data Connections properties to create additional parameters.

    Ashok

  13. hi Rob,

    i get a message .. after I use “parameters”

    Microsoft Visual Basic
    Runtime error ‘7’
    Out of memory

    does it means my query too complex or else..


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.