Memo fields truncated by TransferText

[usenetseu3]

Ok, so this is a topic virtually beaten into the ground on this
newsgroup. After searching and working on this issue for a while, I
have decided I won't get any further much faster without help.

As the topic states, my memo fields are being truncated on export. I am
using a VBA script in my Access 2003 database to read in a CSV file,
process it, and export it into CSV again. I have verified that my memo
fields are preserved during import and during the processing, but my
exported CSV files are all truncated to 255.

Any help, criticism, advice is welcome.

I use a TransferText command to import a CSV file into a table named
"importedData". This table contains a field, "memoField" with data that
is typically around 600 text characters long. Using Design View I am
able to verify that "memoField" is indeed of type Memo (although I of
course made sure of this using an import specification) and using
Datasheet View I verify that the data in "importedData" has not been
truncated. Good so far.

I now need to process the fields in "importedData" and export the
results into another CSV file, using TransferText. Per the instructions
on the Microsoft Help website,
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668, I
attempt to prevent truncation of the data in "memoField" by creating a
table with a Memo data field and using an append query, like so:

sql_string = "CREATE TABLE " & newTable & " ( newMemoFieldMemo );"
DoCmd.RunSQL sql_string
sql_string = "INSERT INTO " & newTable & " ( newMemoField )" & _
" SELECT DISTINCT importedData.newMemoField AS newName" &_
" FROM importedData" & _
" WHERE newMemoField Is Not Null;" & _

and then export, like so:

DoCmd.TransferText acExportDelim, "Export Spec", newTable, newFile,
True

I _believe_ I have covered all my bases. Does anyone think if any of
the cases under which a Memo is truncated to 255 characters apply here?
Is there something I am overlooking?

Let me know if I can provide any more information that would help shed
some light on the issue. Many, many thanks in advance.

Beaten into the ground,
Usenet Seu

 

[usenetseu3]

A link posted elsewhere in this newsgroup had the solution:
http://allenbrowne.com/ser-63.html

The fact that I had a DISTINCT clause in my sql query led to the Memo
field being truncated during comparison with other fields.

 

[jinpol]

I encountered the same problem in Access 2010 when I tried to export a Query to a text file.
What I did to solve the issue is the following;

1. Go to Navigation Options and enable show system objects (File>Options>Navigation Options>...)
2. Open MSysIMEXColumns table
3. Find the FieldName and set the DataType to "12" (Memo) instead of "10" (Text)
4. That's it!

Note: Just be sure you are editing the correct Import Export Specification. You may check the SpecID in MSysIMEXSpecs table. Not tested in CSV or in any Import Specs but I think it should work. Thanks