SQL Excel

Dear Al!

First, thanks for that great tool, this really makes querying/retrieving data a lot easier within Excel. I have a similar project (called DBAddin, being open source), however I'm concentrating on two other things:

• flexible retrieval with user defined functions (therefore you get enormous possibilities for query constructions/parametrizations, you can reconstruct your whole query depending on other cell's input)


• inserting/editing data as in Access's datasheet views (however a bit more responsive when DB errors occur). I call this DBSheets

Now my question/suggestion: I'd like to integrate your query engine into the query building of my DBAddin. For that I need programmatic access to your Addin, mainly requiring a public callable function that returns 1) the connection string used, 2) the query constructed.

I think those two Addins could really work together...

-regards,
Roland

Hya Roland

That is great for the add-ins. I just found DBSheets on codeproject and it looks really great. I am going to take a look at them in a few minutes. It is great to get all your feedback and I will try to help with feedback for you later today. Also, thanks for helping out with your expertise here. I see you did really alot of stuff in this area...

Originally I thought about open source but I went against it as I was thinking it would be hard to get people to join in and do useful work over a longer period. I have no clue though

For the integration that is fine by me. Though as mentioned in an earlier message, the query building control is third party. I am looking into this area at the moment myself though and think we can do this fine. Otherwise you could purchase the control yourself and use it directly. But it is a good idea to integrate in some way as the add-ins are similar it seems.

If you want to look into the integration of the addins in someway then this is good. We would have to come up with some kind of friendly agreement or something like that.

For the two points, I am kindof working on the first point. The next release will have the button on the form to generate the ado/vba code for a SELECT. The output could be either a Function or a SubRoutine and will be able to take input parameters.. I have done some basic prototype work on xll format output years ago and have to find this on my machine. Much faster and secure for password protection.. This will be cool to have. But it is alot of work to do well.

For the second point, I am not very familiar with it but I will look at this in your addin later.

Great for all the feedback. Let me know if you want me to link your projects on the links page. Will look at your add-ins in a bit.

Will be back in a bit,
Al

Dear Al!

Sorry, there must have been some misunderstanding. The two points that I made ("flexible retrieval ..." and "inserting/editing data ..." ) were describing my Addin (BTW, the code on Codeproject is quite old and obsolete, I merged DBSheets with DBFuncs and now the whole thing is an ActiveX DLL hosted on sourceforge (If you wait a bit, I'm currently working on version 1.2 that has a few nice additions/bugfixes).

Actually Integration would be much simpler than generating SQL code outside, it just needs a public accessible function (that accepts 2 optional parameters: 1) connection string/ reference code and 2) an existing query statement to be edited).

If connection string/ reference code is not passed, the user has to choose one, if the query statement is not passed, the user has to start building the query from scratch.

Ideally, the passing of the two parameters is BYREF, so the resulting connection string and query statement can be taken from there.

In my Addin, I'd check for existence of your addin (basically trying to create the public function container object and seeing whether there were any errors) and if it's there, just invoke the function that should whip up your addin/query builder. If it's not there, well then it's back to bad old MS-Query (which is sometimes still better than writing query statements from scratch).

Considering the possibility of including active query by myself, I already thought about it until I saw your cool addin and, lazy as I am, couldn't resist to ask.

-regards,
Roland.

Hi Roland

Have just been reading though your documentation for DBAddin and it is really good. Really well done. I am going to install it later on today. Have to go out soon.

I am fine with what that integration. I can add-in the fuction like you mentioned.
I am not sure but I am thinking that it might be good to limit the permission the new call to your project.

So if the call comes from your project then it will go through otherwise it will fail.

I did not think about this situaion before so on first glance that is what I would suggest.

Please let me know what you think.

Thanks again, Al

Hi Roland

Hope all is going good. FYI, I did not get a chance to check out DBAddins yet. I will be doing it. Sorry very busy here at the moment as getting ready for new job.

I will be back on it soon enough.

Also, not sure if you want to do the integration. If you want to, I will add in the new function.
But I was thinking it would be good to limit this to your project - at least for now. The add-in was only built to run in Excel.

So the method will be public but it would be permissioned to only work if called for your DLL.
This can work if you pass in an extra paraemter - say the guid of your addin. And then on the other side I will check it and also check the name of the DLL calling application. This is a basic check but should work fine..

Hope all is good. Let me know what you think when you get a chance.
Thanks and see you, Al

Hi Al!

I'm fine with doing the integration, I think it won't be that much work.
However, I'm not quite sure why you want to restrict the access to just my Addin, I think that this might also be interesting to other applications that want to have a nice and cool GUI for query statement building.

Why would your Addin only run inside Excel? I think that you should be able to start the whole machinery also outside Excel, the only dependencies are the passing back of data (or embedding), which can be disabled if not called from Excel...

-regards,
ROland

Hi Roland ! Welcome to the weekend !!!

That is cool for the integration.
I am thinking you should pass too the name of the embedded query. This way if a user changes the SELECT, the embedded query will be pasted back in with the same properties as it was originally (they would only change the SELECT statement.).
When sqlexcel pastes an embedded query, it just takes a basic set of property defaults, so if a user has something different originally, it would not be good to modifiy it.
If you pass in the name, then the addin can read what the original properties are and paste in back in.
Is that right ? Not too clear as didnt get a good nights sleep !)

