How do I turn a QueryTable Connection into an ADODB connection?

Question

I'm trying to update an old excel sheet that uses QueryTables to connect to a Microsoft SQL Server.

The following is working:

With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DSN=[dbname];UID=[name];PWD=[pass];APP=Microsoft Office 2003;WSID=[machine name];DATABASE=[dbname];AutoTranslate=No;QuotedId=No;AnsiNPW=No" _
    , Destination:=Range("A20"))
    .CommandText = Array("[a valid query]")

There are some more sophisticated things I want to be able to do with the information this QueryTable is getting, but I keep getting the following error:

Run-time error '-2147467259 (80004005)': [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.

with the following code:

Private SqlConn As ADODB.Connection

Private Sub InitiateSqlConn(Optional User As String, Optional Pass As String, Optional Server As String, Optional DB As String)
    If SqlConn Is Nothing Then
    Dim strConn As String

    Set SqlConn = New ADODB.Connection

    If IsNull(User) Or IsEmpty(User) Or User = "" Then
        User = "[user]"
    End If
    If IsNull(Pass) Or IsEmpty(Pass) Or Pass = "" Then
        Pass = "[pass]"
    End If
    If IsNull(Server) Or IsEmpty(Server) Or Server = "" Then
        Server = "[ServerName]"
    End If
    If IsNull(DB) Or IsEmpty(DB) Or DB = "" Then
        DB = "[DBName]"
    End If

    strConn = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & DB & ";"
    SqlConn.Open "Provider=SQLOLEDB;Data Source=[SeverName];Initial Catalog=[DBName];Trusted_connection=yes;", "[User]", "[Pass]"
    End If
End Sub

Public Sub QueryInto(QR As String, ByRef RS As ADODB.Recordset, Optional User As String, Optional Pass As String, Optional Server As String, Optional DB As String)
    InitiateSqlCon User, Pass, Server, DB

    RS.Open QR, SqlConn
End Sub

I have also tried:

 SqlConn.Open "Driver={SQL Server};Server=[SeverName];Database=[DBName];UID=[User];PWD=[Pass];"

And I get the following error:

Run-time error '-2147467259 (80004005)': [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

Errors always occur on SqlConn.Open.

How do I get the connection that is established with the QueryTable to be established as an ADODB.Connection object ?

Comintern · Accepted Answer · 2015-05-04T00:10:07.997

This one will fail because you are appending extra text after the "Trusted_connection" parameter. If you use a trusted connection, you don't need a username or password, and the syntax is different for SQLOLEDB than with {SQLServer} (it should be Integrated Security=SSPI;.

SqlConn.Open "Provider=SQLOLEDB;Data Source=[SeverName];Initial Catalog=[DBName];Trusted_connection=yes;", "[User]", "[Pass]"

You also need to build the Data Source and Initial Catalog into the string instead of Data Source=[SeverName] and Initial Catalog=[DBName].

This one will fail because you aren't using any security parameters:

strConn = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & DB & ";"

This one fails because for the same reason as the first. You need to build the actual parameters into the connection string.

SqlConn.Open "Driver={SQL Server};Server=[SeverName];Database=[DBName];UID=[User];PWD=[Pass];"

It should look something more like this:

Private Sub InitiateSqlConn(Optional User As String, Optional Pass As String)
    If SqlConn Is Nothing Then
        Dim strConn As String
        Dim Server As String
        Dim DB As String

        'These can't be optional. They are required.
        Server = "TheActualNameOfTheServerHere"
        DB = "TheActualNameOfTheDatabaseHere"

        Set SqlConn = New ADODB.Connection

        If User = vbNullString Or Pass = vbNullString Then
            'No credentials.  Try a trusted connection.
            strConn = "Provider=SQLOLEDB;Data Source=" & Server & _
                      ";Initial Catalog=" & DB & ";Integrated Security=SSPI;"
        Else
            'Credentials.
            strConn = "Provider=SQLOLEDB;Data Source=" & Server & _
                      ";Initial Catalog=" & DB & ";User Id=" & User & _
                      "; Password=" & Pass & ";"
        End If

        SqlConn.Open strConn
    End If
End Sub

Note that the Server and DB parameter cannot be optional. They are in fact required and must be valid in order to connect. You can also skip the null and empty checks for optional string parameters unless you're doing something really strange. They will default to vbNullString if nothing is passed.

I think "UserId" and "Password" need to be "UID" and "PWD" respectively. I continue to get: "Run-time error '-2147467259 (80004005)': [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection." I like the vbnullstring much better though. My Microsoft SQL Server Management studio connects via windows authentications which is why I was trying to do the trusted connection. The ancient functional line of code was made before that change ever happened. — neogeek23, May 03 '15 at 23:36
@neogeek23 - `Uid` and `Pwd` are only the parameters if you are using `{SQL Server}`. `UserId` and `Password` are the parameters if you are using `SQLOLEDB`. — Comintern, May 03 '15 at 23:38
With those parameters I get error "Invalid connection string attribute" whereas with UID and PWD I don't? Maybe I'm using the wrong thing somewhere? — neogeek23, May 04 '15 at 00:05
@neogeek23 - My bad - looks like I forgot the space. Try `User Id` instead of `UserID`. Also, are you specifying the instance in the `Server` parameter (`Server\Instance`)? — Comintern, May 04 '15 at 00:09
I don't think I'm specifying the instance. Where do I get the instance name? — neogeek23, May 04 '15 at 00:16
No it is on a server off site. In my SQL Server Manager Studio I don't see anything that jumps out at me that says 'instance'. The server name there is the same as what vba is using. I just found it in the server properties. It works now, thanks. :) — neogeek23, May 04 '15 at 00:24

How do I turn a QueryTable Connection into an ADODB connection?

1 Answers1