yet another memo field truncation issue

[ChristineMD]

I have an access (2010) database where a vendor whom we no longer deal with wrote a little piece of code to combine order details from multiple rows into a single MEMO field. So, multiple records into a single record.
In the source table, no field of order detail exceeds 255 characters, but the field is defined as a memo field, nonetheless.
The VB code writes the combined data out to a different table, with the combined order details also going into a MEMO field. It works fine. I never see any data truncations.
I copied this module into another database and modified it very slightly - i.e. changed the function, field(s) and table names. And now it’s truncating every combined field @ 255 chars. The order details in the original table are in a memo field (also never exceed 255 chars) and the table the vb writes to also is a memo field for this data.

For the LIFE of me I cannot see why it is not working th same… WHY??

Any help will be appreciated, please let me know if there's any additional info I can provide!!

This code WORKS fine:

Public Function CreateExportO30102() As Boolean
    On Error Resume Next
 
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim strOrder As String, strEmail As String, strName As String, strDescription As String
 
    Set db = CurrentDb()
    'Call RecreateTables(db)
    sSQL = "DELETE FROM O30102_for_export"
    db.Execute sSQL
 
    sSQL = "SELECT order_no, email, name, description FROM O30102_EMail ORDER BY email, order_no ASC"
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
    If Not rst.BOF And Not rst.EOF Then
      rst.MoveFirst
      strOrder = rst!Order_No
      strEmail = rst!Email
      strName = rst!Name
      strDescription = rst!Description
 
      rst.MoveNext
      Do Until rst.EOF
        If strEmail = rst!Email Then
          strOrder = strOrder & ", " & rst!Order_No
          strDescription = strDescription & Chr(10) & rst!Description
        Else
          sSQL = "INSERT INTO O30102_for_export (order_no, Email, Name, Description) VALUES('" & strOrder & "','" & strEmail & "','" & strName & "','" & strDescription & "')"
          db.Execute sSQL
            strOrder = rst!Order_No
            strEmail = rst!Email
            strName = rst!Name
            strDescription = rst!Description
        End If
        rst.MoveNext
      Loop
 
      ' Insert Last Record
      sSQL = "INSERT INTO O30102_for_export (order_no, Email, Name, Description) VALUES('" & strOrder & "','" & strEmail & "','" & strName & "','" & strDescription & "')"
      db.Execute sSQL
    End If
 
    Set rst = Nothing
    Set db = Nothing
End Function

This code does NOT, it truncates the field @ 255 chars.

Public Function CreateExport() As Boolean
    On Error Resume Next
 
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim strEmail As String, strline As String
 
    Set db = CurrentDb()
    'Call RecreateTables(db)
    sSQL = "DELETE FROM export"
    db.Execute sSQL
 
    sSQL = "SELECT Email, line FROM mtOrdShp ORDER BY email ASC"
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
    If Not rst.BOF And Not rst.EOF Then
      rst.MoveFirst
      strEmail = rst!Email
      strline = rst!Line
 
      rst.MoveNext
      Do Until rst.EOF
        If strEmail = rst!Email Then
          strline = strline & Chr(10) & rst!Line
        Else
          sSQL = "INSERT INTO export (Email, line) VALUES('" & strEmail & "','" & strline & "')"
          db.Execute sSQL
            strEmail = rst!Email
            strline = rst!Line
        End If
        rst.MoveNext
      Loop
 
      ' Insert Last Record
      sSQL = "INSERT INTO export (Email, line) VALUES('" & strEmail & "','" & strline & "')"
      db.Execute sSQL
    End If
 
    Set rst = Nothing
    Set db = Nothing
End Function

 

[Ziggy]

Hi Christine,

The only real difference that jumps out to me is this part ( in working code)

 strOrder = strOrder & ", " & rst!Order_No

it would pass an extra comma into the memo field so who knows maybe it causes a break... but it is impossible for me to really know without testing with your data.

anyways, I will give you debugging tips that I would use in this scenario....

1. wrap the fields with a Trim function

2. Set a break point in your code so it stops when you run, then press F8 lto step through the code. when you do this you will be able to open and close your table to see how it changes with each Pass ( tip use F5 with a break point on a loop to go faster)

3. Also with a breakpoint but instead of checking table, use the Immediate window and look at the results of the variable by putting in your variables...

 ?sSQL

The ? is a shortcut to print to screen the variable sSQL ( can also use watch window).

 

[xenou]

Just a strange off chance here but are you sure you just can't see all the data? this will have a lot of line breaks and will be on many lines. Maybe it's just not visible in a text box or what have you?

Another off chance might be rather than using a SQL insert would be to open a DAO table type recordset (on the destination table) and update it / add to it ... maybe it will keep better track of the data type if its done that way.

 

[ChristineMD]

Appreciate the tips, immensely, hope to have time to try them today!

 

[boblarson]

To go with xenou's response - A memo field can only hold 65,535 characters when using normal methods of insert, retrieval. In order to use more than that you must use DAO via code. If done with VBA and DAO (iterating through the recordset and adding it, or reading it, that way you can do up to 2 Million characters. You don't say how many characters you have tried to input when the truncation appears.

Also, it may not have anything to do with this (although it might), but if you use any queries pulling memo data (I wonder about mtOrdShip as it sounds like a table from a make table query) you can't have any criteria on the query where the memo field is included. So if you need to have criteria, you need to create the query first without the memo field and put the criteria on it and then you use that query in another and link it to the original table and then include the fields from the criteria query and only the memo field from the table into the second query. That will keep the memo field from truncating with it.

The next thing is that the method of exporting is very important. Ways like

DoCmd.OutputTo

or

DoCmd.TransferSpreadsheet

do not work with memo fields (at least I've had none work). They will truncate. So, I have had to resort to Excel automation code to export and with my most recent one I couldn't even use the CopyFromRecordset method. I had to iterate through the recordset and set the spreadsheet cells one at a time.

 

[ChristineMD]

To go with xenou's response - A memo field can only hold 65,535 characters when using normal methods of insert, retrieval.

.
.
.

If I calculated that each line detail was 255 chars (which it's not) I would have to have 255(ish) line items combined before hit the limit. I can only have 100 lines on an order (back end system limitation) and really rarely see any orders with over say 10 line items, so I don't believe it is possible for me to hit the 65k limit.


I did think of the whole make table thing before I posted my question. I manually deleted the contents of the file and changed the query to an append, so that the memo field designation wouldnt' be lost during a make table operation, but still no joy.

I am wading through the suggestions though, and do immensely appreciate all the responses here! I always get great direction from the folks on this board, and do appreciate it.

 

[ChristineMD]

Just a strange off chance here but are you sure you just can't see all the data? this will have a lot of line breaks and will be on many lines. Maybe it's just not visible in a text box or what have you?

Another off chance might be rather than using a SQL insert would be to open a DAO table type recordset (on the destination table) and update it / add to it ... maybe it will keep better track of the data type if its done that way.

Can we say "Brain Damage"? It IS working perfectly. It just wouldn't scroll within the field. I had to increase the field height in order to see all the data.

I cannot believe how dumb I am! The simplest things I miss!

Zenou, thank you. You're a genius.