locked
The Field is Too Small to Accept the Amount of Data you Attempted to Add

    Question

  • I am trying to write a small query which will remove the duplicate entries in my Access table.

    This is what I'm trying to do...

    SELECT DISTINCT * INTO TEMPTABLE FROM ORIGINALTABLE

    DROP ORIGINALTABLE

    SELECT * INTO ORIGINALTABLE FROM TEMPTABLE

    DROP TEMPTABLE.

    I get this error when I do that .

    The Field is Too Small to Accept the Amount of Data you Attempted to Add

    I just tried doing a select distinct * from originaltable I still get the same error

    Please advise.

    Thanks
    Thursday, March 05, 2009 4:26 PM

Answers

  • Hi Vidya Sandela

    This problem occurs because when you set the UniqueValues query property to Yes, a DISTINCT keyword is added to the resulting SQL statement. The DISTINCT keyword directs Access to perform a comparison between records. When Access performs a comparison between two Memo fields, Access treats the fields as Text fields that have a 255-character limit. Sometimes Memo field data that is larger than 255 characters will generate the error message that is mentioned in the "Symptoms" section. Sometimes only 255 characters are returned from the Memo field.

    Error message when you run a query in Access: "The field is too small to accept the amount of data you attempted to add"
    http://support.microsoft.com/kb/896950

    To work around this problem, modify the original query by removing the Memo field. Then, create a second query that is based on both the table and the original query. This new query uses all the fields from the original query, and this new query uses the Memo field from the table. When you run the second query, the first query runs. Then, this data is used to run the second query. This behavior returns the Memo field data based on the returned data of the first query. To do this, follow these steps.

    Access 2003, Access 2002, and Access 2000

    1. Copy the original query, and then name this copy Backup Copy OriginalName.
    2. Click the original query, and then click Design on the Database toolbar.
    3. Click the column that contains the Memo field, and then click Delete on the Edit menu.
    4. Save the query.
    5. Double-click Create query in design view.
    6. Click the Both tab.
    7. Click the original query, and then click Add.
    8. Click the table that the original query is based on, and then click Add.
    9. Click Close.
    10. Add the fields from the original query, and then add the memo field from the table.
    11. Create a link between the table and the query.

      For more information about how to create a link, click the following article number to view the article in the Microsoft Knowledge Base:
      136699  (http://support.microsoft.com/kb/136699/ ) Description of the usage of joins in Microsoft Query
    12. Save the query.
    13. On the Query menu, click Run.

      The query should run as expected, and the Memo field is not truncated.


    Best wishes
    xingwei Hu

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Friday, March 13, 2009 9:42 AM
    Wednesday, March 11, 2009 6:23 AM
  • I don't believe that you can update a memo data type column, when the number of characters exceeds 255, without using a parameter object.

            Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _  
                "Data Source=C:\Test Files\db1 XP.mdb")  
            Dim AccessCommand As New OleDbCommand("UPDATE Table1 SET MemoField=? WHERE [record id] = 1", AccessConnection)  
            AccessCommand.CommandType = CommandType.Text  
            Dim StringValue As String = "Memo text greater than 255 characters goes here." 
            Dim QueryParameter As New OleDb.OleDbParameter  
            QueryParameter.ParameterName = "MemoVal" 
            QueryParameter.DbType = DbType.String 
            QueryParameter.Size = Len(StringValue)  
            QueryParameter.Direction = ParameterDirection.Input  
            QueryParameter.Value = StringValue  
            AccessCommand.Parameters.Add(QueryParameter)  
            AccessConnection.Open()  
            AccessCommand.ExecuteNonQuery()  
            AccessConnection.Close()  
     

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Xingwei Hu Friday, March 13, 2009 9:42 AM
    Wednesday, March 11, 2009 12:25 PM

All replies

  • Thats weird, have you tried importing less data

    SELECT top 20 *  INTO TEMPTABLE  FROM ORIGINALTABLE

    or something like that..



    Arjun Paudel
    Friday, March 06, 2009 6:08 AM
  • I tried that. It throws the same message. I think it has something to do with the datatype(Memo). Not sure why... Im just looking for a workaround for this issue.
    Friday, March 06, 2009 3:55 PM
  • Hi Vidya Sandela

    This problem occurs because when you set the UniqueValues query property to Yes, a DISTINCT keyword is added to the resulting SQL statement. The DISTINCT keyword directs Access to perform a comparison between records. When Access performs a comparison between two Memo fields, Access treats the fields as Text fields that have a 255-character limit. Sometimes Memo field data that is larger than 255 characters will generate the error message that is mentioned in the "Symptoms" section. Sometimes only 255 characters are returned from the Memo field.

    Error message when you run a query in Access: "The field is too small to accept the amount of data you attempted to add"
    http://support.microsoft.com/kb/896950

    To work around this problem, modify the original query by removing the Memo field. Then, create a second query that is based on both the table and the original query. This new query uses all the fields from the original query, and this new query uses the Memo field from the table. When you run the second query, the first query runs. Then, this data is used to run the second query. This behavior returns the Memo field data based on the returned data of the first query. To do this, follow these steps.

    Access 2003, Access 2002, and Access 2000

    1. Copy the original query, and then name this copy Backup Copy OriginalName.
    2. Click the original query, and then click Design on the Database toolbar.
    3. Click the column that contains the Memo field, and then click Delete on the Edit menu.
    4. Save the query.
    5. Double-click Create query in design view.
    6. Click the Both tab.
    7. Click the original query, and then click Add.
    8. Click the table that the original query is based on, and then click Add.
    9. Click Close.
    10. Add the fields from the original query, and then add the memo field from the table.
    11. Create a link between the table and the query.

      For more information about how to create a link, click the following article number to view the article in the Microsoft Knowledge Base:
      136699  (http://support.microsoft.com/kb/136699/ ) Description of the usage of joins in Microsoft Query
    12. Save the query.
    13. On the Query menu, click Run.

      The query should run as expected, and the Memo field is not truncated.


    Best wishes
    xingwei Hu

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Friday, March 13, 2009 9:42 AM
    Wednesday, March 11, 2009 6:23 AM
  • I don't believe that you can update a memo data type column, when the number of characters exceeds 255, without using a parameter object.

            Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _  
                "Data Source=C:\Test Files\db1 XP.mdb")  
            Dim AccessCommand As New OleDbCommand("UPDATE Table1 SET MemoField=? WHERE [record id] = 1", AccessConnection)  
            AccessCommand.CommandType = CommandType.Text  
            Dim StringValue As String = "Memo text greater than 255 characters goes here." 
            Dim QueryParameter As New OleDb.OleDbParameter  
            QueryParameter.ParameterName = "MemoVal" 
            QueryParameter.DbType = DbType.String 
            QueryParameter.Size = Len(StringValue)  
            QueryParameter.Direction = ParameterDirection.Input  
            QueryParameter.Value = StringValue  
            AccessCommand.Parameters.Add(QueryParameter)  
            AccessConnection.Open()  
            AccessCommand.ExecuteNonQuery()  
            AccessConnection.Close()  
     

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Xingwei Hu Friday, March 13, 2009 9:42 AM
    Wednesday, March 11, 2009 12:25 PM