Access2013 Copy LongText from one LongText field to another RRS feed

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

    Wednesday, December 14, 2016 8:09 PM

All replies

  • 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

    Wednesday, December 14, 2016 9:40 PM
  • 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?



    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 MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 3:23 AM
  • 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!F1 = rstsource!F1

    rst!F2L = rstsource!F2L ' long field

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




    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

    • Edited by Echolite Tuesday, October 24, 2017 7:50 PM
    Tuesday, October 24, 2017 7:49 PM
  • 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.

    Tuesday, October 24, 2017 8:34 PM