Results 1 to 6 of 6
  1. #1
    AME is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    8

    DAO Connection in Excel to Access 2016

    Sorry if this is in the Wrong Thread....



    I have a macro which connects to a Access 2016 DB using the Code below. This was working fine for Excel 2010, 2013 & 2016 without any issues and suddenly (I think after a MS Office Update) it simply crashes Excel 2013, but 10 & 16 work fine. Can someone please help here as I'm really struggling to understand why it stopped working and more importantly how do I fix it. I do not get an error, Excel simply goes into not working and restarts.

    Code to open DB, there is other code to perform update but I can categorically say the highlight code crashes Excel 2013

    Dim DBOpen As Object ' ADODB.Recordset
    Dim strConn As String
    Dim strTargetDB As String
    Dim strQry, strMsg, MyPath As String
    Dim lngRecs As Long
    Dim db As DAO.Database
    Dim MyTable As DAO.Recordset


    MyPath = ThisWorkbook.Path & ""
    Set db = DBEngine.OpenDatabase(MyPath & "{BDNAME}.accdb")

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The update may have installed new versions of the drivers. Because 2013 is old, it may not work with the new versions.
    You would either:
    upgrade the user from 2013 to latest
    or
    export Access data to excel via Transferspreadsheet.

  3. #3
    AME is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    8
    I thought this was the case BUT, it works fine with Excel 2010. Is there a way to force Excel 2013 to use the same References as 2010 or 2016. I really don't want to have to change the Macro so its a different for each version of Excel that's daft

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i dont know any other option

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    MyPath = ThisWorkbook.Path & "{BDNAME}.accdb"
       '  Debug.Print MyPath
        Set db = DBEngine.OpenDatabase(MyPath)
    Maybe try the above? Uncomment the debug statement, single step through the code to see what the path actually is.

    Do you REALLY have a file name of "{BDNAME}.accdb"? Didn't think Access allowed braces in file names......

  6. #6
    AME is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    8
    I have put the debug on the set DB code. This is exactly where it crashes excel. BUT only for Excel 2013. 2010 and 2016 appear to work ok.

    I'm sure its something to do with a Office Update and the references 2013 must use..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ODBC connection for Access 2016 - results in error
    By Ashish_Panchal in forum Access
    Replies: 3
    Last Post: 11-25-2016, 01:35 AM
  2. Replies: 2
    Last Post: 04-19-2016, 04:09 PM
  3. Replies: 5
    Last Post: 10-20-2015, 06:43 PM
  4. Excel connection to Access query
    By CJGarcia207 in forum Access
    Replies: 0
    Last Post: 08-16-2013, 05:33 PM
  5. ADO Connection to Access from Excel
    By jaffar2000 in forum Access
    Replies: 9
    Last Post: 04-18-2012, 06:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums