How to make an editable UNION query?

Question

During the complex structure of the database, I need to provide the user with the ability to edit the data stored in a series of tables. Although all data types are the same, they do not line up 1: 1 in their names. To facilitate this, I created a query that maps the original names (which come from external reports) to internal names; of these requests, everything is fed into one giant UNION request.

All data types and field sizes align correctly.

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

This is the current SQL for the query:

SELECT * FROM MappingQuery1 UNION SELECT * FROM MappingQuery2; 

EDIT:

Below is a link to an article in KB that states with certainty that the data in a UNION request cannot be updated. Is there any way around this? For example:

 SELECT * FROM MappingQuery1, MappingQuery2; 

Will this work? Remember that all fields are aligned by type, size and name.

Answer 1

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

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

When Access concatenates rows from different tables in a join query, individual rows lose their base table identifier. Access cannot know which table you want to update when trying to change a row in a join request, so it disallows all updates.

Change the following question:

Perhaps you can get around this using VBA and ADO to update the corresponding table. The way I would approach this would be to make sure that the union table contains a column with an identifier from the source table and another column that indicates the source table.

eg. in your union you will have something like this:

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

Then, through the data entry form and VBA, you can look at the values ​​of the current selected row and update the corresponding table.

EDIT 2: for onedaywhen

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 2

My preference is to combine these separate tables into a main table. With all the data in one table, this can be much simpler.

However, if you have separate tables to be partitioned, modify your mapping queries to include a field expression for the name of the source table. And include this table name field in the UNION query.

Then create a continuous form based on a read-only UNION request. Add a subform based on another query that returns one editable record from the corresponding table. In the main On Current event form, rewrite RowSource to request the subform:

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

Answer 3

This is a very old thread, but I was looking for the same solution and came across it. I had a flag value that was pressed by several Union requests, and when I tried to update it, I certainly couldn’t.

However, I found a solution and thought I would share it. In the OnEnter event of this checkbox, I simply ran the SQL Update query, which updated the field in the base table that I wanted to change. If it was True, I upgraded to False, and if False, I updated true. Voila!