Have I broken the Access Database Engine again?

Question

While testing the below SQL code for another stackoverflow answer, I got the following errors:

• mdb via OLE DB: "Catastrophic failure"
• accdb via OLE DB: (blank message)
• Access2007 Query object: "Unknown Access Database Error"

I'm using a new clean database file. The showplan.out execution plan doesn't contain anything useful; I've posted the contents below.

Other than declaring the Access Database Engine unfit for purpose (!!), is there anything I can do?

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") & "\DropMe.mdb"
    conString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & fullFileName

  Else

    fullFileName = Environ$("temp") & "\DropMe.accdb"
    conString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & 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 (" & _
      "CustomerID CHAR(5) NOT NULL UNIQUE);"
      .Execute Sql

      Sql = _
      "INSERT INTO Customers (CustomerID)" & _
      " VALUES ('ANTON');"
      .Execute Sql

      ' Create a virtual (viewed) table
      Sql = _
      "CREATE VIEW TableA AS  " & _
      "SELECT DT1.ID, DT1.[Date], DT1.Supplier_ID " & _
      "FROM ( " & _
      "      SELECT DISTINCT 1 AS ID, '2009-10-23 00:00:00' AS [Date], " & _
      "             1 AS Supplier_ID FROM Customers " & _
      "      UNION ALL  " & _
      "      SELECT DISTINCT 2, '2009-10-23 00:00:00', 1 FROM Customers" & _
      "      UNION ALL  " & _
      "      SELECT DISTINCT 3, '2009-10-24 00:00:00', 2 FROM Customers " & _
      "      UNION ALL  " & _
      "      SELECT DISTINCT 4, '2009-10-25 00:00:00', 2 FROM Customers " & _
      "      UNION ALL  " & _
      "      SELECT DISTINCT 5, '2009-10-26 00:00:00', 1  FROM Customers " & _
      "     ) AS DT1;"
      .Execute Sql

      ' Create VIEWs based on the virtual table

      Sql = _
      "CREATE VIEW TableA_StartDates (Supplier_ID, start_date) " & _
      "AS " & _
      "SELECT T1.Supplier_ID, T1.[Date] " & _
      "  FROM TableA AS T1 " & _
      " WHERE NOT EXISTS ( " & _
      "                   SELECT * " & _
      "                     FROM TableA AS T2 " & _
      "                    WHERE T2.Supplier_ID = T1.Supplier_ID " & _
      "                          AND DATEADD('D', -1, T1.[Date]) = T2.[Date] " & _
      "                  );"
      .Execute Sql

      Sql = _
      "CREATE VIEW TableA_EndDates (Supplier_ID, end_date) " & _
      "AS " & _
      "SELECT T3.Supplier_ID, T3.[Date] " & _
      "  FROM TableA AS T3 " & _
      " WHERE NOT EXISTS ( " & _
      "                   SELECT * " & _
      "                     FROM TableA AS T4 " & _
      "                    WHERE T4.Supplier_ID = T3.Supplier_ID " & _
      "                          AND DATEADD('D', 1, T3.[Date]) = T4.[Date] " & _
      "                  );"
      .Execute Sql

      Sql = _
      "CREATE VIEW TableA_Periods (Supplier_ID, start_date, end_date) " & _
      "AS " & _
      "SELECT DISTINCT T5.Supplier_ID, " & _
      "       ( " & _
      "        SELECT MAX(S1.start_date) " & _
      "          FROM TableA_StartDates AS S1 " & _
      "         WHERE S1.Supplier_ID = T5.Supplier_ID " & _
      "               AND S1.start_date <= T5.[Date] " & _
      "       ), " & _
      "       ( " & _
      "        SELECT MIN(E1.end_date) " & _
      "          FROM TableA_EndDates AS E1 " & _
      "         WHERE E1.Supplier_ID = T5.Supplier_ID " & _
      "               AND T5.[Date] <= E1.end_date " & _
      "       )         " & _
      "  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 & ": " & _
            Err.Description & _
            " (" & Err.Source & ")"
      End If

      On Error GoTo 0

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

This is the contents of showplan.out:

--- 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

Answer 1

Other than declaring the Access Database Engine unfit for purpose (!!), is there anything I can do?

You might make the task easier for Access to cope with by creating an actual table for TableA and INSERTing your sample data instead of "CREATE VIEW TableA AS ..."

Update: AFAICT your approach challenges Access by "SELECT DISTINCT ... UNION ALL ... SELECT DISTINCT"

I think I understand why you did that. This statement works fine without a FROM clause:

SELECT 1 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID

But as soon as I UNIONed another SELECT like this, Access complained "Query input must contain at least one table or query":

SELECT 1 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID
UNION SELECT 2 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID;

So Access (Jet/ACE) forces you into adding a FROM. But since you want only one record returned, you chose SELECT DISTINCT. However, I wonder if it would be easier on Access to include a WHERE for a distinct Customer value so you could return a single row without resorting to DISTINCT.

SELECT
    DT1.ID
    , DT1.aDate
    , DT1.Supplier_ID
FROM (
    SELECT
        1 AS ID
        , '2009-10-23 00:00:00' AS aDate
        , 1 AS Supplier_ID
    FROM Customers
    WHERE CustomerID='ANTON'
    UNION ALL
    SELECT
        2 AS ID
        , '2009-10-23 00:00:00' AS aDate
        , 1 AS Supplier_ID
    FROM Customers
    WHERE CustomerID='ANTON'
    UNION ALL
    SELECT
        3 AS ID
        , '2009-10-24 00:00:00' AS aDate
        , 2 AS Supplier_ID
    FROM Customers
    WHERE CustomerID='ANTON'
    UNION ALL
    SELECT
        4 AS ID
        , '2009-10-25 00:00:00' AS aDate
        , 2 AS Supplier_ID
    FROM Customers
    WHERE CustomerID='ANTON'
    UNION ALL
    SELECT
        5 AS ID
        , '2009-10-26 00:00:00' AS aDate
        , 1 AS Supplier_ID
    FROM Customers
    WHERE CustomerID='ANTON'
) AS DT1;

I doubt that approach would eliminate all the challenges, but at least that SQL seems to work without error.

Regarding the issue of getting sample data from Stack Overflow questions into Access, the easiest, cheapest method for me has been to copy the data (field headings and all) into a text file, massage the headings and data into CSV format (trivial with Vim editor), then Import from CSV into Access.

Answer 2

Declare the Access Database Engine unfit for purpose (!!). These are vanilla queries and should work without error.