sql server - How to set "Application Name" in ADODB connection string

Question

In .NET I simply use Application Name = MyApp inside the connection string, but when using ADO connection through VBA the Activity Monitor of the SQL Server Management Studio always shows Microsoft Office 2010 in Processes on the Application column no matter what name I set on the VBA code.

conn.ConnectionString = "UID=" & UID & ";PWD=" & PWD & ";DSN=" & DSN & _
    ";Application Name = MyApp"

How can I set the application name for monitoring purposes?

Answer 1

Ahh I see VBA connection string doesn't support the Application Name attribute. It simply isn't being recognized when used within VBA. The only way I can think of solving this at the moment it's to return an ADODB.Connection object from a COM C# library.

Your own COM library would return an ADODB.Connection object with a predefined connection string which seem to work in .NET. You will be connecting to the database using a VBA ADODB.Connection object but with a substituted object reference. Instead of

Set cn = new ADODB.Connection you will use a GetConection() method exposed by your own library.

Dim cn as ADODB.Connection
Set cn = yourCOMlibrary.GetConnection

here are the steps

Download and install Visual Studio Express for Windows (FREE)

Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to MyConnection

In the Solution Explorer, rename Class1.cs to ServerConnection.cs

Right click your MyConnection project in the Solution Explorer and select Add Reference

Type activeX in the search box and tick the Microsoft ActiveX Data Objects 6.1 Library

Copy and paste the below code into the ServerConnection.cs completely replacing whatever is in the file.

using System;
using System.Runtime.InteropServices;
using System.IO;
using ADODB;

namespace MyConnection
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("32A5A235-DA9F-47F0-B02C-9243315F55FD")]
    public interface INetConnection
    {
        Connection GetConnection();
        void Dispose();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("4E7C6DA2-2606-4100-97BB-AB11D85E54A3")]
    public class ServerConnection : INetConnection, IDisposable
    {
        private Connection cn;

        private string cnStr = "Provider=SQLOLEDB; Data Source=SERVER\DB; Initial Catalog=default_catalog; User ID=username; Password=password;Application Name=MyNetConnection";

        public Connection GetConnection()
        {
            cn = new Connection();
            cn.ConnectionString = cnStr;
            return cn;
        }

        public void Dispose()
        {
            cn = null;
            GC.Collect();
        }
    }
}

Locate the cnStr variable in the code and UPDATE your connection string details.

Note: if you are unsure about the connection string you should use see ALL CONNECTION STRINGS

Click on TOOLs in Visual Studio and CREATE GUID

Replace the GUIDs with your own and remove the curly braces so they are in the same format as the ones you see now from the copied code

Right click MyConnection in the Solution Explorer and select Properties.

Click the Application tab on the left side, then Assembly Info and tick Make Assembly COM-Visible

Click the *Build* from the menu on the left and tick Register For COM Interop

Note: If you are developing for 64-bit Office then make sure you change the Platform Target on the Build menu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.

---

Right click MyConnection in the Solution Explorer and select Build from the menu.

If everything went OK then your MyConnection.dll and MyConnection.tlb should be successfully generated. Go to this path now

C:Usersusernamedesktop

or wherever you saved them

and you should see your files.

---

Now open Excel and go to VBE. Click Tools and select References.

Click the Browse button and navigate to the MyConnection.tlb.

Also, add references to Microsoft ActiveX Object 6.1 Library - this is so you can use ADODB library.

Now right click anywhere in the Project Explorer window and Insert a new Module

copy and paste the below code to it

Option Explicit

Sub Main()

    Dim myNetConnection As ServerConnection
    Set myNetConnection = New ServerConnection

    Dim cn As ADODB.Connection
    Set cn = myNetConnection.GetConnection

    cn.Open

    Application.Wait (Now + TimeValue("0:00:10"))

    cn.Close
    Set cn = Nothing

    myNetConnection.Dispose

End Sub

---

Open SQL Server Management Studio, right click the server and select Activity Monitor

dont close this window

---

Go back to Excel and hit F5 or hit the green play button on the ribbon.

now switch back to SSMS ( SQL Server Management Studio )

and wait for your custom connection name to appear! :)

Here we go! That was easy, wasn't it? :)

---

This is what is happening.

You are returning an ADODB Connection object from you C# COM library by using myNetConnection.GetConnection function

Dim myNetConnection As ServerConnection
Set myNetConnection = New ServerConnection

Dim cn As ADODB.Connection
Set cn = myNetConnection.GetConnection

It's almost like saying Set cn = new ADODB.Connection but with predefined connection string which you did in your C# code.

You can use the cn object like a normal ADODB.Connection object within VBA now.

Remember to always .Close() the ADODB.Connection. A good programmers practice is to always close anything you open - streams, connections, etc.

You can rely on the Garbage Collector to free references/ memory but I also wrote a Dispose() method for you so you can force the GC to run. You can do that to immediately get rid of the Connection so it does not hang in the SSMS as opened.

Remember to use myNetConnection.Dispose along with the cn.Close and you'll be fine.

Note:

This is how I would do it if any one thinks this is wrong or needs to be updates (as being unstable or unsafe) please leave a comment.

---

Well, I hope this will be helpful to anyone in the future :)