MS Access Update Query Error (Can't update Mem

Question

Good Afternoon All,

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

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..)

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

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

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];

The Error Message I'm Receiving

-james

Answer 1

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.

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.

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.

You must differentiate the fields:

• 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.