I keep getting this error: Invalid Connection string attribute
This is my code:
Option Explicit
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Sub Run_Report()
Dim Server_Name As String
Dim DatabaseName As String
Dim SQL As String
Server_Name = "svr-apps\name1"
DatabaseName = "Database1"
SQL = "select p.description from PurchaseOrder po join PurchaseOrderLine pol on po.id = pol.orderID join product p on p.id = pol.productID"
Call Connect_To_SQLServer(Server_Name, DatabaseName, SQL)
End Sub
Sub Connect_To_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)
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;"
Set conn = New ADODB.Connection
With conn
.Open 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).Value = rst.Fields(col).Name
Next col
.Range("A4").CopyFromRecordset Data:=rst
End With
Set wsReport = Nothing
Call Close_ADODB_Variables
End Sub
Private Sub Close_ADODB_Variables()
If rst.State <> 0 Then rst.Close
If conn.State <> 0 Then conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
I do not see any problems with the connection string - it's all good, even works in Python or C# applications.
Bookmarks