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.