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

Question

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?

Answer 1

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.

Answer 2

If you have access to some system tables, you can emulate a dual table this way:

(SELECT COUNT(*) FROM MSysResources) AS DUAL 

Unfortunately, I'm not aware of any system tables that...

• are always available, readable (MSysObjects might not be accessible to every connection)
• contain exactly one record, such as Oracle's DUAL or DB2's SYSIBM.DUAL

So you'd write:

SELECT 'Mike' AS FName FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL UNION ALL SELECT 'John' AS FName FROM (SELECT COUNT(*) FROM MSysResources) AS DUAL 

This is what is being implemented as a syntactic element in jOOQ, for instance.