How to use Access 2003 with Excel 2010?

Question

I am supporting a legacy application that was written in Access 2003 using VBA. We are updating our systems to Office 2010, with the exception of Access, which will remain the 2003 version. (This is due to several factors pertaining to other groups in my organization.)

We use Access as a front end for running reports out of MS SQL and Sybase databases. Some of these reports open in Excel. During testing, running reports that write to an Excel workbook causes the Access application to crash and exit without a warning message - the application simply disappears from the screen.

I'm not sure what the cause of this is or where I should start looking for answers. Has anybody dealt with a similar situation?

Answer 1

I saved and recompiled the application on the development machine that had Office 2010 installed, and thereafter the application worked correctly with Office 2010 (of course, it no longer works on machines only having Office 2003.) This was, of course, after checking the references were correct. Without any intervention, the reference to Office was for 2010.

After testing with early versus late binding, it would appear the best answer to this issue is to use late binding.

Answer 2

Make sure you are writing to Excel in Excel compatibility mode (.xls).