Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel export error in MDE but not in MDB

Posted on 2014-10-29
12
Medium Priority
?
274 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

721 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