How do I update a long text field in from a long text field in another table without truncating

Question

How do I update a long text field in from a long text field in another table without truncating fields that exceed 256 characters?

I have two tables.

tbl_current_text with two fields: system_id, current_system_text  
tbl_new_text with two fields: system_id, new_system_text

The text field in both tables is long text. Is there a way to update current_system_text from new_system_text without truncating?

I don't know vba but I know someone who does if that's necessary.

I tried an update query and a make table query and both truncated the results. I used the gui to make these queries

Answer 1

Per my comment perhaps you haven't set up your table relationships properly. For instance:

'sql
UPDATE Table1 INNER JOIN Table2 ON Table1.Table1ID = Table2.Table1ID SET Table2.[LongText] = [Table1].[LongText];

Please note how Table2 has Table1ID as a foreign key and how Table1ID in Table1 is hooked up to Table1ID in Table2 forming a relationship. Since we need information from Table1 & Table2 the most direct way is to use the relationship to combine the tables. This has the advantage of making it explicit that the relationship needs to be in the data. by combining on Table1ID we can use Table1ID to grab the other values in the appropriate row of the combined table.

we can see the combined table by grabbing everything with a select query:

Combined Table:

Then use the combined table to update table2.

Before:

After:

it might make it clearer if we don't use the relationship directly. we are still combining Table1 and Table2 and then using the combined table to update Table2. The relationship is still there, but now we have to be more explicit about the criteria to use the relationship to get the same combined table.

'sql
UPDATE Table1, Table2 SET Table2.[LongText] = [Table1].[LongText]
WHERE (((Table1.Table1ID)=[Table2].[Table1ID]));