none
Using ADO/DAO in Excel 2013 to query an .accdb database hard crashes Excel RRS feed

  • Question

  • Hello All,

    Wondering if I can get some assistance, both methods of querying my access (.accdb) databases that were working in Office 2010 are now causing Excel to hard lock and crash when opening a connection.


    Function Test_Connection_DAO()

        Dim db As DAO.Database
        Dim rst As DAO.Recordset

        Dim strDB As String

        strDB = "C:\MyDatabase.accdb"

        Set db = OpenDatabase(strDB)

        Set rst = db.OpenRecordset("UserInfo")

        MsgBox rst.Fields(0)

    End Function

    Function Test_Connection_ADO()

        Dim strMyPath As String, strDBName As String, strDB As String
        Dim strSQL As String

        Dim adoRecSet As New ADODB.Recordset
        Dim connDB As New ADODB.Connection

        strDB = "C:\MyDatabase.accdb"

        connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

        strSQL = "SELECT * FROM MyTable"

        adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

        connDB.Close

        Set adoRecSet = Nothing
        Set connDB = Nothing

    End Function

    Wednesday, November 25, 2015 8:21 PM

Answers

  • >>>Wondering if I can get some assistance, both methods of querying my access (.accdb) databases that were working in Office 2010 are now causing Excel to hard lock and crash when opening a connection.

    According to your description, I have made a sample with your sample codes to try to reproduce this issue in Excel 2013, unfortunately, I am not able to reproduce it.
    So I suggest you could make sure you have made a reference to the correct DAO library and ADO library (in VBA, in Tools->References), you could refer to below:

    then you could set one breakpoint to debug your VBA to figure out which line throws error or cause Excel to hard lock and crash.
    Could your provide more information, for example error, screen shot and Excel Version, that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:21 PM
    Monday, November 30, 2015 2:15 AM

All replies

  • Hi William,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Access
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, November 26, 2015 7:41 AM
  • >>>Wondering if I can get some assistance, both methods of querying my access (.accdb) databases that were working in Office 2010 are now causing Excel to hard lock and crash when opening a connection.

    According to your description, I have made a sample with your sample codes to try to reproduce this issue in Excel 2013, unfortunately, I am not able to reproduce it.
    So I suggest you could make sure you have made a reference to the correct DAO library and ADO library (in VBA, in Tools->References), you could refer to below:

    then you could set one breakpoint to debug your VBA to figure out which line throws error or cause Excel to hard lock and crash.
    Could your provide more information, for example error, screen shot and Excel Version, that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:21 PM
    Monday, November 30, 2015 2:15 AM