Excel export error in MDE but not in MDB

<<running on a pc with Acc2010.>>

 Is the Office install 64 bit?  My guess is yes.

Jim.

<<Any ideas how to solve/Work around this ?>>

  If you are running 64 bit with 2010, then your only choices are:

1. Uninstall the 64 bit version and install the 32 bit edition.  This is what I would do.

2. Install the 32 bit data engine components for Office 2007.   Within an Office edition, you can't have 32 and 64 bit mixed, but between versions you can.   Installing the 2007 components should let your MDE be able to make it's 32 bit calls.   Not 100% positive this will work though.  Access 2010 may still make a 64 bit call even though the DB was compiled for 32 bits.

You can try it out easily enough though.  Download is here:

2007 Office System Driver: Data Connectivity Components
http://www.microsoft.com/en-us/download/details.aspx?id=23734

Jim.

Hi Jim
No - its a 32 bit

But I think I've found a workaround - just omitting the file type helps :)
DoCmd.TransferSpreadsheet acExport, , "qry_ExportAll", "c:\testfile", True

BTW, make sure you accept your comment as the answer.

Jim.

Thanks - I'll accept your answer as this contains the explanation why it Works :)

I just double checked and acSpreadsheetTypeExcel14 was *not* added in 2010.  acSpreadsheetTypeExcel12 is still the latest, so something else is at work.

 The two choices are:

acSpreadsheetTypeExcel12
acSpreadsheetTypeExcel12XML

  Wonder if it's getting mixed up on the extension....

Jim.

Yes I also thought that acSpreadsheetTypeExcel12 was correct, but you sounded pretty certain :) Could be the extension, but I'm happy now as it's working, when omitting the type - so its using the default

<<Yes I also thought that acSpreadsheetTypeExcel12 was correct, but you sounded pretty certain :)>>

  Constants have always been updated with every release of Office, but apparently not for 2010, which is a new one.   Not sure why that is.   Maybe because the format wasn't changed from 2007.
 
  I'm still left wondering too what's driving the error.   With the MDE having compiled code only, some things can't be adjusted on the fly like they can when your using a MDB (which has the source and can be re-compiled if needed).  

 I'd like to understand what's underlying this, but at least you have a solution in any case!

Jim.

Yes it's pretty weird

From some other minor problems I can see that when an Acc2003 runs under Acc2010 as a mdb, it's correctly detected as running VBA7 - but not if its a mde

So when run as a MDB, it's definitely re-compiling it in the process of executing it.

Jim.