How to Perform a Table-less UNION Query in MS Access?

Tableless UNION query in MS Access (Jet/ACE)

In Microsoft Access using the Jet/ACE database engine, you may encounter errors when trying to use a UNION query without specifying a table data source. The error message "Query input must contain at least one table or query" will appear.

This limitation stems from the engine's requirement for at least one table or query in the FROM clause, even if no fields from that data source are used. To overcome this problem, you can use a virtual table that always contains a single row.

Create virtual table

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

Use virtual tables

Using virtual tables you can now create tableless UNION queries:

<code>SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;</code>

Alternative methods

Alternatively, you can use a SELECT statement with a TOP 1 or WHERE clause to limit the result set to a single row.

Please note that check constraints are only available in statements executed via ADO (CurrentProject.Connection.Execute). DAO (CurrentDb.Execute) does not support creating check constraints.

The above is the detailed content of How to Perform a Table-less UNION Query in MS Access?. For more information, please follow other related articles on the PHP Chinese website!