MS Access COLUMN TRUNCATION after moving data between tables


G

Guest

Hello,
PROBLEM DESCRIPTION:
A column in an MS Access which holds 423 characters is being truncated at
255 when I run an "Insert Into...Select From" query. The new string does not
have the closing double quote that it should have. When exported out of the
DB and opened in MS Excel, the subsequent row information as well as the rows
are wrapping into this one cell until Excel sees a closing double quote.

A SCENARIO:
I imported some information from a tab-delimited text file into an MS Access
Database temp table. The information that is stored in a temp table on import
is then transported over to the final table using an Insert statement.
While in the temp table the row in question has column member that holds a
string which is 423 characters long (with opening and closing double quotes
surrounding it). When I run a simple Insert query (Insert * into finalTable),
"some" of the descriptions are being cut off at 255 characters. This creates
a major problem on export because 1. The string loses all of the information
2. When opened in MS Excel, the subsequent cell information is wrapped into
this one cell until it comes to another closing double quotation mark.

ATTEMPTED TROUBLESHOOTING:
- Before Import: I opened the Excel file and formatted the columns to make
sure that the column in question was not considered "Text". It was left as
"General"

- In The DB: I checked the Import Specifications and the data type for the
column in question is Memo. I verified that the temp table column is set to
Memo and after import NONE of the descriptions are truncated. I verified that
the finalTable column is set to Memo, but after coming over from the temp
table some of the descriptions are truncated (I don't think that it's a
column size issue since most of the text over 255 did not get cut off
throughout the whole process).

- After Export
I check the Excel file and most of the text descriptions that are over 255
did not truncate, but some of them did.

QUESTION:
Is there some sort of invisible formatting that text can have within a
column in an Access table? Why would only a 3rd of the description truncate,
when the other 2 3rds have descriptions well over 255 and they did not? Is
there a setting in Access that looks for some character and thinks that it
indicates that the field is of type Text instead of the previously specified
Memo type? Can anyone help me? :)

Thanks for any assistance.

RodneyM
 
Ad

Advertisements

T

Tim Ferguson

A column in an MS Access which holds 423 characters is being truncated
at 255 when I run an "Insert Into...Select From" query.

Max size of a Text column is 255. Define the column as a memo instead.

Hope that helps

Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top