For the second point, on one hand I agree that it will be very good to open it up. But on the other hand it will not be that useful to have the query form if you can't use the paste functionality. A user could see the output results of the query in the datagrid and that is good but it would be more useful if they could paste the results in to the application.

I am thinking that there are a bunch of levels to this. Say:
- you could leave it in Excel
- You could restrict it to office only.
- You restrict it to Office and a few other windows applications. Visual Studio, Texpad..
- You could open it up and let any windows applicaion access the public method to load the form.(if you do this, you would have to disable all the pasting for now.)

I think a good place to get it going at least is Excel (and your addin). Then if it is working fine and useful, then open up the toolbar to Office.. It would be cool to have the same toolbar in them also and also in the VBE
And the if that is good and getting good response then open it up to other applications like visual studio, textpad or other places where it would be useful. Then rename the addin to a cool name like SQLMoneky

Another important thing for me is that I am starting a new job soon and wont want to spend crazy time working on this. I will do my best though and it will keep moving forward.. So less users less problems.. So keeping it in Excel only is pretty good for that.

Have to rush out now. Please let me know what you think if you have the time. Sorry I did not get back to you on your addin. Will do it soon but v busy at the moment - no on sqlexcel - Thanks God !

See you later and thanks,
Al

Hi AL!

OK, you really seemed to have misunderstood something. It's not about data passing back, only the query resp. connection string should be passed to and from your addin, basically perusing it as a slick and easy query creation tool.

The data fetchin I take care of within DBAddin, basically putting it into so-called DB functions (e.g. DBListfetch, which is similar to embedded Queries, however having the query statement not embedded, but fully visible/modifiable/parametrisable inside Excel's Cells.. There are other DB functions that have different purposes like filling arbitrary ranges (DBRowFetch), listboxes/dropdowns (DBMakeControl) and single cells with additional "sentence" creation possibhilities (DBCellFetch))

The openness is no problem as it depends on the calling application/addin, what to do with the returned query statement. In Word, I'm filling a DATABASE field function with it, in other applications (powerpoint, etc.) the only possibility would just be to start an ADO session, fetch some data with the query statement (+ the connection string) and paste the results into the application (although that's something that can be done directly from your addin, you'd just have to detect in which application context you're being called, so as to choose the right pasting method).

In case of Access, a VBA programmer could call your Addin via that public function and do the rest using Access's own tools.

But actually I (and my colleagues here) are keen on having it as a cool and easy way to create queries, which directly integrates with what we already use here for DB access (my DBAddin).

-best regards,
ROland

I got it Sorry for the misunderstanding. I am going 90 over here at the moment as trying to get some paperwork done and it is all falling apart. My name on my passport doesnt match exaclty the name on my social security card. And that number doesn't match my bank account name exactly!) They kind of match - alec, alexander, al

That is fine. Thanks for clearing it up. I didn't get a chance yet to run DBAddin...

Ok, I can send you an install package later today or tomorrow. Probably tomorrow as am running around.

If ok, you will pass two strings and get returned and the name will be SQLExcel_GetSQLStatement ??
Also, this will work only for SELECTs at the moment. Also SP's are excluded too at the moment (will add them back) for your addin.

Except some ado objects, I am not using byref in the project at the moment and kind of like it like that.

Please let me know what you think. have to rush again. back later, Al

Hi Roland

Just took a look at this.

I will use byref like you suggested originally. That is the right way here like you mentioned.

This the way I see this now (Let me know what you think please):
- you will call the function passing in the parameter
- there will be 2 new global variables in SQLExcel. These variables will persist the two parameters you are passing in - while sqlexcel is running.
- there will be a new button that you will use to execute return the value to the new function. Could use an existing button but better to seperate these things for now at least.
- Button will update the global variables and pass them back to you in your function as the byrefs.
- Need to make some changes on the form to disblable/enable the controls, so that when the controls that are enabled are relevent to the situation (no need to have paste query, formatting options etc..)

I hope this is good now. I got a rough version working and seems ok but did not test much. Also have to look at the disabling of certain controls and also the new button. The new button not really necessary but we can put it in the same spot as the "Exeucte" and hide the Execute... Something like that.

Please let me know what you think. thanks, Al

'SOMETHING LIKE THIS

Sub LoadSQLStatemenTest()
Dim sConnectionString As String, sSQLStatement As String
Dim oSQLExcel As Object

