Hello,
I created an ERP based custom report using Access '03 & Excel '03. I
developed an Access query that pulls the required data from the ERP
(Progress) database via link tables. I use Excel to call this query and
pull the resulting data into a spreadsheet complete with running balance
formulas and conditional formatting. There is neither SQL, nor VB code
involved.
The problem that I am running into is that Excel 2007 does not appear to be
able to handle the link with Access. If I attempt to (re)create the
datasource link in the spreadheet using Excel '07 (in compatibilty mode),
then Excel "Runs the Query" for 30-90 seconds before crashing without
explanation and shutting down. Fortunately, there is still a PC available
with Office 2003 installed, so I continue to work on everything there.
Now, on top of this, Access '07 is suddenly no longer able to run the query.
At first, I was getting "Cursor not opened." errors in both Access '03 & 07.
After a little research, I was able to correct this by changing the ODBC
driver configuration to "READ UNCOMMITTED". This worked for both versions.
A week later, it started again and I experimented by changing the ODBC Fetch
Array Size from 50 to 100. This worked for '03 and continues to do so, but
'07 is having none of it. I continue to to get either "Cursor not opened.",
or "ODBC Call" errors. In addition, Excel '07 has taken to crashing without
explanation and shutting down when I attempt to refresh the data.
As of yesterday, Office '03 refreshes quickly & cleanly, whereas Office '07
crashes without explanation and shuts down. It's clear that between the
upgrade to Office 2007 and the XP SP3 update, something has changed behind
the scenes. I'm out of my depth and I'd appreciate any advice from whatever
patient souls are willing to offer it.
Many thanks,
- Rob
