none
Access 2016 crashes when trying to enumerate tables using ADOX RRS feed

  • Question

  • I cannot seem to find a solution for this and would appreciate some help. I'm using late binding to create an ADOX object. The open connection statement works fine, but Access 2016 32-bit version crashes when it tries to enumerate the tables (For Each tbl in cat.Tables). This works in all other version of Access except Access 2016 and used to be working in Access 2016 up until the last few months it seems. I have the latest versions of Access installed (build 11029) and have tried on both a standalone Access 2016 and Office 365 installation. 

    I have a reference to the Microsoft ADO 6.1 Library (msado15.dll version 6.1.7601.24023 dated 1/11/2018). The msadox.dll file is version 6.1.7601.17857 dated 6/6/2012, but I'm using late binding to reference that as shown below.

    Does anyone know why Access 2016 32-bit would be crashing when trying to enumerate the tables? I have no idea.

    Dim cat As Object
    Dim tbl As Object
       
        Set cat = CreateObject("ADOX.Catalog")
        Set cat.ActiveConnection = CurrentProject.Connection
        For Each tbl In cat.Tables  'CRASHES HERE
            With tbl
                     'refreshing table link properties here
            End With
        Next tbl

    Friday, November 30, 2018 4:54 PM

All replies

  • What about

    Sub ListTablesADOX()
      Dim Conn As New ADODB.Connection
      Dim TablesSchema As ADODB.Recordset
      
      Set Conn = CurrentProject.Connection
    
      'Get all database tables.
      Set TablesSchema = Conn.OpenSchema(adSchemaTables)
      Do While Not TablesSchema.EOF
          Debug.Print TablesSchema("TABLE_NAME")
        TablesSchema.MoveNext
      Loop
    End Sub

    or

    Sub ListTablesADOX2()
      Dim Conn As New ADODB.Connection
      Dim Catalog As New ADOX.Catalog
      Dim Table As ADOX.Table
      
      Set Conn = CurrentProject.Connection
      Set Catalog.ActiveConnection = Conn
      
      For Each Table In Catalog.Tables
          Debug.Print Table.Name
      Next
    End Sub

    Using Late Binding

    Sub ListTablesADOX2()
        Dim ADODB_Conn            As Object          'New ADODB.Connection
        Dim ADOX_Catalog          As Object         'New ADOX.Catalog
        Dim ADOX_Table            As Object         'ADOX.Table
    
        Set ADODB_Conn = CurrentProject.Connection
        Set ADOX_Catalog = CreateObject("ADOX.Catalog")
        Set ADOX_Catalog.ActiveConnection = ADODB_Conn
    
        For Each ADOX_Table In ADOX_Catalog.Tables
            Debug.Print ADOX_Table.Name
        Next
    
        Set ADOX_Table = Nothing
        Set ADOX_Catalog = Nothing
        Set ADODB_Conn = Nothing
    End Sub


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, November 30, 2018 6:17 PM
  • Thanks for your help on this Daniel. I received the same result using the late binding SubListTablesADOX2 code. I had high hopes for the Sub ListTablesADOX() code. I had not thought of using a recordset with the TableSchema command. It crashed the same way, however, when it ran the line:

    Set TablesSchema = Conn.OpenSchema(adSchemaTables)

    Extremely odd. I thought it had something to do with my msado15.dll file, but now I'm not so sure.

    Any other ideas?

    Greg

    Saturday, December 1, 2018 3:49 AM
  • Dim cat As Object

    Dim tbl As Object
       
        Set cat = CreateObject("ADOX.Catalog")
        Set cat.ActiveConnection = CurrentProject.Connection
        For Each tbl In cat.Tables  'CRASHES HERE
            With tbl
                     'refreshing table link properties here
            End With
        Next tbl

    That exact code works fine for me in my installed version of Access 2016.  I wonder if the problem is here:

    I have a reference to the Microsoft ADO 6.1 Library (msado15.dll version 6.1.7601.24023 dated 1/11/2018). The msadox.dll file is version 6.1.7601.17857 dated 6/6/2012

    On my current PC, which has never had any other version of Access on it, the msadox.dll file is 10.0.17134.1, dated 4/11/2018, and the msado15.dll file is the same.  So your copy of msadox seems to b quite old.  I wonder if the database file format is in some way incompatible with the that version.  Is there another version on your system?  On my system, these files are in C:\Program Files (x86)\Common Files\system\ado.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, December 2, 2018 9:17 PM
  • Dirk,

    It looks like the msadox version that gets installed is based on the OS. I've tested on 2 machines - one that had another version of Office installed and a new one that has only had Office 365 installed, but both machines have Windows 7 Pro SP1. The older machine had the msado15.dll and msadox.dll versions as stated previously. The new machine which exhibits the same problem has the following versions:

    Windows 7 Pro 6.1.7601 SP1
    Office 365 build 16.0.11029 32-bit
    msad015.dll Version 6.1.7601.34023 modified 1/11/2018
    msadox.dll Version 6.1.7601.17857 modified 7/1/2013

    So the msadox file is slightly newer, but not as new as yours, but I think it's because of the OS version.

    Just to be sure it wasn't an issue with the Office 365 install, I reinstalled Office 365, but there was no update to the ado files and still the same problem enumerating tables with ADOX.

    It seems as though Access 2016 is not backward compatible to the Windows 7 ado drivers. It used to be though. Possibly an Office 16 update that affected compatibility? It could have been months ago. I only just noticed because I haven't tested recently. If that's the case, I don't think there's anything I can do to fix it. I'm at the mercy of Microsoft.

    Greg


    • Edited by mfgadv Monday, December 3, 2018 4:19 AM Correct 1st sentence
    Monday, December 3, 2018 4:18 AM
  • Very odd because it runs fine for me on my Win7 Pro SP1/Office365 machine.  If it helps, here's what I currently have under the hood:

    Office365 1802 9029.2167

    msado15.dll Version 6.1.7601.17587 6/6/2012
    msadox.dll Version 6.1.7601.17857 6/6/2012

    so, for whatever the reason, mine are old and haven't been updated?


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, December 3, 2018 1:15 PM
  • Daniel,

    What happens when you update Office 365 to Version 1811 Build 11029? Can you still run the table enumeration code? Hopefully that doesn't mess you up so make sure you create a restore point first. My ADO and ADOX files don't match yours so if it still runs the code to enumerate tables after you update Office, it must be the ADOX driver version, not Access.

    Greg

    Tuesday, December 4, 2018 3:27 AM
  • Stay away from 1811 as there is a new bug that just surfaced.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, December 4, 2018 10:21 AM
  • OK, it works now. I ended up rolling back my Office version to Version 1802 Build 9029.2253 which was released in March 2018. Daniel, that's only one version later than yours.  I knew I had run the table enumeration functionality on a previous version of the software in the Feb/Mar timeframe so I went that far back. I don't really know when the functionality broke i.e. what Access build, but somewhere between 1802 and 1811. It has nothing to do with the ado drivers so that's good to know. It's purely an Access 2016 issue. Hopefully, they fix the problem in a later build.

    Thank-you Daniel and Dirk for your help.

    Greg


    • Edited by mfgadv Wednesday, December 5, 2018 2:21 AM
    Wednesday, December 5, 2018 2:10 AM