在.NET中,我只是Application Name = MyApp
在连接字符串内部使用,但是当通过VBA使用ADO连接时,无论我在VBA代码上设置了什么名称,SQL Server Management Studio的活动监视器都始终显示Microsoft Office 2010
在Processes
该Application
列中。
conn.ConnectionString = "UID=" & UID & ";PWD=" & PWD & ";DSN=" & DSN & _
";Application Name = MyApp"
如何设置应用程序名称以进行监视?
啊,我看到VBA连接字符串不支持该Application Name
属性。在VBA中使用时,根本无法识别它。我想解决此问题的唯一方法是ADODB.Connection
从COM C#库返回一个对象。
您自己的COM库将返回带有预定义连接字符串的ADODB.Connection对象,该对象似乎在.NET中有效。您将使用VBA ADODB.Connection对象连接到数据库,但使用替换的对象引用。代替
Set cn = new ADODB.Connection
您将使用GetConection()
自己的库公开的方法。
Dim cn as ADODB.Connection
Set cn = yourCOMlibrary.GetConnection
下载并安装适用于Windows的Visual Studio Express(免费)
以管理员身份打开它并创建一个新项目。选择Visual C#
然后Class Library
将其重命名为MyConnection
在解决方案资源管理器中,重命名Class1.cs
为ServerConnection.cs
MyConnection
在解决方案资源管理器中右键单击您的项目,然后选择Add Reference
键入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.
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! :)
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 :)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句