Is Microsoft ACE OLEDB 12.0 the right connection string for Microsoft Excel 365?

Question

I have recently upgraded my version of excel from Excel 2016 to Excel 365.

I have a VBA code which makes a drop down list more dynamic by running a sql query from a dataset in the same Excel workbook. This code used to work in Excel 2016 but it no longer works in the upgrade:

cn_xl.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 12.0; HDR=Yes"";"

The error is: Cannot update. Database or object is read-only.

The wider context of this code is:

'Connect to the campaign data stored in excel Dim cn_xl As ADODB.Connection Set cn_xl = New ADODB.Connection

cn_xl.Open "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 16.0 XLSM; HDR=Yes"";"
  
Dim sql_xlcm As String
sql_xlcm = "select media_type, timeperiod, control, exposed, uplift, uplift_pct " & _
           "from [Campaign_data$A1:I" & s05_cmpgn_data.Cells(Rows.Count, 1).End(xlUp).Row & "] " & _
           "where product = 'Offer' " & _
           "and metric = '" & ThisWorkbook.Sheets("Customer Averages").DropDowns("Drop Down 6").List(ThisWorkbook.Sheets("Customer Averages").DropDowns("Drop Down 6").ListIndex) & "';"

Dim rs_xlcm As ADODB.Recordset
Set rs_xlcm = New ADODB.Recordset
 
With rs_xlcm
    .ActiveConnection = cn_xl
    .Open sql_xlcm
    ThisWorkbook.Sheets("Customer Averages").Range("E21").CopyFromRecordset rs_xlcm
    .Close
End With

What can I do to fix this?

Thanks

That provider should be installed with 365. I can't see how you could get the error you mention on that line as it isn't updating anything. — Rory
Does ThisWorkbook.FullName represent a local/UNC path (ie. not https) ? — Tim Williams
@TimWilliams Yes, ThisWorkbook.FullName is referring to a tab in the same workbook — Wai Tang
@Rory, it is part of a longer code. 'Connect to the campaign data stored in excel Dim cn_xl As ADODB.Connection Set cn_xl = New ADODB.Connection cn_xl.Open "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 16.0 XLSM; HDR=Yes"";" — Wai Tang
Use: "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 12.0 Macro; HDR=Yes"";" for an xlsm file, but you really shouldn't do this to the same workbook due to memory leaks. — Rory

Zam Zam · Accepted Answer · 2021-03-16T17:48:23

Microsoft ACE.OLEDB provider, version 12.0...16.0 is required.

Office 365, as i know, does not have this provider.

And full connection string is:

"Provider=Microsoft.ACE.OLEDB.16.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes';"

where {0} your Excel file

Most probably you have to install:

Download Microsoft Access Database Engine 2016 Redistributable from https://www.microsoft.com/en-us/download/details.aspx?id=54920

Another sample and additional information available on: https://github.com/KohrAhr/SqlOverExcel/wiki

Is Microsoft ACE OLEDB 12.0 the right connection string for Microsoft Excel 365?

1 Answers