VSTO & .NET & Excel

May 28, 2007

.NET Connection Tool

Filed under: .NET & Excel, COM Add-ins, SQL Server — Dennis M Wallentin @ 12:10 am

Introduction 
.NET Connection is a new free .NET based tool for MS Excel to create and store connection strings in to a various number of databases and to other sources. This is my first public free .NET based tool for MS Excel and it will be followed by additional tools based on the .NET technology in the future.

It can create connection strings with both the Data Link Wizard as well as with the .NET Data Link Wizard. The .NET Data Link Wizard provides us with access to .NET Framework Data Providers to create connection strings with. These Data Providers are not available through the Data Link Wizard.

To store and handle the connection strings it uses a Microsoft Database (aka Access database). It may appear as an ‘overkill’ but in the future it will also handle additional  data of different types.

Per se it is a COM Add-in which has been developed and deployed with Add-in Express 2007 for Microsoft .NET. It is also an update of my free ADO Connection Add-In for Excel.

Requirements & Installation 

Requirements:

  • Excel 2000, 2002, 2003 or 2007
  • .NET Framework 2.0 and higher
  • Windows 2000 SP-4, Windows XP SP-2 or Windows Vista
  • (The option “Trust access to the VBA Project object model” must be checked – I still prefer that this is done by the owners of the computers then changing it through Windows Registry – code of ethics )

To install .NET Connection Tool You download it from here, save it to the hard drive and then execute the MSI-package.

The package has intentionally been kept simple and clean. It does not check if the above requirements are met or not.

Using .NET Connection Tool
After the installation of the tool a new item has been added to the menu in the VB Editor as the following screenshot shows:

menu.png


When selecting the command .NET Wizard it starts with the following dialogform:

wizard1.png

In the next step the following dialogform is showed:

wizard2.png

The following dialogforms are similar to both Wizards which make it possible to store the created connection string with a unique name:

wizard3.png

wizard4.png

Please also see the startpage at MSDN for .NET Data Providers

Let me me know what You think about it and also how it can be improved.

Special note:
The .NET Connection Tool is provided “AS IS” and with no liabilities from Dennis Wallentin or XL-Dennis.

Kind regards,
Dennis 

Advertisements

6 Comments »

  1. Dennis,

    This is really, really nice. Ok, I will admit, I am biased: I am so weak at ADO that I really need a connection string helper like this. Badly!

    For those who really know their way around, this might not be as big a deal, but this will definately help me out.

    Well done, and thank you for the tool. 🙂

    Comment by Mike Rosenblum — May 29, 2007 @ 4:08 pm

  2. Mike – Thanks for Your kind feedback.
    By developing tools like .NET Connection and then distributing them is a great way of learning.
    Windows Vista’s security model is of high interest as well as other aspects to create good practical solutions. I learned a lot when I challenged writing permissions under the Program Files folder!
    As for the tool itself I will add more functionality to it in the future.
    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 29, 2007 @ 7:32 pm

  3. Dennis,

    Thank you for the tool!

    I had a minor issue with the .NET connection Tool:
    When I wanted to store a connection string in the stored connections i got an OleDbException (The field is too small to accept the amount of data you attempted to add…)

    The length of my string was 472 characters (=OLAP connection string ;o))
    Just to let you know for possible future releases ;o)

    this was my string:
    “Provider=MSOLAP.3;Cache Authentication=False;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXXXXXXX;Data Source=XXXXXXXXX;Impersonation Level=Impersonate;Mode=ReadWrite;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096”

    Comment by Nico — June 27, 2007 @ 3:26 pm

  4. Hi Nico,

    Thanks for coming back with feedback – Highly appreciated.

    When the next version is available – next week – there will not be the 255 limitation for connection strings.

    When I release the updated version the present package will be removed.

    Would it be possible for You to test the next version?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 27, 2007 @ 3:53 pm

  5. Hi Dennis,

    I surely can test your tool.
    Feel free to contact me on my e-mail address when you like to send me your next version for testing.

    (off topic)
    Just to let you know I’m also looking to the Add-in Express™ 2007 tool. Thanks to your article about it ;o). I just have to convince my boss to buy it ;o(. The standard package will do but the professional package looks temping too. What version did you use?

    Greetings,
    Nico

    Comment by Nico — June 27, 2007 @ 4:18 pm

  6. Nico,

    Thanks and I will let You know when it’s ready and available.

    I use the Pro package and I also find their support to be fast and reliable.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 27, 2007 @ 5:13 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: