Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    39

    Unanswered: The field is NOT too small to accept the amount of data you attempted to...

    I'm getting an error, I'm not seeing the cause:

    "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.

    [end] [debug] [help]
    Debug highlights this segment of code (I just snipped a portion here):
    Code:
     strProc = "Insert into Parts (MfgPN, Manufacturer, Description)" & _
                   "select distinct PurchasedPN, Manufacturer, Description " & _
                   "from DBimport where PurchasedPN is not null " & _
                   "and PurchasedPN not in (select MfgPN from Parts) " & _
                   "group by PurchasedPN, Manufacturer, Description"
                   
         'insert into Parts:
         conTAGdb.Execute strProc
    The "Execute" line is highlighted as the current step.

    I have two VBA scripts that make up this user-operated process:
    The first script: User is prompted to naviagate to a specially formatted file that he has prepared for import, and imports it into a DBimport temporary table.
    The second script: User is prompted to assign a job number, and the script processes the DBimport table into the tables in the database.

    This error is occurring in the second script, which is particularly baffling to me since my DBimport table shares the same column definitions as the columns in the target tables.

    It doesn't specifically implicate one particular column here, but there are only three in this operation:
    MfgPN - "text" in DBimport, "text" in Parts
    Manufacturer - "text" in DBimport, "text" in Parts
    Description - "text" in DBimport, "text" in Parts

    How, if the data is resident in DBimport, could I throw this error doing an insert of this data into Parts?



    Any help is greatly appreciated!!

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    This size of one the text fields in your parts table is too small for one of your import fields e.g. you may be trying to paste an import field of 70 characters into your table that has a field size of 50 characters.

  3. #3
    Join Date
    Jun 2008
    Posts
    39
    Very interesting...



    I thought the two columns were exactly the same, and were even created on the same day, in the same effort, via the same series of keystrokes, by the same DBA.



    This in fact best expresses why I'm puzzled - even attributes that were not explicitly defined, which might be a bit out-of-sight even as I troubleshoot now should be the same, given the diligence of the process that was followed.



    However, I see the field size is 255 in the DBimport table (for ALL columns in that case - this table is simply to dump a preformatted spreadsheet into).



    On the other hand, my Parts table is 50 for each text field. Strange - I thought I had not set any value, in fact I believed Access handled text as variable-length text columns!



    Rather than modify my tables and jeapordize fallout elsewhere, would I be better served to include code in my VBA script to query the table for data longer than 50, and notify the end user? What I don't like about that solution is that the only fix presented to the end user is to modify the data.



    I don't mind increasing the "Description" column width in the Parts table, and don't expect the others to ever exceed 50. But obviously, since the risk is there, I should be programatically checking, wouldn't you agree?
    Last edited by geolemon; 12-02-08 at 11:17.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    It appears that Access when importing or linking a csv or xls will set the Text field size to 255. In your preformatted spreadsheet are the correct length e.g. 50 characters etc.? If so, you could add a table say tbl_raw_data to store your imported data. Then use an append query to add the raw data to your DBimport during the append your could use the LEFT or MID function to only append the first 50 characters of each field. The TRIM function may be suitable but the final length after the TRIM may exceed your limit.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I thought I had not set any value, in fact I believed Access handled text as variable-length text columns!
    It does and you probably didn't. 50 is the default size for text fields, so if you defined no value for it, the 50 would be left there.

    Rather than modify my tables and jeapordize fallout elsewhere, would I be better served to include code in my VBA script to query the table for data longer than 50, and notify the end user? What I don't like about that solution is that the only fix presented to the end user is to modify the data.

    I don't mind increasing the "Description" column width in the Parts table, and don't expect the others to ever exceed 50. But obviously, since the risk is there, I should be programatically checking, wouldn't you agree?
    Personally, I would just increase the field size for your text fields to 255.
    Last edited by StarTrekker; 12-03-08 at 00:58.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2008
    Posts
    39
    I decided to handle this by increasing the size of the description column in the parts table which ultimately receives the description - but only to 100, since that alone is double my "extremely long comment" comfort threshhold... a comment even that length might indicate a problem with the data - certainly would warrant DBA attention anyway.

    And I didn't want to reduce the column lengths in my DBimport table, since that could potentially cause a failure in the import step.

    In my second script, I'm going to add some length checking using the LEN() function, at the same point in my routine where I'm querying to check for duplicates in the data, since I already have mechanisms built for stopping the processing and notifying the operator with descriptive message boxes prior to getting into the portion of the script that actually does data manipulation.
    Last edited by geolemon; 12-03-08 at 12:21.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can afford to be generous with your text fields.
    "thistext" stored in a 8-char wide text field or 255-char wide text field takes the same space on your disk.

    i'm playing with a db as we speak - 131,782 records with 5x255 char, 1x50 char fields = 174,611,150 char from simple maths = 349,222,300 bytes in unicode.

    the real db file with table & queries & forms & code & overhead is 22,188,032 bytes

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Every text field I have is 255.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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