如何进行可编辑的UNION查询? [英] How do I make an editable UNION query?

问题描述

在复杂的数据库结构过程中，我需要为用户提供一种编辑存储在一系列表中的数据的方法.尽管所有数据类型都相同，但它们的名称并不是按1:1的顺序排列的.为了减轻这种情况，我创建了一个查询，该查询将原始名称(来自外部报告)映射到内部使用的名称.从这些查询中，所有内容都被馈入一个巨型UNION查询中.

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.

要使此UNION查询正常工作，我还需要做些什么?

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

这是查询背后的当前SQL:

This is the current SQL behind the query:

SELECT * FROM MappingQuery1 UNION SELECT * FROM MappingQuery2;

下面的答案发布了指向 KB文章的链接，该链接可以肯定地指出该数据UNION查询中的值无法更新.有什么办法可以解决这个问题?例如:

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.

推荐答案

当查询为联合查询时，您可以 无法更新查询中的数据.

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

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

当Access在联合查询中合并来自不同表的行时，单个行将失去其基础表标识.当您尝试更改联合查询中的行时，Access无法知道您要更新的表，因此它不允许所有更新.

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.

以下问题编辑:

您可能可以使用 VBA 和 ADO 来解决此问题，以更新相应的表.我要采用的方法是确保联合表包含具有源表ID的列以及命名源表的另一列.

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

然后通过数据输入表单和VBA，您可以查看当前所选行的值并更新相关表.

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

有一天

这将使用Access VBA将值插入表中

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"

这篇关于如何进行可编辑的UNION查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！