Solved

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

Posted on 2016-10-13
10
117 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

739 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