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

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 ?

Answers


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.


Need Your Help

cell.textlabel.text not working properly

ios iphone objective-c ios7

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath