使用带有两个表的 SQL 视图编辑 MS-Access 表单上数据的任何方式 [英] Any way to edit data on MS-Access form using SQL View with two tables

问题描述

据我所知，如果被修改的数据仅代表一个表并且数据中包含唯一标识符字段，则应该可以编辑视图描述的数据.

From what I've read it should be possible to edit data described by a view if the data being modified represents only one table and a unique identifier field is included in the data.

根据微软

任何修改都必须只引用一个基表中的列

Any modifications must reference columns from only one base table

当基础记录集基于合并来自两个表的数据的视图或存储过程时，有没有人创建一个可编辑的 MS-Access 表单?

Has anyone had any luck creating an MS-Access form that is editable when the underlying recordset is based on a view or stored procedure merging data from two tables?

我的表单仅使用辅助表加入代码表以获得基于文本的描述 - 但这样做会使表单无法编辑.

My form only used the secondary table to join a code table to get a text based description - but doing so makes the form uneditable.

我是否需要将其取出并为表单创建额外的查找代码?或者有人可以为我指出一个对他们有用的例子吗?

Do I need to pull that out and create additional lookup code for the form? Or is there someone that can point me to an example that works for them?

这就是我现在试图用来编辑PreliminaryInvoices表的表单的视图

This is what my view looks like now for the form I'm trying to use to edit PreliminaryInvoices table

SELECT        t1.*, t2.JobTypeGroup AS Grouping
FROM          dbo.PreliminaryInvoices AS t1 
INNER JOIN    dbo.tblJobTypes AS t2 
ON t1.JobTypeID = t2.JobTypeID

编辑 - 更新以显示当前的工作解决方案 - 没有第二个表

我能够让它工作的唯一方法是拆分第二个表并添加查找以显示来自 JobTypeID 的文本

The only way I've been able to get it to work is to split out the second table and add a lookup to display the text from the JobTypeID

当前可编辑视图:

SELECT        t1.*
FROM          dbo.PreliminaryInvoices AS t1 

查找过程:

CREATE PROCEDURE [dbo].[spJobTypeGrouping]
    -- Add the parameters for the stored procedure here
    @TypeID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  dbo.tblJobTypes.[Grouping] 
    FROM    dbo.tblJobTypes 
    WHERE   dbo.tblJobTypes.JobTypeiD = @TypeID
END

推荐答案

您应该在视图上创建而不是触发器，以便您可以更新基础表.您不必更新触发器中的所有字段.如果您创建了适当的触发器，Access 会将其视为一个表.

You should create instead of triggers on the view so you can update the the underlying table. You don't have to update all of the fields in the trigger. If you create the appropriate triggers, Access will treat it like a table.

至于触发器未涵盖的字段.如果用户尝试更新表，UI 将显示修改字段，但如果您重新打开视图，原始值将返回.

As for fields that aren't covered by your trigger. If a user tries to update the table, the UI will appear to modify fields, but if you reopen the view the original values will return.

这是一个处理插入、更新或删除的触发器.请注意，在发生更新时，会填充插入和删除结果集.这不是处理事情的最有效方法，但您正在更新 Access 中的链接视图，因此...

Here's a trigger that will handle an insert, update, or delete. Note that in the event of an update, insert and delete results sets are populated. This isn't the most efficient way to handle things, but you're updating linked views in Access so...

CREATE TRIGGER vwInvoices_Modify ON vwInvoices
  INSTEAD OF INSERT, UPDATE, DELETE
AS 

  SET NOCOUNT ON;

  -- delete non-updates
  DELETE FROM PreliminaryInvoices WHERE Id IN (
    SELECT Id FROM deleted EXCEPT SELECT Id FROM inserted
  );

  -- insert non-updates
  INSERT INTO PreliminaryInvoices (Id, colA, colB)
    SELECT Id, colA, colB FROM inserted WHERE Id NOT IN (SELECT Id FROM deleted)

  -- updates
  UPDATE PreliminaryInvoices SET
    colA = ins.colA
    colB = ins.colB
  FROM inserted ins
  WHERE ins.Id IN (SELECT Id FROM deleted)
    AND PreliminaryInvoices.Id = ins.Id

END

这篇关于使用带有两个表的 SQL 视图编辑 MS-Access 表单上数据的任何方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！