Solved

Excel export error in MDE but not in MDB

Posted on 2014-10-29
12
268 Views
Last Modified: 2014-10-30
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
Comment
Question by:Bojerne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 57
ID: 40410586
<<running on a pc with Acc2010.>>

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

Jim.
0
 
LVL 57
ID: 40410600
<<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
 
LVL 1

Author Comment

by:Bojerne
ID: 40410607
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40410621
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
 
LVL 57
ID: 40410626
BTW, make sure you accept your comment as the answer.

Jim.
0
 
LVL 1

Author Comment

by:Bojerne
ID: 40410638
Thanks - I'll accept your answer as this contains the explanation why it Works :)
0
 
LVL 57
ID: 40410646
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
 
LVL 1

Author Comment

by:Bojerne
ID: 40410668
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
 
LVL 57
ID: 40410868
<<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
 
LVL 1

Author Comment

by:Bojerne
ID: 40410874
Yes it's pretty weird
0
 
LVL 1

Author Comment

by:Bojerne
ID: 40412914
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
 
LVL 57
ID: 40412986
So when run as a MDB, it's definitely re-compiling it in the process of executing it.

Jim.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question