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?
Advertisement
Answer
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.