?
Solved

OLEDBConnection.open call causing VB.Net application  to crash.

Posted on 2011-05-04
22
Medium Priority
?
2,242 Views
Last Modified: 2012-05-11
Trying to connect to an Excel 2010 .xlsx workbook using oledbconnection to grab data from the execl sheet. This connection only fails in a 64 bit Windows 7 machine. While using vshost.exe for debugging I would get a vshost.exe stopped working error. Here the details from the error log.

Problem signature:
  Problem Event Name:      APPCRASH
  Application Name:      50PAM.vshost.exe
  Application Version:      8.0.50727.42
  Application Timestamp:      4333d898
  Fault Module Name:      msxml6.dll
  Fault Module Version:      6.30.7600.16723
  Fault Module Timestamp:      4d104323
  Exception Code:      c0000005
  Exception Offset:      00000000000016d1
  OS Version:      6.1.7600.2.0.0.256.48
  Locale ID:      1033
  Additional Information 1:      3cfc
  Additional Information 2:      3cfc6c6d14bdc651a9c8274b1b406d59
  Additional Information 3:      6d1e
  Additional Information 4:      6d1e5f65081d8290ba1061afc2f91c77

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt


Relevant link: http://social.msdn.microsoft.com/Forums/eu/xmlandnetfx/thread/f277f673-410e-45a7-821a-a1ad1182d890

It appears that wherever the oledbconnection.open call is made the msxml6.dll is opened. On this specific  machine it will at time cause the application to crash.
0
Comment
Question by:rjetienne
  • 13
  • 4
  • 2
  • +1
20 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 35703107
If your application is compiled as "AnyCPU", then on x64 systems the program will be looking for a 64 bit Excel driver.  Unfortunately, a 64 bit OLEDB Excel driver doesn't exist.  
You can either compile the application as "x86" rather than "AnyCPU" to work around this.  
You should also add error handling around your code sections to prevent unhandled exceptions like you are seeing.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35703315
It's the missing x64 driver that's causing it - there's no x64 Excel driver, as the other poster mentioned. I wish this crashed with a nicer error too, but it's a framework crash because it's trying to load something from a DLL that's not there, and it doesn't handle that well. It's essentially a "Can't load this file or one of its dependencies" error.

Compiling to x86 instead of AnyCPU will resolve your issue.
0
 

Author Comment

by:rjetienne
ID: 35705877
I have tried the compiling with Any CPU and x86 neither of which helped. The issue still occurs with the Any CPU  and the x86 cause the creation of the OLEDBconnection object to fail. One workaround that seems to work is transferring the data I want to grab to temp workbook and grab the data from there and there is never an issue. Also the slim version is executing the the same code and using the workbook that usually fails in the main application and slim version doesn't crash. If the slim version would also crash than this would be easier live with.  That is the crux of my problem why isn't the code failing in both instances.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Expert Comment

by:mirtheil
ID: 35707005
Can you show your exact code that's failing?  
You said the slim version works where the main application fails and it's the same code?  

Do you get any information about the actual exception that's causing the crash when you run through the debugger?  

Have you wrapped your .Open call in a try..catch block to catch the exception.  For more information on try..catch, see http://msdn.microsoft.com/en-us/library/fk6t46tz%28v=VS.80%29.aspx
0
 
LVL 19

Expert Comment

by:lamaslany
ID: 35709763
Have you tried installing the '2007 Office System Driver: Data Connectivity Components' from the Microsoft site?

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891
0
 

Author Comment

by:rjetienne
ID: 35709786
But I using Excel 2010 on 64 bit OS.
0
 
LVL 19

Expert Comment

by:lamaslany
ID: 35709815
Both Access and Excel can use OLEDB.

Out of interest what version of Office 2010 do you use?  32-bit or 64-bit?
0
 

Author Comment

by:rjetienne
ID: 35719964
64-bit 2010 on Windows 7 pro 64-bit.
0
 
LVL 19

Expert Comment

by:lamaslany
ID: 35719986
That sounds about right.  Did you try installing the '2007 Office System Driver: Data Connectivity Components'?  I know it says 'Access...' but if you read the details it does say it is used by Excel as well...
0
 

Author Comment

by:rjetienne
ID: 35720252
The application still crashed even with the try catch block added to the code.
This is a oledbconnection open call trying to make a connection to a Excel 2010 .xlsx file.
Below is the details from the application stopped working window. Which looks like the one in my original post.
 
The  offending code segment is
xlConnAndData.connXL.Open()

where connXL is an OLEDBconnection in the xlConnAndData object.

The connection string is correct and it appears the crash stems from the msxml6.dll that bombs out whenever the oledbconnection open is made. As I stated above if I copy the data to a temp workbook and attempt the connection the code never crashes. Also this same code works fine in slim application.

