• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

"Microsoft Excel has stopped working" error when getting external data from Access

I have a access database that has been built up over several years with many queries

Our normal usage is to write the query in MS Access that uses ODBC links to a SQL database and, after checking that the query output is correct in MS Access, use import data in excel using the "Get External Data" using the "From Access" button spreadsheet

About a month ago the ability to use the button stopped working and we now just get a message box the says "Microsoft Excel has stopped working" and excel is restarted

The error happens in early stages before available tables/queries are displayed


I am able to import the query data by using the "Get External Data" using the "From Other Sources"  and then "From Microsoft Query" but this is cumbersome

I suspect the problem is a timeout or buffer capacity error but am unsure where to look

Can I have advice on trying to fix this problem please?

Thanks
0
Paul Burrows
Asked:
Paul Burrows
  • 5
  • 5
1 Solution
 
COACHMAN99Commented:
Maybe log the long query time in the ODBC connection?
and modify connect retries.
0
 
Paul BurrowsAuthor Commented:
Thanks COACHMAN99

How do I do this?
0
 
COACHMAN99Commented:
IF IT IS A 32-BIT CONNECTION THEN VIA c:\WINDOWS\SYSTEM32\odbcad32.exe
- EDIT YOUR CONNECTION
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Paul BurrowsAuthor Commented:
Running a version of excel in safe mode we managed to open the dialogue to select the query to be added to the database.
This seemed to be a once only result as haven't managed to do this again

When the dialogue box did not appear and we received the error nothing was logged.

The instance when we managed to open the log file showed the following:
Time      Fri Oct 14 10:01:39
TimerResolution      0
SQLIDU      0
SQLIDURows      0
SQLSelects      17248
SQLSelectRows      515778
Transactions      17250
SQLPrepares      1
ExecDirects      8632
SQLExecutes      8613
CursorOpens      0
CursorSize      0
CursorUsed      0
PercentCursorUsed      0.000000
AvgFetchTime      0.000000
AvgCursorSize      0.000000
AvgCursorUsed      0.000000
SQLFetchTime      0
SQLFetchCount      0
CurrentStmtCount      0
MaxOpenStmt      2
SumOpenStmt      28
CurrentConnectionCount      0
MaxConnectionsOpened      4
SumConnectionsOpened      7
SumConnectiontime      48036
AvgTimeOpened      6862.000000
ServerRndTrips      8641
BuffersSent      8641
BuffersRec      13216
BytesSent      513043
BytesRec      19134380
msExecutionTime      3170
msNetWorkServerTime      3095
Comment
0
 
COACHMAN99Commented:
Did you increase the retries?
And is the excel up to date with patches and service packs
And does the query always run OK in Access?
And is the odbc connection 32 bit (or 64).?
I suspect Excel is corrupt or out of date.
0
 
Paul BurrowsAuthor Commented:
Where is the retries setting?
I don't believe we have come across it yet
0
 
Paul BurrowsAuthor Commented:
I think we are talking about different points of failure

As yet there is no specific query involved

We are at the point of bringing up the list of potential queries to extract
0
 
COACHMAN99Commented:
In the advanced tab of the odbc editor.
That is why I suspect corrupt excel libraries or exe
0
 
COACHMAN99Commented:
does the excel file work OK on a different machine?
if so then excel is probably corrupt
else incompatible drivers.
0
 
Paul BurrowsAuthor Commented:
The problem seems to have been resolved by creating a new access database and importing in all objects from the database that would not open.

We did use a different ODBC version but unsure if that had an effect or it was simply a "fresh" start that made the difference

COACHMAN99 .... thanks for your help
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now