Solved

Excel export error in MDE but not in MDB

Posted on 2014-10-29
12
271 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 58
ID: 40410586
<<running on a pc with Acc2010.>>

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

Jim.
0
 
LVL 58
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 58

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

Jim.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

624 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