ADODB Connection.ConnectionString property doesn't return server name

Question

I am using ActiveX Data Objects 6.1 library in Excel VBA to access a SQL Server 2012 database. I can connect and run queries fine, but once connected there appears to be no way to determine which server the connection object is connected to, as the connectionString property does not return the same string that was used to open the connection:

Public Sub connectDB()
        Dim conn As New Connection
        Dim strServer As String, strDatabase As String, strUser As String, strPassword As String
        strServer = "****": strDatabase = "****": strUser = "****": strPassword = "****"

        conn.ConnectionString = "Driver={SQL Server};Server=" & strServer & ";Database=" & strDatabase & ";UID=" & strUser & ";PWD=" & strPassword
        Debug.Print conn.ConnectionString
        conn.Open
        Debug.Print conn.ConnectionString
End Sub

Outputs:

Driver={SQL Server};Server=****;Database=****;UID=****;PWD=****

Provider=MSDASQL.1;

In other words, once the connection is open the connectionString property is reset to something unhelpful. Connection.DefaultDatabase returns the database, but there appears to be no way to determine the server from the connection object.

If I have code which is passed a connection object at runtime it would be nice to be able to see which server it's connected to without having to execute sys.dm_exec_connections (which seems like a waste of resources, and you need fairly high permissions to run it). Is there a way?

1. Why do you need to read the server name after you connected? 2. Why don't you use global variables to save the server, so you can read it everytime you need it — EngJon
– EngJon, Commented Aug 11, 2015 at 11:45
Yes, there are ways round it. But if my code gets a connection object from other code that I don't otherwise need to modify, it would be neater to just get the connection string or server from the connection. — aucuparia
– aucuparia, Commented Aug 11, 2015 at 12:30
There is a property that disables the stripping of the data from your connection string. However, if you plan to read from other code, the property has to be set to true in that code, too. — EngJon
– EngJon, Commented Aug 11, 2015 at 12:54
Can you post details of the property in an answer? I couldn't find it (though in the process of looking I did find the Server Name property so thanks for that!) — aucuparia
– aucuparia, Commented Aug 11, 2015 at 14:34

aucuparia · Accepted Answer · 2015-08-11 14:32:35Z

2

The server name (along with a lot of other interesting information including DBMS version, supported features, special characters, etc) is one of the Connection object's dynamic properties, accessed through the Properties collection:

connection.Properties("Server Name").Value

returns the server name. For the Microsoft OLE DB Provider for ODBC Drivers (and possibly others), the full connection string is also in the "Extended Properties" property. You can list the properties by doing:

Dim prop As Property
For Each prop In conn.Properties
    Debug.Print prop.Name, prop.Value
Next prop

Exactly which properties you get for any given connection depends on the provider you are using - see MSDN for lists.

answered Aug 11, 2015 at 14:32

aucuparia

2,05121 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EngJon Over a year ago

Is it always possible to read the server name through this property? The property I meant in the comment above was "Persist Security Info". It can be found inside the conn.Properties as well. Seems like it only is meant for the connectionString, so the property "Server Name" won't be affected.

Collectives™ on Stack Overflow

ADODB Connection.ConnectionString property doesn't return server name

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related