Solved

Excel export error in MDE but not in MDB

Posted on 2014-10-29
12
260 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

947 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now