sConnectionString = "Provider"
sSQLStatement = "Select * From"

Set oSQLExcel = CreateObject("SqlExcel.dsrConnect" )

With oSQLExcel
'//FORM WILL BE LOADED AS VBMODAL
.SQLExcel_LoadSQLForm sConnectionString, sSQLStatement '//PASS IN BYREF
End With

' //NOW YOU SHOULD HAVE THE NEW STRINGS (OR ORIGINAL IF NO CHANGE)

End Sub

Hi Roland

fyi, I got this working from a function. I need to do some cleanup on the form a bit still but the function seems good so far.

Please let me know when you want to try it out. I can send you a copy of the file or put it up for download from a link somewhere. You will just need to register the new version of the DLL

Here is an example VBA function I used to test the function from Excel.

Public Function GetSQLStatementFromSQLExcel(ByRef sConnectionString As String, ByRef sSQLStatement As String)

Dim oSQLExcel As Object

Set oSQLExcel = CreateObject("SqlExcel.dsrConnect" )
GetSQLStatementFromSQLExcel = oSQLExcel.SQLExcel_LoadMainFormtoGetSQLStatement(sConnectionString, sSQLStatement)

Set oSQLExcel = Nothing

End Function


There is a funky thing with this function - it is dependent on a button being pressed in the main form. There is a new button captioned (Get SQL) and when you hit this button, the new function will return the SQL statement..

Please let me know when you want to take a look.
Thanks, Al

Hi Roland

Hope the weekend went fine.

I uploaded the development version of the sql_excel.dll here http://www.sqlexcel.net/dload/temp/
This version has the public functions mentioned above (you will need to register the file to try them out).
The functions are not completed yet (need to handle situations like when there is no valid SQL statement, or missing SQL Statement) but the basics are there I think.
Also, these functions will only work correctly in Excel at the moment.
Need to change the toolbar related functions for them to work correctly in other office applications.

I hope the basics are there though. Thanks and have a nice day. Al

Hi Al!

Wow, that was really fast! Thanks for the function, I even tried it outside Excel, however there were a few error mgsboxes probably related to calls to excel specific functions. What I do in this case is to set a "Application"specific flag in the OnConnection event of the connect class:

Code:

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) On Error GoTo hErr Set theHostApp = Application If theHostApp.name = "Microsoft Excel" Then xlApp = True If theHostApp.name = "Microsoft Word" Then wdApp = True .... End Sub

and then, every time, I use Excel specific objects, I enclose these in

Code:

If xlApp Then ...

statements. Theres just one suggestion I'd like to make: The modifications (or the complete new creation) to the connection string should be somehow passed back as well. If you don't like the usage of ByRef, I'd suggest that the form invocation is separated from the passing back of parameters and the returned value displays the status the form was left/closed (e.g. cancel, OK, (internal/user) error occured, ...). The Connecction string could then be fetched with a function like

Code:

Function SQLExcel_GetLastConnectionString() As String

Also I sometimes experience that the SQL Return button is not displayed, even though it should be. What criteria does the form take for displaying? -regards, Roland

Hi Al!

Ok, I've implemented the check/call already in DBAddin, you can check out the latest build on http://dbaddin.svn.sourceforge.net/viewvc/dbaddin/install/ you need the files (download into one directory): DBAddinP.dll, ConfigFileRegister.reg, DBAddin.reg, DBAddinSettings.reg, install.cmd and (if you don't want to add DBfunctions worksheet functions to your Addin list) addDBAddinToAutomationAddins.xls.

Then simply edit DBAddinSettings.reg to your needs (described in the help file on http://dbaddin.sourceforge.net/HelpFrameset.htm , the latest version is available on http://dbaddin.sourceforge.net/doc.zip) and run install.cmd, followed by addDBAddinToAutomationAddins.xls

Usually I'd provide an installer, but I haven't found time for version 1.2.0 now, and as we're still in matching the call/interfaces I've decided to wait a bit...

-regards,
ROland

Hi Roland

Great you were able to inttegrate it a bit.. Sorry for the problems with it. I know it would not work from outside Excel. I need to change the Excel specific part like you mentioned.

Thanks very much for the example code. That is very useful.. I will use it soon.

I will change this to use ByRef. I had some problems while working on this with the stability of the addin. Originally, I tred load loading the form in Modal but it would eventually crash. Then I though of doing what you mentioned - seperate the form form invocation and then use the GetLast type function. Then finally, I did it the way it is now.

I think if we do it with ByRef it will be best.

Seperately we can add in the GetLastConnctionString - it might be useful sometimes and it is only a tiny function.

For the "Get SQL" button not showing when it should sometimes - sorry. Will check it.
I added an extra boolean called bLoadFromApi function and when you use one of the public functions this should get set to true. When the form is loading it checks this value to see whether or not hte button should be enabled or not.

Back in a second..
Al