[SOLVED] Table-less UNION query in MS Access (Jet/ACE)

Table-less UNION query in MS Access (Jet/ACE)

This works as expected:

SELECT "Mike" AS FName

This fails with the error "Query input must contain at least one table or query":

SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName

Is this just a quirk/limitation of the Jet/ACE database engine or am I missing something?

Solution

You didn't overlook anything. Access' database engine will allow a single row SELECT without a FROM data source. But if you want to UNION or UNION ALL multiple rows, you must include a FROM ... even if you're not referencing any field from that data source.

I created a table with one row and added a check constraint to guarantee it will always have one and only one row.

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

That Dual table is useful for queries such as this:

SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;

Another approach I've seen is to use a SELECT statement with TOP 1 or a WHERE clause which restricts the result set to a single row.

Note check constraints were added with Jet 4 and are only available for statements executed from ADO. CurrentProject.Connection.Execute strSql works because CurrentProject.Connection is an ADO object. If you try to execute the same statement with DAO (ie CurrentDb.Execute or from the Access query designer), you will get a syntax error because DAO can't create check constraints.