SQL Excel

Dear Al!

The embedding functionality could do well with two additions:

• Let the user edit existing embedded queries (the sql button recognizing whether the active cell is located in a data area, where the query could be retrieved from)


• Allow for parameters like in MS-Query, basically being some kind of placeholder (e.g. ?) that is then used for linking cell values to the query.

-regards,
Roland

Sorry Roland, missed this point earlier. I think that is a great idea. This came up before on the forum and I think it will be very good.

At the moment, am thinking of functionality as follows:
- If a user embeds a query into the worksheet, a VBA function will be added in to a module within the workbook. The new function will add the right click shortcut access when in a querytable range. The new shortcut will allow the user to auto-load the main form with the SQL statement (for modification of the statement). There might be some problems with this when the vba project is protected. Will look into other ways of doing it.
- Have an additional toolbar button on the toolbar that will be enabled if there is a embedded query in the active workbook. This button would load some sort of control panel for the embedded queries. So from there you could either modify a query or modify some other property of the embedded query - cell range, name or something like that. This functionality would be a simple way to maintain a bunch of embedded queries.

Not sure when this will get done though !)

See you later, thanks, Al

I would love both options, although difficult of course. We talked about option 2 for the auto refresh where you can update multiple at once

Oh, it doesn't check if there is already an embedded query with the same name. If there is already "whaa", then it makes "whaa_1" of the second one. I would rather have a notice that there is an embedded query with that name already. Maybe easy to implement if you work with a list of all the queries (option 2 in your post)

Hiya Blitzoff !

Thanks.

For the updating of the multiple it will be going in soon enough. But not this weekend unfortunately.. I just got a new job and have to prepare for it and not too much time now.
But I will be adding this in.

Idea is to:
- have an extra toolbar button which will load the embedded queries form
- The new form will show all embedded queries in a worksheet. And have the possibility to change there properties in the form.
- Have a new right click item that will only show when you are in a querytable. So you can quickily load the main form with the SQL statement loaded.
- Also, if you click the "SQL" button when you are in a querytable the main form will default to the SQL statement of the querytable (Roland's suggestion above).

I did try this to do something on this already but ran into some problems. Will get it done sooen enough I hope..

Thanks for reminding me again. It is on the list!

Thanks vm Blitzoff,
See you later,

Sorry, forgot to mention, for the
"check if there is already an embedded query with the same name"
I will change it to the way you suggest.. That is a better way I think too.

Thanks and see you later, Al

Nice Post, but can't seem to find the response to the parameter part of Roland's question. I ask, because without the ability to pass variables from the worksheet through to SQL server, this reduces the appeal of this tool. MS Query, and other editors I've tried all allow for parameters, but only work outside of the query, (non nested) and can't seem to be passed through to a stored proceedure in the ? (placeholder state). Was hoping your tool may do that....

Hi MerlinJnr!

If you can live without the beauty of creating Queries visually with SQL-Excel (which you usually do only once when formulating the query) and you're p...ed off by the limitations of MS Query then I can recommend the DB-Addin tool I've written just because of that (http://dbaddin.sourceforge.net) . If you're downloading the latest version it also works with SQL-Excel.

BTW: I'm waiting on a new version of SQL-Excel to come around the issues with being called from DB-Addin...

-regarsd,
ROland

Cheers Roland, I'll take a look

Hi Guys

Sorry for the delay in posting.

Thanks Roland for coming in on this. I think as Roland mentions the best way for this is to use DB Addin (did not try it myself yet but will one of these days).

For the SQL Excel addin, development has slowed big time. Had a bit of a perfect storm when my laptop died and got canned at the same time

But will be back on track soon.

Roland, nice to see you here again For those items we were looking into before - office integration of SQL Excel - wil be back on this soon enough. I did do a good part of it but didnt get good testing done yet.

Thanks guys and have a nice night.

Al