Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-10-13
10
Medium Priority
?
334 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

971 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