Solved

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

Posted on 2016-10-13
10
33 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

20 Experts available now in Live!

Get 1:1 Help Now