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

I don't know much about working with Access, but my first thought on reading the question is to create a view that returns your UNIONed data and then INSTEAD OF Triggers to handle inserts/updates/deletes correctly across the tables.

Answer 2

When the query is a Union query, you cannot update data in the query.

http://support.microsoft.com/kb/328828

When Access combines rows from different tables in a union query, the individual rows lose their underlying table identity. Access cannot know which table you mean to update when you try to change a row in a union query, so it disallows all updates.

Following question edit:

You could probably work around this using VBA and ADO to update the corresponding table. The way i'd approach this would be to ensure that your union table contains a column that has the id from the source table along with another column that names the source table.

e.g. in your union you'd have something like this:

SELECT 'Table1', id, ... FROM Table1
UNION 
SELECT 'Table2', id, ... FROM Table2

Then through a data entry form and VBA you could look at the values of the currently selected row and update the relevant table.

EDIT 2: For onedaywhen

This inserts values into a table using Access VBA

Option Compare Database
Option Explicit

Public Sub InsertDataPunk(TargetTable As String, IdVal As Long, MyVal As String)

    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection

    Dim sql As String
    'You could build something fancier here
    sql = "INSERT INTO " & TargetTable & " VALUES (" & IdVal & ",'" & MyVal & "')"

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = adCmdText
    cmd.Execute

End Sub  


InsertDataPunk "Table2", 7, "DooDar"

Answer 3

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