sqlsql-serverwindows-authenticationado

VBA connect with ADO to SQL Server (Windows authenticated)

I get a runtime error 3709 when trying to connect via VBA to a SQL Server using Windows authentication.

The problem occurs on this line:

.ActiveConnection = conn

Here is the complete code used to connect:

Dim strConn As String
Dim wsReport As Worksheet
Dim col As Integer

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Server=" & Server_Name & ";"
strConn = strConn & "Database=" & Database_Name & ";"
strConn = strConn & "Trusted_Connection=yes;"
strConn = strConn & "Integrated Security=True;"

Set conn = New ADODB.Connection

With conn
    .ConnectionString = strConn
    .CursorLocation = adUseClient
End With

Set rst = New ADODB.Recordset

With rst

    .ActiveConnection = conn
    .Open Source:=SQL_Statement
End With

Set wsReport = ThisWorkbook.Worksheets.Add

With wsReport
    For col = 0 To rst.Fields.Count - 1
        .Cells(1, col + 1) = rst.Fields(col).Name
    
    Next col
End With

Or is ADO out of date now?

Solution

  • the solution was "Provider=MSOLEDBSQL;Server=XX;Database=XXXX;Trusted_Connection=yes;"

    THis website gives all the different combinations according to the exact case, showing the complexities of connections with SQL-Server:

    https://www.connectionstrings.com/ole-db-driver-for-sql-server/