Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Field Is Too Small To Accept The Amount Of Data You Attempted To Add

    Hi all,

    Using Excel/Access 2007.

    I am trying to insert all contents of a worksheet into a table in Access
    I receive this error message
    Run-time error '-2147217833 (80040e57)':
    The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less
    data
    The worksheet contains 20 columns, 25,000 rows
    I perform this in another process with many more records so I don't believe the 25,000 is a problem.

    The worksheet has 20 columns, the table has 20 fields
    The spelling of all titles/headers has been checked and rechecked.

    There are some cells in the worksheet that are blank
    All fields in the table are text fields. No fields are indexed

    Here is my code for loading the contents of the worksheet
    Code:
          strSQL2 = "INSERT INTO " & strTblCustomerProfiles & " SELECT * FROM [tmpf$] IN '" _
          & wbtmp.FullName & "' 'Excel 12.0;'"
    
         'Do it
          cnt.Execute (strSQL2)
    What else can I check?

    thx
    w

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could have an Integer field where the incoming data is larger, or a text field with a width of 20 where the incoming text is longer, or that type of thing. You might be able to find it by eliminating fields one at a time until you find the offending one, or start with one safe field and start adding fields until you find the bad one.
    Paul

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Paul,

    There smallest text field in the table as set to 50, the largest was set to 255.
    I increased all fields to 255.

    Now the code works as expected.

    Thanks
    w

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •