TransferSpreadsheet Error 3251 Operation Not Supported

Question

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.

Answer 1

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).

https://support.microsoft.com/en-us/topic/kb5002984-configuring-jet-red-database-engine-and-access-connectivity-engine-to-block-access-to-remote-databases-56406821-30f3-475c-a492-208b9bd30544

Answer 2

Update: It's a bug introduced in the May 2021 security updates to the MSI versions of Office. Microsoft knows about it and is working on a bugfix. Quoting MVP Tom van Stiphout's answer in the MS Answers thread:

I heard back from MSFT. This is an issue with A2013 and A2016 MSI, and will be likely be fixed in the July update.

---

We have the same problem. I did some more debugging and managed to create a minimal reproducible example. Apparently, the problem only occurs

• in Access 2013 with KB4493206 installed (probably in Access 2016 MSI with the corresponding KB installed as well, but I can't verify that, since I only have the C2R version of Access 2016),
• when a crosstab query is exported with DoCmd.TransferSpreadsheet and
• the query references an Access user interface control.

Here are the full repro instructions:

1. Create a new Access database.

2. Create a new table Table1 with fields ID (Long Integer) and MyText (Text) and the following records:

   ID: 1, MyText: A
   ID: 2, MyText: B
   

3. Create a new empty form Form1 with a text box Text0 and save it.

4. Open the form and enter 1 in the text box. Press "Tab" to "save" the value and leave the form open.

5. Create a new crosstab query CrosstabQuery1 with the following SQL:

    PARAMETERS [Forms]![Form1]![Text0] Long;
    TRANSFORM First(Table1.MyText) AS FirstValueOfMyText
    SELECT Table1.ID
    FROM Table1
    WHERE ((([Forms]![Form1]![Text0])=1))
    GROUP BY Table1.ID
    PIVOT Table1.ID;
   

   (Yes, that transformation is pointless and referencing form controls in queries is bad practice, but this is just a minimal repro example.)

6. Execute the crosstab query to confirm that it works. Save and close it. (Don't open it in design view, or you'll have to fix a few design-time-only things that don't affect the SQL before being able to save it.)

7. In the immediate window of the VBA code editor execute:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CrosstabQuery1", "C:\temp\repro.xlsx"
   

Expected result: The result of the crosstab query is exported to Excel.

Actual result (on Access 2013 with KB4493206 installed): Error "3251 - Operation Not Supported for This Object Type"

I have also reported this bug on the Microsoft forums.

Answer 3

I solved this issue by disabling caching for the database.