none
OLEDB Connection causes crash in Excel 2010, works fine in Excel 2016 RRS feed

  • Question

  • Happens as soon as the ReConnect method is called from VBA.

    Exact same connection string with the exception of the OLE Provider version (16 for 2016, 12 for 2010)

    Is the only solution to change these to ODBC connections ?

    All data sources are MS Access queries or tables.

    Thursday, December 22, 2016 6:54 PM

All replies

  • Hi Syswizard,

    you did not post any code here.

    so from your description all I know that when you try to Reconnect then your Excel Application get crashed.

    did you get any error when excel get crashed?

    it can help us to narrow down the issue.

    I suggest you to redownload the “Microsoft.ACE.OLEDB.12.0” and try to test again.

    if anything wrong with your old “Microsoft.ACE.OLEDB.12.0” then it will be corrected when you install new one.

    also please check that which office and windows version you are using 32 bit or 64 bit.

    Microsoft Access Database Engine 2010 Redistributable

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 23, 2016 1:20 AM
    Moderator
  • The code is simple: oConnectionOLEDB.Reconnect followed by oConnectionOLEDB.Refresh

    There is no trappable error...Excel just blows-up.

    Doesn't the new 2016 edition have a different OLE Database Engine ....Microsoft.ACE.OLEDB.16.0 ?

    I seriously doubt there is a problem with my installation.....the connections work fine if you set them up manually. The problem here is something to do with connection string modifications made with VBA.

    Friday, December 23, 2016 2:21 PM
  • Database Engine Confusion

    From the post above, you can see there is much confusion as to which OLE DB version to run.

    The question is: does Office 2016 have a 2016 edition of the engine. If not, why wouldn't it default to the Office 2013 version instead of the 2010 edition ?

    Sunday, December 25, 2016 5:39 AM
  • Hi Syswizard,

    did you try to test with the link I mentioned above?

    if you see the instructions then you will know that which version it will install and how to use it.

        • To install this download:         
            1. Download the file by clicking the Download        button and saving the file to your hard disk.
            2. Double-click the AccessDatabaseEngine.exe program        file on your hard disk to start the setup program.
            3. Follow the instructions on the screen to complete the        installation.
        To use this download:
                
            1. If you are the user of an application, consult your        application documentation for details on how to use the appropriate        driver.   
            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 the        appropriate Extended Properties of the OLEDB connection string based on        the Excel file type:
                    
                     File Type (extension)                                                           Extended        Properties
                     ---------------------------------------------------------------------------------------------
                     Excel 97-2003 Workbook (.xls)                                                "Excel 8.0"
                     Excel 2007-2010 Workbook (.xlsx)                                           "Excel 12.0 Xml"
                     Excel 2007-2010 Macro-enabled workbook (.xlsm)             "Excel 12.0 Macro"
                     Excel 2007-2010 Non-XML binary workbook (.xlsb)             "Excel 12.0"
                
        • If you are an 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”
          
      • If you are an 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”
          

    so please test it on your side and let me know about the results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 26, 2016 7:15 AM
    Moderator
  • Interestingly, that Microsoft Access 2010 Database Engine installation was dated rather recently....July 2016.

    I take it there is no 2013 or 2016 engine ?

    Thursday, December 29, 2016 2:36 PM
  • Provider=Microsoft.ACE.OLEDB.12.0;

    User ID=Admin;Data Source=S:\Portfolio Database2.accdb;

    Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";

    Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;

    Upon detailed debugging, Excel crashes right after the Refresh method is called.

    Above is the connection string. What is wrong with it ? What about the Engine Type ? Locking Mode ?

    Thursday, December 29, 2016 4:08 PM
  • After completely replacing the same Connection string that was created by MANUALLY adding a new OLEDB connection that successfully refreshed, Excel 2010 still crashed !!!

    There is something wrong that occurs when changing the Connection property via VBA....or some sort of hidden property that needs to be set.


    • Edited by Syswizard Friday, December 30, 2016 1:53 AM
    Friday, December 30, 2016 1:49 AM
  • Hi Syswizard,

    I suggest you to run the same thing with the different machine.

    if you succeed to run without crash then try to repair the Excel Application and try again.

    it is possible that something got corrupted and because of that excel get crash when connection get refreshed.

    let me know about your testing results. so that we can try to suggest you further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 5:11 AM
    Moderator
  • Something is really screwy with OLE DB query tables:

    When I go to audit the listobjects and querytables in all of the worksheets,

    sometimes I am unable to get the handle on the QueryTable object...about 10 out of 20 cases fail.

    Set oQT = oList.Querytable. (1004 error)

    However, when I debug this on the next line, it shows the Commandtext correctly !!
    debug.print oList.QueryTable.CommandText. So I can get some of the properties of the QueryTable, but I cannot instantiate it.

    Even crazier, is that the ListObject's Refresh method works fine and the ListObject's connection can be refreshed without error as well.

    How to fix this without having to delete these errant ListObjects ? If I delete the listobject and then try to add it with VBA, the existing connection will be lost.

    Tuesday, January 10, 2017 10:28 PM
  • Hi Syswizard,

    From your last post we can see that connection.Refresh is working now.

    and now you have a different issue.

    if this issue is not connected with your original issue then suggest you to create new thread for a new issue.

    if it is connected with your original issue then it seems that something got corrupted.

    as I suggest you in my previous post to test with different machine and try to repair the Excel and database engine.

    and let me know about the results.

    but from your last reply I did not find that you perform these steps.

    please try to test it and let me know about that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 6:18 AM
    Moderator
  • The issue IS related...as refreshing can be done at the ListObject level or the Connection level. They are essentially the same when OLEDB queries are involved.

    I have used 2 different machines, 2 different installations and 2 different versions of Excel.

    This is most definitely an internal code issue...as Excel 2010 should not be crashing. I'll try yet another machine with a different version of Excel 2010 tonight. Excel 2016 is erroneously throwing a 1004 error when I try to instantiate a query table object and this is likely masking the real problem. If the query table or related list object was corrupted, I would not be able to refresh the data. That is not the case....the refresh works fine.

    Wednesday, January 11, 2017 5:22 PM
  • Hi Syswizard,

    you had mentioned that, " Excel 2016 is erroneously throwing a 1004 error ".

    I would also like to suggest you that try to use different versions of Excel 2016.

    sometimes it happens that some updates cause the issues.

    so it is possible that because of some erroneous update you are receiving this error.

    you can try to uninstall update and try to test the same thing with previous available updates.

    if you find that you can reproduce the issue with all or specific version of excel 2016 then we can try to report the issue to Excel developers and they try to correct it later.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 12, 2017 6:45 AM
    Moderator
  • I have this version:
    16.0.4432.1000
    64 bit

    Today I am going to attempt to fix the broken query tables either manually or via scripting.....what a pain !!!

    Thursday, January 12, 2017 3:05 PM
  • Hi Syswizard,

    let me know about the results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 13, 2017 6:07 AM
    Moderator
  • This was a hopeless venture. The only way to prevent Excel from crashing or throwing a "cannot find isam" error was to rebuild each every connection by hand....UGHH.

    Every attempt to change the connection string via VBA resulted in a crash...despite the fact the string was exactly the same as the rebuilt connection.

    This truly was a bug.

    Wednesday, January 25, 2017 1:38 PM
  • Hi Syswizard,

    please submit your feedback to Excel User Voice regarding this Bug.

    Excel User Voice

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 1:23 AM
    Moderator