1

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

Wai Tang
  • 11
  • 1
  • 3
  • 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 Mar 16 '21 at 18:00
  • Does `ThisWorkbook.FullName` represent a local/UNC path (ie. not https) ? – Tim Williams Mar 16 '21 at 19:54
  • @TimWilliams Yes, ThisWorkbook.FullName is referring to a tab in the same workbook – Wai Tang Mar 29 '21 at 09:12
  • @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 Mar 29 '21 at 09:13
  • 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 Mar 29 '21 at 09:45

1 Answers1

1

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

Zam
  • 2,880
  • 1
  • 18
  • 33
  • Thank you for your response! I've tested this, but this time this error comes up: Could not find installable ISAM I've downloaded and installed the Microsoft Access Database Engine 2016 Redistributable, but doesn't seem to have helped. – Wai Tang Mar 29 '21 at 09:21
  • somehow i need to see you connection string. this error is similar to https://stackoverflow.com/questions/512143/error-could-not-find-installable-isam – Zam Mar 29 '21 at 14:16
  • I installed both 2010 and 2016 and still getting the error: `{"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."}` – Vipin Verma Nov 01 '21 at 00:18