Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Excel export error in MDE but not in MDB

I'm having trouble with a particular line in a Acc2003 database - running on a pc with Acc2010.

The line below throws a "ISAM could not be found" when running the database as a MDE - when using a MDB everything is fine. Any ideas how to solve/Work around this ?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_ExportAll", "c:\testfile", True
0
Bojerne
Asked:
Bojerne
  • 7
  • 5
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<running on a pc with Acc2010.>>

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

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
BojerneAuthor Commented:
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Didn't catch that.   You specified a constant that is for A2007 only.  That's why it works when you drop it.

 For 2010, it would be acSpreadsheetTypeExcel14

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW, make sure you accept your comment as the answer.

Jim.
0
 
BojerneAuthor Commented:
Thanks - I'll accept your answer as this contains the explanation why it Works :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
BojerneAuthor Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
BojerneAuthor Commented:
Yes it's pretty weird
0
 
BojerneAuthor Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
So when run as a MDB, it's definitely re-compiling it in the process of executing it.

Jim.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now