Solved

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

Posted on 2016-10-13
10
62 Views
Last Modified: 2016-10-17
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
Comment
Question by:Zarbs
  • 5
  • 5
10 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842885
Maybe log the long query time in the ODBC connection?
and modify connect retries.
0
 

Author Comment

by:Zarbs
ID: 41842893
Thanks COACHMAN99

How do I do this?
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842897
IF IT IS A 32-BIT CONNECTION THEN VIA c:\WINDOWS\SYSTEM32\odbcad32.exe
- EDIT YOUR CONNECTION
0
Back Up Your Microsoft Windows Server®

Back up 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.

 

Author Comment

by:Zarbs
ID: 41842986
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41842997
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
 

Author Comment

by:Zarbs
ID: 41842999
Where is the retries setting?
I don't believe we have come across it yet
0
 

Author Comment

by:Zarbs
ID: 41843001
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41843028
In the advanced tab of the odbc editor.
That is why I suspect corrupt excel libraries or exe
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
ID: 41843856
does the excel file work OK on a different machine?
if so then excel is probably corrupt
else incompatible drivers.
0
 

Author Closing Comment

by:Zarbs
ID: 41847250
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
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

777 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