如何在ADODB连接字符串中设置“应用程序名称”

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.

在.NET中，我只是在连接字符串中使用Application Name = MyApp，但是当通过VBA使用ADO连接时，SQL Server Management Studio的活动监视器始终在应用程序列的进程中显示Microsoft Office 2010，无论我在VBA代码。

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

How can I set the application name for monitoring purposes?

如何设置应用程序名称以进行监视？

2 个解决方案

#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.

啊，我看到VBA连接字符串不支持Application Name属性。在VBA中使用时，它根本无法被识别。我能想到解决这个问题的唯一方法就是从COM C＃库返回一个ADODB.Connection对象。

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

您自己的COM库将返回一个带有预定义连接字符串的ADODB.Connection对象，该字符串似乎在.NET中有效。您将使用VBA ADODB.Connection对象连接到数据库，但使用替换的对象引用。代替

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

设置cn = new ADODB.Connection，您将使用由您自己的库公开的GetConection（）方法。

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

here are the steps

Download and install Visual Studio Express for Windows (FREE)

下载并安装适用于Windows的Visual Studio Express（免费）

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

以管理员身份打开它并创建一个新项目。选择Visual C＃，然后选择Class Library并将其重命名为MyConnection

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

在Solution Explorer中，将Class1.cs重命名为ServerConnection.cs

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

在解决方案资源管理器中右键单击MyConnection项目，然后选择“添加引用”

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

在搜索框中键入activeX并勾选Microsoft ActiveX Data Objects 6.1 Library

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

将以下代码复制并粘贴到ServerConnection.cs中，完全替换文件中的内容。

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.

在代码中找到cnStr变量并更新连接字符串详细信息。

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

单击Visual Studio中的TOOL和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

将GUID替换为您自己的GUID并删除花括号，使它们的格式与您从复制的代码中看到的格式相同

Right click MyConnection in the Solution Explorer and select Properties.

在Solution Explorer中右键单击MyConnection，然后选择Properties。

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

单击左侧的Application选项卡，然后单击Assembly Info并勾选Make Assembly COM-Visible

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

单击左侧菜单中的* Build *并勾选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.

注意：如果您正在为64位Office开发，请确保将Build菜单上的Platform Target更改为x64！这对于64位Office COM库是必需的，以避免任何与ActiveX相关的错误。

---

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

在解决方案资源管理器中右键单击MyConnection，然后从菜单中选择“构建”。

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

如果一切正常，那么应该成功生成MyConnection.dll和MyConnection.tlb。现在去这条路

C:\Users\username\desktop\

or wherever you saved them

或者你保存的地方

and you should see your files.

你应该看到你的文件。

---

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

现在打开Excel并转到VBE。单击工具，然后选择参考。

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

单击“浏览”按钮并导航到MyConnection.tlb。

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

此外，添加对Microsoft ActiveX Object 6.1库的引用 - 这样您就可以使用ADODB库。

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

现在右键单击Project Explorer窗口中的任意位置并插入一个新模块

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

打开SQL Server Management Studio，右键单击服务器并选择“活动监视器”

dont close this window

不要关闭这个窗口

---

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

返回Excel并点击F5或点击功能区上的绿色播放按钮。

now switch back to SSMS ( SQL Server Management Studio )

现在切换回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

您正在使用myNetConnection.GetConnection函数从C＃COM库返回ADODB Connection对象

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.

这几乎就像说Set cn = new ADODB.Connection，但是你在C＃代码中使用了预定义的连接字符串。

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

您现在可以像使用VBA中的普通ADODB.Connection对象一样使用cn对象。

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

记住要始终.Close（）ADODB.Connection。一个优秀的程序员实践是始终关闭你打开的任何东西 - 流，连接等。

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.

您可以依赖垃圾收集器来释放引用/内存，但我也为您编写了一个Dispose（）方法，以便您可以强制GC运行。你可以这样做，立即摆脱连接，因此它不会挂在SSMS中打开。

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

记得使用myNetConnection.Dispose和cn.Close，你会没事的。

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 :)

好吧，我希望将来对任何人都有帮助:)

#2

The correct keyword to set the application name in an ADODB connection string in VBA is APP, not Application Name.

在VBA中的ADODB连接字符串中设置应用程序名称的正确关键字是APP，而不是Application Name。

Example connection string, copied from an MS Access app I'm working on:

从我正在处理的MS Access应用程序复制的示例连接字符串：

DRIVER={SQL Server};SERVER=xxxx;DATABASE=xxxx;Trusted_Connection=Yes;APP=xxxx