VBA SQL Connections Refresh error 1004 exception has been thrown by the target of an invocation

Question

I created OLEDBD connection to get data from SQL and VBA code to refresh the connection with given parameter. The same code refresh power query connection and copy and format data to other table.

In general it works but very often I get run time error:"1004 exception has been thrown by the target of an invocation " pointing out: "ActiveWorkbook.Connections("XXX").Refresh". When I close the file(or all excel files) and reopen it, it mostly works. But I dont understand where is the issue. File should be used by many other users so should not be problematic in usage. Is there better way to build this connection?

Sub Rectangle1_Click()

Dim Customer_ID As String 


Customer_ID = "0" & ActiveWorkbook.Sheets("Sheet3").Range("B1").Value  


'Pass the Parameters values to the Stored Procedure used in the Data Connection
With ActiveWorkbook.Connections("XXX").OLEDBConnection
    .CommandText = "EXEC dbo.ContractProcedure_6 '" & Customer_ID & "'"
    ActiveWorkbook.Connections("XXX").Refresh
    
End With

With ThisWorkbook.Connections("Query - Table_ServiceDB_Query_2020").OLEDBConnection
    bRfresh = .BackgroundQuery
    .BackgroundQuery = False
    .Refresh
    .BackgroundQuery = bRfresh

End With


End Sub

score 1 · Answer 1 · answered Nov 10 '20 at 09:40

The problem is here - "or all excel files" in combination with ActiveWorkbook.

Long story short - use ThisWorkbook everywhere, instead of ActiveWorkbook, it is obviously referring another active workbook, where no connections are available. And refer to this for more information - How to avoid using Select in Excel VBA

VBA SQL Connections Refresh error 1004 exception has been thrown by the target of an invocation

1 Answers1