MSAccess: SELECT INTO NewTable cuts long texts

Question

When I use (in MS Access 2003 SP3):

SELECT * INTO NewTable FROM SomeQuery;

MEMO fields are converted to TEXT fields (which are limited to 255 characters), so longer texts are cut.

The output of the query itself is fine and not truncated; the text is cut only in the new table that is created.

An update: I managed to focus the problem on the IIF statement in my query. When I remove the IIF, the new table contains the MEMO field, but with the IIF the same field appears as TEXT. The weird thing is that the query output shows the long strings in full, even when the IIF is being used. Only when it is 'copied' to the new table (by the INTO statement), the text is cut.

Do you know of any problems that IIF may cause to MEMO fields?

Thank you for your answers.

Answer 1

You have here the current workarounds for avoiding any Truncation of Memo fields.

In your case, that may be the result of the query's Properties Sheet including a "set Unique" Values to Yes (which forces comparison of Memo fields, triggering the truncation), or the Format property of the field, e.g. forcing display in upper case (>) or lower case (<).

---

What Access are you using, and what format are you saving your document into ?

In a Access 2000 compatible format, the cell are in Excel5.0/95 format: 255 characters max.

Do you have any other (non-Memo) field with lengthy value you could try to select, just to see if it also gets truncated ?

---

If the output is fine, but the export in a new table does truncate the Memo fields, could you check the following:

In the export dialog under advanced, even though it looks like you can only inlude the name, if you click very carefully to expand column that don't appear, you can change the data type to memo.

Answer 2

SP3 of 2003 is notorious, it may be related to that. There is a hotfix:

http://support.microsoft.com/default.aspx/kb/945674

Answer 3

I have just tested in A2K3 with a make table and appending a memo field. I had no difficulty getting it to append full data to a memo field.

Perhaps you could post the SQL for the query you're using to populate your table. If you're sorting (or grouping) on the memo fields that could do it, because sorting on memo fields is supposed to truncate them to 255 characters (though in the test I just ran on A2K3 SP3 with all the latest post-SP3 patches, mere sorting doesn't truncate but GROUP BY does).

Another issue is that it's usually not advisable to have a Make Table query in a production app. Anything that's happening repeatedly enough that you programmed for it really ought to be appending to a pre-defined table, instead of replacing an existing table. For one, a pre-defined table can have indexes defined on it, which makes it much more efficient to use after it's been populated. Sure, you have to delete existing records before appending your new data, but the benefit is pretty big in terms of indexing. And, yes, you could redefine indexes each time you run your Make Table query, but, well, if it's too much trouble to delete existing data, isn't it even more work to add indexes to the newly-created table?

I hardly ever use Make Table queries except when I'm manipulating data that I'm massaging for some other purpose. It's not always predictable what data types you'll end up with in a target table because it is partly dependent on the data in your source table. That alone makes it inadvisable to use them in most situations.

--
David W. Fenton
David Fenton Associates