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.

Solution 1

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"

Solution 2

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

Comments

[Andrew Scagnelli]

The the records in the tables have unique primary keys (specifically, the PK in each table is wholly unique to the database and intrinsic to the data set, instead of just an AutoNumber).

[pjp]

Provided that the ids are not overlapping then you could restructure your tables to have a parent table containing the ids and child tables containing the data. It's possible that the resulting query may be updatable (but you'd have to test it first). SELECT * FROM IdTable inner join Table1 ON Table1.Id=IdTable.Id inner join Table2 ON Table2.Id=IdTable.Id

[pjp]

The above should use LEFT outer joins.. The only problem is that the values will end up in different columns... The result is updatable tho.. Maybe it's best to stick to the VBA solution

[Andrew Scagnelli]

The VBA method would work; however, I'm only a summer intern, and I'll be passing off this project in a week and a half, so I'm trying to make it as easy to maintain as possible; I guess such a solution isn't tenable with this software, and I'll have to stick to the current model of separate edit interfaces.

[David-W-Fenton]

I second the suggestion (it was what I was going to suggest, in fact). Indeed, I have a standard practice that I make all continuous/datasheet forms read-only (with a few notable exceptions, such as invoice detail items).

[onedaywhen]

"You could probably work around this using VBA and ADO to update the corresponding table" -- have you tried? I cannot get it to work. With a client-side cursor and batch optimistic locking I get an error, "Insufficient base table information for updating or refreshing." Using a server-side cursor and optimistic locking I get a silent failure. Can you post a working example please?

[onedaywhen]

Ah, thought you mean using an updatable ADO recordset. Thanks for clarifying :) Though I have to say, this is moving far away from the question i.e. your VBA doesn't remotely resemble an "editable UNION query."

[pjp]

Yes I agree this we are starting to diverge a bit.