如何设置“申请名称”在ADODB连接字符串中 [英] How to set "Application Name" in ADODB connection string

问题描述

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

  conn.ConnectionString =UID =& UID& ; PWD =& PWD& ; DSN =& DSN& _ 
;应用程序名称= MyApp
  

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

解决方案

啊我看到VBA连接字符串不支持 应用程序名称 属性。在VBA中使用时，它根本就不被识别。在COM C＃库中返回一个 ADODB.Connection 对象的时候，我可以想到解决这个问题的唯一方法。

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

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

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

这里是步骤

下载并安装

单击Visual Studio中的工具和CREATE GUID

用自己的GUID替换GUID，并将删除大括号，以便它们与您的格式相同从复制的代码看现在

右键单击 MyConnection 并选择属性。

点击左侧的 应用程序 侧面，然后 程序集信息 并勾选使程序集COM-Visible

点击左侧菜单中的 * Build * ，然后勾选注册COM Interop

注意：如果您正在开发64位Office，那么请确保在 Build 上更改 Platform Target em>菜单到 x64 ！对于64位Office COM库，这是强制性的，以避免任何与ActiveX相关的错误。

---

右键单击 <$ c \\ c> MyConnection ，然后选择 Build 如果一切顺利，那么你的 MyConnection.dll 和 MyConnection。应该成功生成tlb 。现在去这个路径

  C：\Users\username\desktop\ 
  pre> 
 
 
 或您保存的位置 
 
 
 
，您应该会看到您的文件。 
 
 
 
  
 
 
 
 
 
 
现在打开Excel并转到VBE。单击工具并选择参考。
 
 
 
 kbd>浏览按钮并导航到 MyConnection.tlb 。
 
 
 
另外， code> Microsoft ActiveX对象6.1库  - 这样就可以使用ADODB库。
 
 
 
  
 
 
 
现在右键单击项目资源管理器中的任何位置窗口和插入新的 模块  
 
 
 
复制并粘贴以下代码
  Option Explicit 
 
 Sub Main（）
 
 Dim myNetConnection As ServerConnection 
设置myNetConnection = New ServerConnection 
 
 Dim cn As ADODB.Connection 
设置cn = myNetConnection.GetConnection 
 
 cn .Open 
 
 Application.Wait（Now + TimeValue（0:00:10））
 
 cn.Close 
设置cn =没有
 
我的网络连接。 
打开SQL Server Management Studio，右键单击服务器，然后选择活动监视器 
 
 
 
  
 
 
 
不关闭此窗口
 
 
 
 
 
 
返回到Excel并点击 F5 或点击功能区上的绿色播放按钮。
 
 
 
  
 
 
 
现在切换回SSMS（SQL Server Management Studio）
 
 
 
，等待您的自定义连接名称显示！ ：）
 
 
 
我们走吧！那很简单，不是吗？ ：）
 
 
 
  
 
 
 
 
 
 
这是发生了什么。
 
 
 
您正在返回一个ADODB连接对象来自你C＃COM库通过使用 myNetConnection.GetConnection 函数
  Dim myNetConnection As ServerConnection 
设置myNetConnection = New ServerConnection 
 
 Dim cn As ADODB.Connection 
设置cn = myNetConnection.GetConnection 
  
几乎就像说 Set cn = new ADODB.Connection ，但是您在C＃中执行的预定义连接字符串
 
 
 
您可以像VBA中的正常ADODB.Connection对象一样使用 cn 对象。
 
 
 
记住永远 .Close（） ADODB.Connection。一个好的程序员的做法是永远关闭你打开的任何东西 - 流，连接等。
 
 
 
你可以依靠垃圾收集器来释放引用/内存，但是我也写了一个 Dispose（）方法，以便您可以强制GC运行。您可以这样做，立即摆脱连接，所以它不会在打开的SSMS中挂起。
 
 
 
记住使用 myNetConnection.Dispose 连同 cn.Close ，你会很好。
 
 
 
注意：
 
 
 
这是我会做的，如果有人认为这是错误或需要更新（因为不稳定或不安全）请留下评论。 
 
 
 
 
 
 
嗯，我希望这将有助于将来的任何人:) 
 
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?
解决方案
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:\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.

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

                        
这篇关于如何设置“申请名称”在ADODB连接字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！