Ahh I see that the VBA connection string does not support the Application Name attribute. It simply is not recognized when used in VBA. The only way I can solve this issue at the moment is to return the ADODB.Connection object from the C # COM library.
Your own COM library will return an ADODB.Connection object with a predefined connection string that seems to work in .NET. You will connect to the database using the VBA ADODB.Connection object, but with a reference to the replaced object. Instead
Set cn = new ADODB.Connection you will use the GetConection() method opened 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 an Administrator and create a new project. Select Visual C# , then Class Library and rename it to MyConnection
In Solution Explorer, rename Class1.cs to ServerConnection.cs
Right-click your MyConnection project in Solution Explorer and select Add Reference
Type activeX in the search field and check Microsoft ActiveX Data Objects 6.1 Library
Copy and paste the code below into ServerConnection.cs , completely replacing everything 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(); } } }
Find the cnStr variable in the code and UPDATE the connection string information.
Note. If you are not sure about the connection string you should use, see ALL COMMUNICATION EVENTS
Press TOOL in Visual Studio and CREATE GUID
Replace your GUID with yours and remove the curly braces so that they are in the same format as the ones you see now from the copied code
Right-click MyConnection in Solution Explorer and select Properties.
Click the Application tab on the left, then Assembly Info and check Make Assembly COM-Visible
Click *Build* in the menu on the left and check Register For COM Interop
Note. If you are developing 64-bit Office, make sure you change the Platform Target in the Build menu to x64 ! This is necessary for 64-bit Office COM libraries to avoid ActiveX-related errors.
Right-click MyConnection in Solution Explorer and select Build from the menu.
If everything went right, your MyConnection.dll and MyConnection.tlb should be successfully generated. Go to this way 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 go to MyConnection.tlb .
Also add links to the Microsoft ActiveX Object 6.1 Library - this means that you can use the ADODB library.
Now right-click anywhere in the Project Explorer window and insert a new Module
copy and paste the code below
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
close this window
Return to Excel and press F5 or press the green play button on the ribbon.
now switch back to SSMS (SQL Server Management Studio)
and wait for the name of the custom connection to appear! :)
Here we go! It was easy, wasn't it? :)
<T411>
This is what happens.
You are returning an ADODB Connection object from the C # COM library using the myNetConnection.GetConnection function
Dim myNetConnection As ServerConnection Set myNetConnection = New ServerConnection Dim cn As ADODB.Connection Set cn = myNetConnection.GetConnection
This is similar to saying Set cn = new ADODB.Connection , but with the predefined connection string you made in C # code.
Now you can use the cn object as a regular ADODB.Connection object in VBA.
Remember to always .Close() ADODB.Connection. It’s good practice for programmers to always close everything that you open — threads, connections, etc.
You can rely on the garbage collector for free links / memory, but I also wrote the Dispose() method for you so you can force the GC to run. You can do this to get rid of Connection immediately so that it does not hang in SSMS as open.
Remember to use myNetConnection.Dispose with cn.Close and everything will be fine.
Note:
Here is how I would do it, if someone thinks that it is wrong or needs updates (as unstable or unsafe), please leave a comment.
Well, hopefully this will be useful to anyone in the future :)