VBA SQL Statement failing after 2016 MS Office Update

[erickamiller143]

I have a sql statement that my vba queries against itself. This has been working just fine and is properly set up. Since I updates my MS Office and now have 2016 Excel I am getting a Runtime Error ODBC driver does not support the requested properties.

    strSQL = "SELECT DISTINCT Len([GridData$].[WidthFT]), [GridData$].[WidthFT], [GridData$].[WidthIN] FROM [GridData$] WHERE [GridData$].[Model] = '" & Model & "'"
    Debug.Print strSQL
    Width.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

Debugged Message from Immediate Window:
SELECT DISTINCT Len([GridData$].[WidthFT]), [GridData$].[WidthFT], [GridData$].[WidthIN] FROM [GridData$] WHERE [GridData$].[Model] = '123'

This is exactly correct so not sure how to get around whatever this update did. I would appreciate any advice.

 

[Fazza]

The SQL should be OK.

Does the connection suit Excel 2016?

https://www.connectionstrings.com/

 

[erickamiller143]

This is the connection string I have now and is what has ben working for a long time prior to update. I could not see from the link provided where it would be different for 2016 vs previous versions.

Sub OpenDB()
'This macro creates a connection that will allow Excel to run queries against itself
'OpenDB must be called before any SQL queries can be ran


    If cnn.State = adStateOpen Then cnn.Close  'If the connection is already active, close it and reopen it
    cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
    ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name   'Connect to the activeworkbook
    cnn.Open
    
End Sub

 

[ANTONIO ELINON]

re: {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
1. Try to remove the curly braces - { and }.
2. Also, keep in mind that when you are reading via Excel Driver, the workbook data must be saved first before being read by ODBC driver - because ODBC driver reads the physical file (thus need to save) and not the Excel data in memory.
Good Luck!

 

[erickamiller143]

re: {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
1. Try to remove the curly braces - { and }.
2. Also, keep in mind that when you are reading via Excel Driver, the workbook data must be saved first before being read by ODBC driver - because ODBC driver reads the physical file (thus need to save) and not the Excel data in memory.
Good Luck!

I removed the "{}" and received the error at the same spot. I saved it, closed and re-opened the file before running again. Trying to cover all my bases. I am at a loss.

 

[ANTONIO ELINON]

Your SQL appears to be reading the data only, try to replace adLockOptimistic with adLockReadOnlyIf you really want to update, then add READONLY=0 to connection string:
"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=0;DBQ="

Good Luck!