MS Access更新查询错误(无法更新备注字段，即实际的文本字段) [英] MS Access Update Query Error (Can't update Memo Field, That is actual Text Field)

问题描述

下午好，

我可以访问连接到内部拥有的一些Quality Software(SQL)的后端，并拉出2个表(软件公司进行了更新，并将数据分为两个表)，这些表被合并并转储到一个表中( tblWeakPointMaster )和生成表格查询".主键未在转储中传输，快照后添加了新列(Aunonumber)，以及一些我们将要添加的数据的其他列

I have Access connecting to the Backend of some Quality Software we have in house (SQL) and pulling 2 tables (Software company made an update and split the data into two tables), These tables are joined and dumped into a Table (tblWeakPointMaster) with a Make Table Query. Primary key didn't transfer in Dump, New column (Aunonumber) was added Post-Snapshot, as well as some additional columns for the data we will be adding

数据已经被提取一次，我使用相同的Make Table查询转储到新表( tblWeakPointUpdates )，并且我想运行更新查询以从中推送New Records和Changed记录.将此表发送到 tblWeakPointMaster ，我可以使用它们.我遇到的问题是运行更新查询时出现错误(无法加入备忘录，OLE或超链接.)

With the Data already pulled once, I Used the same Make Table Query to dump to a new Table (tblWeakPointUpdates), and i want to run an update query to push New Records and Changed records from this table to tblWeakPointMaster which i have them working with. The problem I run into is I receive Errors when running the Update Query (Unable to Join on Memo, OLE, or Hyperlink..)

问题是，其拒绝的字段不是这些字段中的任何一个.是什么导致此问题.到目前为止，所有遇到问题的字段都是50个字符以内的文本字段，这些字段是完全相同的(因为它们是由相同的Make Table查询生成的)

The catch is, The fields its rejecting are not any of these fields. What could be causing this issue. So far all fields that it is having problems with are Text Fields of 50 Characters or less, The fields are completely identical (As they are made from the same Make Table Query)

我无法找到关于此特定错误的任何信息，我不确定这是否是访问限制，是否存在被拉出数据的问题，或者我是否只是头晕目眩

I wasn't able to find anything on this particular error, I'm not sure if this is an Access limitation, and issue with the data being pulled, or if I'm just in over my head

更新
两张表的图片我突出显示了我从SQL数据库中提取的字段，这些也是我试图更新的字段.表格已在图像

UPDATE
Pic of Both Tables I have highlighted the fields that I've pulled from the SQL database, These are also the fields im attempting to update. Tables have been labelled in image

在Access中用于更新查询的SQL即时消息(由Access生成)

The SQL im using in Access for Update Query (Generated by Access)

UPDATE tblWeakPointMaster INNER JOIN tblWeakPointUpdates ON 
(tblWeakPointMaster.TaskTypeID = tblWeakPointUpdates.TaskTypeID) AND 
(tblWeakPointMaster.NCR = tblWeakPointUpdates.NCR) AND 
(tblWeakPointMaster.NCR_Date = tblWeakPointUpdates.NCR_Date) AND 
(tblWeakPointMaster.NC_type = tblWeakPointUpdates.NC_type) AND 
(tblWeakPointMaster.Customer = tblWeakPointUpdates.Customer) AND 
(tblWeakPointMaster.Material = tblWeakPointUpdates.Material) AND 
(tblWeakPointMaster.Rev = tblWeakPointUpdates.Rev) AND 
(tblWeakPointMaster.Qty_rejected = tblWeakPointUpdates.Qty_rejected) AND 
(tblWeakPointMaster.TaskType = tblWeakPointUpdates.TaskType) AND 
(tblWeakPointMaster.dbo_PT_Task_Notes = tblWeakPointUpdates.dbo_PT_Task_Notes) AND 
(tblWeakPointMaster.Origin = tblWeakPointUpdates.Origin) AND 
(tblWeakPointMaster.Origin_ref = tblWeakPointUpdates.Origin_ref) AND 
(tblWeakPointMaster.Origin_cause = tblWeakPointUpdates.Origin_cause) AND 
(tblWeakPointMaster.Origin_category = tblWeakPointUpdates.Origin_category) 

SET tblWeakPointMaster.TaskTypeID = [tblWeakPointUpdates].[TaskTypeID], 
tblWeakPointMaster.NCR = [tblWeakPointUpdates].[NCR], 
tblWeakPointMaster.NCR_Date = [tblWeakPointUpdates].[NCR_Date], 
tblWeakPointMaster.NC_type = [tblWeakPointUpdates].[NC_type], 
tblWeakPointMaster.Customer = [tblWeakPointUpdates].[Customer], 
tblWeakPointMaster.Material = [tblWeakPointUpdates].[Material], 
tblWeakPointMaster.Rev = [tblWeakPointUpdates].[Rev], 
tblWeakPointMaster.Qty_rejected = [tblWeakPointUpdates].[Qty_rejected], 
tblWeakPointMaster.TaskType = [tblWeakPointUpdates].[TaskType], 
tblWeakPointMaster.dbo_PT_Task_Notes = [tblWeakPointUpdates].[dbo_PT_Task_Notes], 
tblWeakPointMaster.Origin = [tblWeakPointUpdates].[Origin], 
tblWeakPointMaster.Origin_ref = [tblWeakPointUpdates].[Origin_ref], 
tblWeakPointMaster.Origin_cause = [tblWeakPointUpdates].[Origin_cause], 
tblWeakPointMaster.Origin_category = [tblWeakPointUpdates].[Origin_category];

我收到的错误消息

-詹姆斯

推荐答案

首先是直接问题:该查询将连接所有字段，包括

First the direct problem: The query joins all fields, including

AND (tblWeakPointMaster.dbo_PT_Task_Notes = tblWeakPointUpdates.dbo_PT_Task_Notes)

这是一个备注字段，因此查询失败.

which is a Memo field, thus the query fails.

错误消息被截断(Access通常使用长SQL表达式执行此操作)，因此未列出dbo_PT_Task_Notes.但这是原因.

The error message is truncated (Access often does this with long SQL expressions), so dbo_PT_Task_Notes isn't listed. But it is the cause.

实际问题:
即使没有备注"字段，您的查询也不会执行您想要的操作.
如果在所有字段上执行INNER JOIN，则仅将完全相同的记录连接起来，而UPDATE没有意义.

The actual problem:
Even without the Memo field, your query would not do what you want.
If you do an INNER JOIN on all fields, only records that are completely identical will be joined, and the UPDATE makes no sense.

您必须区分以下字段:

• 那些标识一条记录，并且不变的在原始数据库中.这些字段属于JOIN表达式，用于查找匹配的记录.
• 那些可以更改的在原始数据库中.这些字段属于UPDATE查询的SET部分，它们是您要更新的字段.

• Those that identify a record, and don't change in the original database. These fields belong into the JOIN expression, to find matching records.
• Those that can change in the original database. These fields belong into the SET part of the UPDATE query, they are the ones you want to update.

为什么不导出和导入主键?这将使任务变得微不足道-第一组将由主键组成，第二组将由其余键组成.

Why don't you export and import the primary key? This would make the task trivial - the first group would consist of the primary key, the second group of the rest.

这篇关于MS Access更新查询错误(无法更新备注字段，即实际的文本字段)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！