CODEWITHSUNDEEP
vbaconnection-stringadodb
aucuparia
aucuparia

Reputation: 2051

ADODB Connection.ConnectionString property doesn't return server name

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?

Upvotes: 0

Views: 3253

Answers (1)

aucuparia
aucuparia

Reputation: 2051

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.

Upvotes: 2

Related Questions