Excel VBA SQL error, Connection failed

Question

I get the following error when I try to run an internal sql query in my Excel spreadsheet.

Error 2147467259 - Method of 'Execute' object' _Connection' failed.

Also when I run the code with breakpoints, I can get an automation error. I am querying in Excel 2013 (15.0.5023.1000).

With conn_obj
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & report_name & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

On Error GoTo err_SQL
SQL = query

Set rs = conn_obj.Execute(SQL)  'error here

Update: I replaced the above code with the below, I am getting an error on

Format of the initialization string does not conform to the OLE DB Specification.

Dim sSQLString As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String

Let DBPath = report_name 'Path here
Let sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Extended Properties='text;HDR=YES;';"

Conn.Open sconnect
sSQLString = query ' query here
mrs.Open sSQLString, Conn

Answer 1

could you try changing the below

 "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

to

  "Extended Properties='Excel 12.0 Xml;HDR=YES';"

if not advise what report_name is equal to.

alternatively try the below:

    Dim sSQLString As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
Let DBPath = "" 'Path here
Let sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";Extended Properties='text;HDR=YES;';"
Conn.Open sconnect
sSQLString = "" ' query here
mrs.Open sSQLString, Conn