I have code to export MS Access (2016\2013) queries results into Excel spreadsheets that has worked for years.
This is the code (not posting all variables definitions I've on top of it):
strSQL = "select[query].* FROM [query] "
Set qdf = dbs.CreateQueryDef("MKTShare", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MKTShare", "\\path\file.xlsx", _
True, ""
DoCmd.DeleteObject acQuery, "MKTShare"
The error given is 3251 - Operation Not Supported for This Object Type
I tried two machines, one running WIN10 and one WIN7. Same result. The really strange thing is that this problem arises mainly with crosstab queries. No issue with "standard" select queries. If I transform the xtabquery into table and then export that table, no issue but that this is an extremely inefficient solution as I've lots of query with very big datasets running in loop.
The query shows fine results if I run it from within Access. The problem is when it comes to the transferspreadsheet method.
It happened yesterday after some Windows updates.
I tried to workaround using Output, but it does not work with prefilled Excel templates as it screws up the whole file it is exporting to.
I just ran into this exact issue yesterday. The webpage for a Windows Update that was installed yesterday had a clue in it. It seems a new security related registry key was added to Jet/ACE to prevent connections to remote sources. Not sure why crosstab queries in a TransferSpreadsheet Export are affected (must be behind the scenes in how transfer spreadsheet works), but I can successfully run my command with this update uninstalled.
Here's the link to the update. It was distributed within an office security update (KB4493206).