VSTO & .NET & Excel

October 5, 2006

Develop DB Code Generator Wizard with VB.NET?

Filed under: .NET & Excel — Dennis M Wallentin @ 3:13 pm

Last year I started a new project to develop a COM Add-in for Excel, the DB Code Generator Wizard. For some reasons I decided to delete one vmWare setup where, of course, the code project was developed on. At that time I was not aware of that I had no backup for the utility. What is left of it is only some screenshots. Now I wonder if it of interest that it will be re-created or not.

The following links show the screenshots of the DB Code Generator Wizard:

  • DB Code’s Main tab
    In this view there are some central settings that need to be configured. When clicking on the ‘Create Procedure’ button a complete VBA function is created which retrieve the data from the database.
  • SQL Query’s tab
    In this view new SQL queries can be created and tested and when clicking on the ‘Test’ button a new tab is viewed with the output of the query.
  • Parameter’s tab 
    In case of using parameters for the queries these are controled in this view.
  • Setting’s tab
    Here are the general databases and QueryTables settings handled.
  • Connection’s tab
    Here You can administrative the available connection strings.
  • SQL tab
    In this view You can administrative the configured SQL expressions.

In addition to the above it also offer another utility, Code Snipper, which allows developers to store complete procedures as well as snippet code.  Saving and using the code will be available via a toolbar in the VB-editor.

All information is stored in a local Microsoft Database (so called ‘Access’ database). 

It can be used with most of the common databases. However, one idea I has is to implement stored procedures but it will then only target SQL Server 2000 / 2005.

If it will be re-created it will be developed with VB.NET 2005 and distributed as freeware.

The following requirements must be met in order to use it:

  • .NET Framework 2.0 or 3.0 (depending on when it’s released).
  • Excel versions 2003 or 2007.

Let me know what You think about it from a general view. More specific, if it interesting to take part of the development via the blog or not. Also if something is missing which You would like to see as part of the utility.

Kind regards,
Dennis

Ps. In my next post (next week) I will introduce the case that will be used for creating a utility for Excel from VBA to VSTO.

Advertisements

9 Comments »

  1. Looks useful to me indeed, Dennis.
    No additions come to mind right now though.

    Comment by Jan Karel Pieterse — October 5, 2006 @ 4:04 pm

  2. Hi Dennis,

    I think that looks very useful indeed. I don’t have an SQL server, but I’ve been busy playing with Excel/Access interfacing using ADO. I’ve also seen quite a few questions about it in the forums lately.

    I can’t think of anything else to add to your screenshots right now, but if I can be of any help whatsoever in developing this, you know how to find me. 🙂

    Re the VMWare partition, I store all my data on a shared (host’s) drive. Now I know why! LOL!

    Comment by Ken Puls — October 5, 2006 @ 7:34 pm

  3. My goodness that looks really good, very impressive… I can’t believe that it got destroyed. 😦 😦 You’re not really going to re-do all that from scratch, are you?

    But if you do, I’m sure that many would find it useful… Starting with myself!

    Comment by Mike Rosenblum — October 5, 2006 @ 9:10 pm

  4. Hi guys,

    Thanks for the input so far 🙂

    >>You’re not really going to re-do all that from scratch, are you?

    Yes, I will do it with VB.NET and in that way I hope to learn more about the interaction between .NET and Excel.

    In general I refuse to rebuild anything as I have already gone through the process, i e to repeat is not what I call learning.

    Here I make an exception because it serves three things well:

    Knowledge is best when it can be discussed and reviewed in public and this blog tries to support it.

    When it comes to .NET we all are in the process of learning, especially when MSFT release new versions of the .NET Framework with a tight schedule.

    In order to maintain an interest for the blog it needs to have input for a long ‘life’. It needs at least 52 blogposts (of high quality!) on a yearly basis (1 post / week). Except for that I like to use one case for several blogposts.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 5, 2006 @ 11:32 pm

  5. Hi Dennis,

    Firstly, support for SQL Server stored procedures would be good. One excellent addition to reporting services (IMHO) was the ability to base queries on a stored proc, even a parameterised one (I’ll be blogging about it!).

    In order to make your model fully up to date, I think stored procedures would be a handy addition (at least for someone who will use it with MSFT SQL Server 2000/2005).

    Regards,

    Will

    Comment by Will Riley — October 6, 2006 @ 10:23 pm

  6. Dennis,

    If you are interested, I have now put that post about Stered Procedures in Reporting Services on my blog…

    http://wills-blog.com/?p=48

    Comment by Will Riley — October 6, 2006 @ 11:33 pm

  7. Will,

    Many thanks for the link to Your blogpost about stored procedures and Reporting Service 🙂

    As for stored procedures I hear You and I’m thinking of a smaller project that focus on using parameterized stored procedures on the .NET platform. After the exploring and test phase it then can be added to the DB Code Generator Wizard.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 7, 2006 @ 10:23 am

  8. The devil is in the detail of course but IIRC I read in a VB.NET EUA that I was forbidden from writing an ‘Access-style’ front end to a mdb file. I figured I would be OK for a regular application that used a mdb for it’s data store but I made a mental note to check the small print should I ever get into the business of writing database management apps.

    Jamie.

    Comment by Jamie Collins — October 9, 2006 @ 4:17 pm

  9. Hi Jamie,

    Thanks for the information. It’s interesting to learn and I’ll add it to my list of ‘keep an eye’ when developing tools. I do believe it will more important for those developers that implement VSTA in developer tools.

    I remember that when VB.NET 2002 was released the EULA explicit stated that we were not allowed to compare in public VB.NET’s performance vs any other language. It also included classic VB. I’m not sure if it still applicable or not.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 9, 2006 @ 5:47 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: