Access2013 Copy LongText from one LongText field to another

Question

Have an Access2013 database with 2 basic tables.  I need to copy the 577 characters from three longtext fields located in Table1 to a single longtext field in Table2.  I need to do this because Table2 is a reusable table and gets cleared each month whereas Table1 is the main source of data.  The data is initially entered into 4 plain-text fields on a form and stored as plain text in the three longtext fields of Table1.  I've tried an Append query and no matter how I put the three fields together (using '&', '+', concatenate()) the data ends truncated at 255 chrs.  The only way I've seen to get more than 255 chrs into a longtext field is to open the table directly and type them in manually.

I really need a vba code solution that takes the three Table1 longtext fields and concatenates them as a single string (always less than 65K chrs) and places the combined data into the single longtext field in Table2 as a continuous string without truncating.  I've searched the web and haven't found a solution that's worked.  Some suggested "ADODB.command" which doesn't compile.  Others mess with QueryDef which also hasn't worked.

Anyone got something that actually works without having to add References, install addins or is a huge cluge?  Thanks very much.

Answer 1

I have no trouble executing an append query to take long text (or short text) fields from one table, concatenate them, and place them in a long text field in a new record in another table.  In VBA, something along these lines ought to work (leaving out any criteria to limit the records appended):

CurrentDb.Execute "INSERT INTO Table2 ([Field1], [Field2], [Field3], [Field4]) SELECT [Field1], [Field2], [Field3], ([Field1] & [Field2] & [Field3]) FROM Table1", dbFailOnError

That assumes that Table2 has all three of the original fields, but adds a fourth that is the concatenation of the first three.  If that -- suitably adapted to your table and field names -- doesn't work, then there's something about your circumstances that you haven't told us.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Hi christoph62,

can you please confirm that when you try to insert the data in to longtext field manually and with the VBA Append Query then what is the length of the data that actually entered in the table?

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

I have setup a recordset for both the source and the destination and it will truncate the long Text data in Access 2013.

{

Dim dbs as dao.database

dim rst as dao.recordset

dim rstSource as dao.recordset

dim strSQL as string

dim strSQLSource as string

strSQL = "SELECT * FROM TableA"

strSQLSource = "SELECT * FROM TableB"

set dbs = currentdb

set rst = dbs.openrecordset(strsql, dbopendynaset) ' Linked table on network

set rstSource = dbs.openrecordset(strsqlsource) ' Local table

do while not rstsource.eof

rst.addnew

rst!F1 = rstsource!F1

rst!F2L = rstsource!F2L ' long field

rst!F3L = Trim(rstsource!F3L & "") ' long field

rst.update

rstsource.movenext

loop}

and additional code to handle errors and closing of the recordsets

Even if the field is trimmed as seen in F3 or not trimmed as seen in F2 the fields are both truncated to 255

Answer 4

I figured out the solution,

I needed to sort by the Long Text field in descending order

strSQLSource = "SELECT * FROM TableB ORDER BY F2L DESC"

I did notice that if I change the Long Text type to Rich Text versus Plain Text, that the Line returns and tab descriptors are not saved in the field.