Copying Table

[shevek]

Hi,

When I try to execute a SQL query in access I get the next error (sorry translated from spanish)

runtime error 3163: The field is too little to accept the amount of data you are trying to add. Try to add less data.

The SQL is:

INSERT INTO _AlbumsTmp (Album, Anyo, Comentario)
SELECT DISTINCT Album, Year, Comment
FROM _Import

The error comes from Comment/Comentario field that is a memo field (maybe because you cannot put memo field with DISTINCT?)

Any hint?

 

[The_Doc_Man]

When converting from a field of type MEMO to a field of type TEXT, Access compares the actual amount of data in each MEMO (source) field to the pre-allocated size associated with the TEXT (destination) field. Your error is occurring because at least one of your memo fields is longer than the associated text field in the destination record.

There are a couple of ways to handle this.

First, write a query against your source table. In the row titled "Field", put one of the entries as "Len([myMemoField])" and of course substitute the right field name. This will tell you the size of the fields in question.

Next, decide how long your text field can safely be. Please note that Access text fields can go up to 255 bytes. BUT if the data to be loaded is shorter than that, Access does not allocate all 255 bytes. It ALWAYS allocates only the amount of space needed. (If the destination table is actually an ODBC-linked table, this isn't always true...)

Finally, in the query that populates the destination table, do not copy the memo field directly. Instead, in THAT query grid, use "Left$([myMemoField],myMaxTextSize)" where the memo field name is used in brackets and the NUMERIC maximum size of that field is used for the non-bracketed item.

 

[shevek]

sorry i expressed myself in a bad way, both fields (comment and comentario) are memo fields, in fact table structure is exactly the same, just a copy/paste one from the other.

 

[shevek]

Thank you for your answers, but I think the error does not come from there.

I'll state clear so maybe anybody can help:

SQL query:

INSERT INTO Albums(Album, Year2, Comment)
SELECT DISTINCT Album, Year2, Comment
FROM _Import

Albums and _Import tables are exactly the same, except that Albums has no records.

_Import
Album Title Year2 Comment

Album1 song1 year1 This is the comment for album1
Album1 song2 year1 This is the comment for album1
...
...
Album1 songn year1 This is the comment for album1
Album2 song1 year2 This is the comment for album2
Album2 song2 year2 This is the comment for album2

So every record repeats Album, year and comment fields for every different song, but comment is a memo and seems that cannot be selected with distinct clause.

I would like to make SELECT DISTINCT (Album, Year2), Comment
but thats not possible.

Thanks in advance.