Problem signature:
  Problem Event Name:      APPCRASH
  Application Name:      50PAM.exe
  Application Version:      1.0.0.0
  Application Timestamp:      4dc46910
  Fault Module Name:      msxml6.dll
  Fault Module Version:      6.30.7600.16723
  Fault Module Timestamp:      4d104323
  Exception Code:      c0000005
  Exception Offset:      00000000000016d1
  OS Version:      6.1.7600.2.0.0.256.48
  Locale ID:      1033
  Additional Information 1:      3cfc
  Additional Information 2:      3cfc6c6d14bdc651a9c8274b1b406d59
  Additional Information 3:      6d1e
  Additional Information 4:      6d1e5f65081d8290ba1061afc2f91c77

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt
0
 

Author Comment

by:rjetienne
ID: 35720286
No I haven't not allow to change the machine configurations. I would be different than all of our other test machines. I just curious why the 2007 driver for a 2010 version of excel?
0
 
LVL 19

Expert Comment

by:lamaslany
ID: 35720564
Fair point.  ;)

Extract from Microsoft Access Database Engine 2010 Redistributable:

2.If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”
¿If you are connecting to Microsoft Office Excel data, add “Excel 14.0” to the Extended Properties of the OLEDB connection string.
3.If you are application developer using ODBC to connect to Microsoft Office Access data, set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
4.If you are application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”



If you're setting the connection string to “Microsoft.ACE.OLEDB.12.0” then the 2007 components would also work (at least for a 32-bit app which I think is where I remembered it from)

If you need to support target machines that do not have Office 2007/Office 2010 installed you could use the Microsoft Access Database Engine 2010 Redistributable instead.


PS: Another useful post I came across:  http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
0
 

Author Comment

by:rjetienne
ID: 35723423
I installed the Microsoft Access Database Engine 2010 Redistributable onto my dev machine. The site I downloaded the Database engine from suggest that I update the Extended Properties to Extended Properties="Excel 14.0" the only problem is this piece of code doesn't work. It is actually verified by multiple forum post and the post you mentioned in the PS of your previous reply. The Extended Properties should be "Excel 12.0" even after the 64 bit database engine is installed. Not sure if the Redistributable engine had any effect because the code has never crashed on my dev machine and since I don't have permission to alter the test machine where the error occurs it will be difficult to verified if this is truly a successful solution. If I can get the test machine updated I'll let you know if this helps.

0
 

Author Comment

by:rjetienne
ID: 35728607
I tried installing the Microsoft Access Database Engine 2010 Redistributable on the test machine and application still crashed. So it still not a solution to the issue. Thanks for the suggestion.
0
 

Author Comment

by:rjetienne
ID: 35734051
Finally got the slim version to crash but it is with another workbook which calling the same function that tries to open the oledbconnection. The interesting this is I created a subroutine that would repeatedly call these storing function I'm having the issue with. In the first pass the app works without a problem but in the second pass the is where the slim version fails when trying to open the oledbconnection to xlsx file.  The good thing is this error is much easier to reproduce on the offending machine.

Problem signature:
  Problem Event Name:      APPCRASH
  Application Name:      BEM.exe
  Application Version:      1.0.0.0
  Application Timestamp:      4dc46910
  Fault Module Name:      msxml6.dll
  Fault Module Version:      6.30.7600.16723
  Fault Module Timestamp:      4d104323
  Exception Code:      c0000005
  Exception Offset:      00000000000016d1
  OS Version:      6.1.7600.2.0.0.256.48
  Locale ID:      1033
  Additional Information 1:      3cfc
  Additional Information 2:      3cfc6c6d14bdc651a9c8274b1b406d59
  Additional Information 3:      6d1e
  Additional Information 4:      6d1e5f65081d8290ba1061afc2f91c77
0
 

Author Comment

by:rjetienne
ID: 35737673
Since I could get the slim application to crash constantly at the same point, I tried an experiment. I saved the offending file as a xls workbook and the code did not crash. Just to make sure file format wasn't important I create a copy of the original file and rename from .xlsx to .xls. I checked to see if the format was changed after the renaming and it was not all of the 2007/2010 formula and conventions were still in place. To my surprise the code also ran without crashing. Can this be chalk up to a screwy machine? Because try as I may I can't get this to reproduce on other Windows 7 machines.
0
 

Accepted Solution

by:
rjetienne earned 0 total points
ID: 35743005
I found out a solution to the issue. When opening  the xlsx workbook Read only mode should be set to false. I appears that the msxml6.dll has a issue with xlsx workbook in the read only file format. After opening up the workbooks in live mode the application hasn't crashed.  
I'd like to thank all of you who responded for your time it was greatly appreciated.
0
 

Author Comment

by:rjetienne
ID: 35745253
Please close
0
 

Author Comment

by:rjetienne
ID: 35745271
i found the solution
0
 

Author Closing Comment

by:rjetienne
ID: 35775369
msxml6.dll has a problem with opening a oledbconnection to xlsx workbooks that are opened in read only mode on 64 bit Windows 7 running Excel 2010 64 bit on some computers.
Tested with applications and crashing issue is no longer occuring.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Unable to change the program that handles the scan event from a network attached Canon/Brother printer/scanner. This means you'll always have to choose which program handles this action, e.g. ControlCenter4 (in the case of a Brother).
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

850 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