title: Have I broken the Access Database Engine again?
body: <p>While testing the below SQL code for <a href="https://stackoverflow.com/questions/1594064/merging-records-with-consecutive-dates/1595348#1595348">another stackoverflow answer</a>, I got the following errors:</p>
  
  <ul>
  <li>mdb via OLE DB: "Catastrophic
  failure"</li>
  <li>accdb via OLE DB: (blank message)</li>
  <li>Access2007 Query object: "Unknown
  Access Database Error"</li>
  </ul>
  
  <p>I'm using a new clean database file. The <a href="https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5064388.html" rel="nofollow noreferrer">showplan.out</a> execution plan doesn't contain anything useful; I've posted the contents below.</p>
  
  <p>Other than declaring the Access Database Engine unfit for purpose (!!), is there anything I can do?</p>
  
  <pre><code>Sub Discon()
  
    Const USE_MDB As Long = 1
    Const USE_ACCDB As Long = 2
  
    Dim version As Long
  
    ' Hard code which version of the
    ' Access Database Engine to use
  
    version = USE_MDB
  
    Dim fullFileName As String
    Dim conString As String
  
    If version = USE_MDB Then
  
      fullFileName = Environ$("temp") &amp; "\DropMe.mdb"
      conString = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
          "Data Source=" &amp; fullFileName
  
    Else
  
      fullFileName = Environ$("temp") &amp; "\DropMe.accdb"
      conString = _
          "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
          "Data Source=" &amp; fullFileName
  
    End If
  
    On Error Resume Next
    Kill fullFileName
    On Error GoTo 0
  
    Dim cat
    Set cat = CreateObject("ADOX.Catalog")
    With cat
  
      ' Create a new database file in user's temp folder
      .Create conString
  
      With .ActiveConnection
  
        Dim Sql As String
  
        ' Create a new base base with data (required to
        ' be able to later create a virtual table)
  
        Sql = _
        "CREATE TABLE Customers (" &amp; _
        "CustomerID CHAR(5) NOT NULL UNIQUE);"
        .Execute Sql
  
        Sql = _
        "INSERT INTO Customers (CustomerID)" &amp; _
        " VALUES ('ANTON');"
        .Execute Sql
  
        ' Create a virtual (viewed) table
        Sql = _
        "CREATE VIEW TableA AS  " &amp; _
        "SELECT DT1.ID, DT1.[Date], DT1.Supplier_ID " &amp; _
        "FROM ( " &amp; _
        "      SELECT DISTINCT 1 AS ID, '2009-10-23 00:00:00' AS [Date], " &amp; _
        "             1 AS Supplier_ID FROM Customers " &amp; _
        "      UNION ALL  " &amp; _
        "      SELECT DISTINCT 2, '2009-10-23 00:00:00', 1 FROM Customers" &amp; _
        "      UNION ALL  " &amp; _
        "      SELECT DISTINCT 3, '2009-10-24 00:00:00', 2 FROM Customers " &amp; _
        "      UNION ALL  " &amp; _
        "      SELECT DISTINCT 4, '2009-10-25 00:00:00', 2 FROM Customers " &amp; _
        "      UNION ALL  " &amp; _
        "      SELECT DISTINCT 5, '2009-10-26 00:00:00', 1  FROM Customers " &amp; _
        "     ) AS DT1;"
        .Execute Sql
  
        ' Create VIEWs based on the virtual table
  
        Sql = _
        "CREATE VIEW TableA_StartDates (Supplier_ID, start_date) " &amp; _
        "AS " &amp; _
        "SELECT T1.Supplier_ID, T1.[Date] " &amp; _
        "  FROM TableA AS T1 " &amp; _
        " WHERE NOT EXISTS ( " &amp; _
        "                   SELECT * " &amp; _
        "                     FROM TableA AS T2 " &amp; _
        "                    WHERE T2.Supplier_ID = T1.Supplier_ID " &amp; _
        "                          AND DATEADD('D', -1, T1.[Date]) = T2.[Date] " &amp; _
        "                  );"
        .Execute Sql
  
        Sql = _
        "CREATE VIEW TableA_EndDates (Supplier_ID, end_date) " &amp; _
        "AS " &amp; _
        "SELECT T3.Supplier_ID, T3.[Date] " &amp; _
        "  FROM TableA AS T3 " &amp; _
        " WHERE NOT EXISTS ( " &amp; _
        "                   SELECT * " &amp; _
        "                     FROM TableA AS T4 " &amp; _
        "                    WHERE T4.Supplier_ID = T3.Supplier_ID " &amp; _
        "                          AND DATEADD('D', 1, T3.[Date]) = T4.[Date] " &amp; _
        "                  );"
        .Execute Sql
  
        Sql = _
        "CREATE VIEW TableA_Periods (Supplier_ID, start_date, end_date) " &amp; _
        "AS " &amp; _
        "SELECT DISTINCT T5.Supplier_ID, " &amp; _
        "       ( " &amp; _
        "        SELECT MAX(S1.start_date) " &amp; _
        "          FROM TableA_StartDates AS S1 " &amp; _
        "         WHERE S1.Supplier_ID = T5.Supplier_ID " &amp; _
        "               AND S1.start_date &lt;= T5.[Date] " &amp; _
        "       ), " &amp; _
        "       ( " &amp; _
        "        SELECT MIN(E1.end_date) " &amp; _
        "          FROM TableA_EndDates AS E1 " &amp; _
        "         WHERE E1.Supplier_ID = T5.Supplier_ID " &amp; _
        "               AND T5.[Date] &lt;= E1.end_date " &amp; _
        "       )         " &amp; _
        "  FROM TableA AS T5;"
        .Execute Sql
  
        ' Attempt to use the nested VIEWs in a query
  
        Sql = _
        "SELECT * FROM TableA_Periods AS P1;"
  
        Dim rs
  
        On Error Resume Next
        Set rs = .Execute(Sql)
  
        If Err.Number = 0 Then
          MsgBox rs.GetString
        Else
          MsgBox _
              Err.Number &amp; ": " &amp; _
              Err.Description &amp; _
              " (" &amp; Err.Source &amp; ")"
        End If
  
        On Error GoTo 0
  
      End With
      Set .ActiveConnection = Nothing
    End With
  End Sub
  </code></pre>
  
  <p>This is the contents of showplan.out:</p>
  
  <pre><code>--- temp query ---
  
  - Inputs to Query -
  - End inputs to Query -
  
  01) Insert into 'Customers'
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  - End inputs to Query -
  
  01) Insert into 'Customers'
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
  05) Restrict rows of result of 04)
        by scanning
        testing expression "Not "
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
  05) Restrict rows of result of 04)
        by scanning
        testing expression "Not "
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
  05) Restrict rows of result of 04)
        by scanning
        testing expression "Not "
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
  05) Restrict rows of result of 04)
        by scanning
        testing expression "Not "
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
        store result in temporary table
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
        store result in temporary table
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
        store result in temporary table
  
  
  
  --- temp query ---
  
  - Inputs to Query -
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  Table 'Customers'
  - End inputs to Query -
  
        store result in temporary table
        store result in temporary table
  01) Union result of '00)' and result of '00)'
        store result in temporary table
  02) Union result of '01)' and result of '01)'
        store result in temporary table
  03) Union result of '02)' and result of '02)'
        store result in temporary table
  04) Union result of '03)' and result of '03)'
        store result in temporary table
  </code></pre>