How do I make an editable UNION query?

Question

In the course of a complex database structure, I need to provide the user with a means of editing data stored in a series of tables. Although all the data types are the same, they don't line up 1:1 in their names. To alleviate this, I created a query that maps the original names (which come from outside reports) to the internally-used names; from these queries, everything is fed into one giant UNION query.

All the data types and field sizes line up properly.

What else do I need to do to make this UNION query work?

This is the current SQL behind the query:

SELECT * FROM MappingQuery1 UNION SELECT * FROM MappingQuery2;

EDIT:

An answer below posted a link to a KB article that states with certainty that the data in a UNION query can't be updated. Is there any way I can work around this? For example:

SELECT * FROM MappingQuery1, MappingQuery2;

Will this work? Remember, all the fields are aligned in type, size, and name.

Answer 1

My preference would be to consolidate those individual tables into a master table. With all the data in one table, this could be a whole lot easier.

However, assuming you have to keep the indiviual tables separate, change your mapping queries to include a field expression for the source table name. And include that table name field in the UNION query.

Then create a continuous form based on the read-only UNION query. Add a subform based on another query which returns a single editable record from the appropriate table. In the main form's On Current event, rewrite the RowSource for the subform's query:

strSQL = "SELECT fields_to_edit FROM " & Me.txtTableSource & _
    " WHERE pkfield =" & Me.txtPKeyField & ";"
Me.SubformName.Rowsource = strSQL
Me.SubformName.Requery