Jet error - field is too small

Posted on 2006-04-26
Last Modified: 2010-04-17
I'm using vbs code to create a database table in Access 2003.
I've defined the fields to be 'about' the right character limit, with a shave more where it may be anticipated.
However, once the code runs, and I try to execute my Insert statement, I get this error:

Microsoft JET Database Engine: The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.

Must be a JET issue, as I took the statement exactly and successfully pasted it as an INSERT query in Access.
After traversing through all fields that were written to the table, each one is lengthed the same as I set it up in my code! :(

(a) how do I find 'which' field (or fields) are too long for Jet
(b) or how do I get around this?  Is there a dynamic method to creating field lengths?

Question by:sirbounty
    LVL 12

    Assisted Solution

    Hi, that sounds strange, perhaps you can set only one field at a time, just to see which one is giving the error. A memo field is dynamic in length.
    LVL 32

    Accepted Solution

    >>(a) how do I find 'which' field (or fields) are too long for Jet

    Check your input data to the database in your VBS code and validate the lengths of each field BEFORE sending it to the database.  Then you can take appropriate action like truncating or reporting the error.

    >>(b) or how do I get around this?  Is there a dynamic method to creating field lengths?

    The maximum length of a text field in Access is 255 characters.  It doesn't hurt anything to setup the table to be 255 even if the data will often be shorter.  If it's going to be longer than 255, use the Memo field type instead.

    But, in general, you will run into problems if you try to fine-tune the field size with variable input data.  It sounds like that's what you're doing when you said a "shave more".  If you need to cut it this close, check the data first as recommended.
    LVL 67

    Author Comment

    In the past, I'd set one up to be 255 for each field and found that Jet doesn't like that either (if you exceed some magical # of fields that are 255).
    It would take quite some time to go through these fields one at a time unfortunately.  
    I don't understand why, when looking at the field, it shows the same size of the actual data as my code established - I 'thought' perhaps Access caught the problem field and simply expanded it, but I don't find any reference if that's the case.
    LVL 67

    Author Comment

    Well, adding 5-15 to each field seems to have resolved the problem... : |
    LVL 32

    Assisted Solution

    I'm not aware of any such limit but it's possible that there is one.  But how many columns are you putting in your table anyway?  Good DB schema design says that 7 is the most you should use.  I'd say 10 is too many...  I know that 10 columns with 255 will work as I've seen that (bad) situation.  My recommendation is to break up such a large table into multiple smaller and more manageable ones.
    LVL 67

    Author Comment

    I can't remember where I found that info, but I've tested it and it's accurate.
    I'm generating about 8 tables, most with less than 5-7 fields, but the 'main' one has probably 20 (or so), but there's no effective way to disect it (nor valid reasoning).
    I ran through my pilot data and rematched column widths to be more accurate (where possible) and it's still running fine.
    I guess I'll just have to wait until I get all the live data and try again...
    LVL 67

    Author Comment

    I did some further examination and it seems that I've somehow corrected it by better-identifying the field types.
    Thanx for the help. :^)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    A short article about a problem I had getting the GPS LocationListener working.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now