vba 如何在 ADODB 连接字符串中设置“应用程序名称”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20398477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set "Application Name" in ADODB connection string
提问by SysDragon
In .NET I simply use Application Name = MyAppinside the connection string, but when using ADO connection through VBA the Activity Monitor of the SQL Server Management Studio always shows Microsoft Office 2010in Processeson the Applicationcolumn no matter what name I set on the VBA code.
在.NET我简单地使用Application Name = MyApp利用通过VBA的SQL Server Management Studio中始终显示的活动监视器ADO连接在连接字符串中，但Microsoft Office 2010在Processes上Application栏无论什么名字我的VBA代码设置。
conn.ConnectionString = "UID=" & UID & ";PWD=" & PWD & ";DSN=" & DSN & _
";Application Name = MyApp"
How can I set the application name for monitoring purposes?
如何设置应用程序名称以进行监控？
回答by
Ahh I see VBA connection string doesn't support the Application Nameattribute. 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.Connectionobject from a COM C# library.
啊，我看到 VBA 连接字符串不支持该Application Name属性。在 VBA 中使用时，它根本无法被识别。目前我能想到解决这个问题的唯一方法是ADODB.Connection从 COM C# 库返回一个对象。
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.Connectionyou will use a GetConection()method exposed by your own library.
Set 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 Administratorand create a New Project. Select Visual C#then Class Libraryand rename it to MyConnection
以管理员身份打开它并创建一个新项目。选择Visual C#然后Class Library将其重命名为MyConnection
In the Solution Explorer, rename Class1.csto ServerConnection.cs
在解决方案资源管理器中，重命名Class1.cs为ServerConnection.cs
Right click your MyConnectionproject in the Solution Explorerand select Add Reference
MyConnection在解决方案资源管理器中右键单击您的项目并选择Add Reference
Type activeXin 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.cscompletely 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 cnStrvariable 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 中的工具并创建 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 替换并删除花括号，使它们的格式与您现在从复制的代码中看到的格式相同
Right click MyConnectionin the Solution Explorerand select Properties.
MyConnection在解决方案资源管理器中右键单击并选择属性。
Click the Applicationtab on the left side, then Assembly Infoand 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 Targeton the Buildmenu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.
注意：如果您正在为 64 位 Office 进行开发，请确保Platform Target将Build菜单上的更改为x64! 这对于 64 位 Office COM 库是必需的，以避免任何与 ActiveX 相关的错误。
Right click MyConnectionin the Solution Explorerand select Buildfrom the menu.
MyConnection在解决方案资源管理器中右键单击并Build从菜单中选择。
If everything went OK then your MyConnection.dlland MyConnection.tlbshould 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 Toolsand select References.
现在打开 Excel 并转到 VBE。单击Tools并选择References。
Click the Browsebutton and navigate to the MyConnection.tlb.
单击Browse按钮并导航到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 Library- 这样您就可以使用 ADODB 库的引用。
Now right click anywhere in the Project Explorerwindow and Inserta new Module
现在右键单击Project Explorer窗口中的任意位置并插入一个新的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
打开 SQL Server Management Studio，右键单击服务器并选择 Activity Monitor
dont close this window
不要关闭这个窗口
Go back to Excel and hit F5or hit the green playbutton 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.GetConnectionfunction
您正在使用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.Connectionbut with predefined connection string which you did in your C# code.
这几乎就像在说，Set cn = new ADODB.Connection但是使用您在 C# 代码中所做的预定义连接字符串。
You can use the cnobject like a normal ADODB.Connection object within VBA now.
您现在可以cn像使用VBA 中的普通 ADODB.Connection 对象一样使用该对象。
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 hangin the SSMS as opened.
您可以依靠垃圾收集器来释放引用/内存，但我也Dispose()为您编写了一个方法，以便您可以强制运行 GC。您可以这样做以立即摆脱连接，使其不会在打开时挂在 SSMS 中。
Remember to use myNetConnection.Disposealong with the cn.Closeand 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 :)
好吧，我希望这对将来的任何人都有帮助:)
回答by Christian Specht
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
