Jet error - field is too small

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! :(

So,
(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?


LVL 67
sirbountyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vb_jonasCommented:
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.
jhanceCommented:
>>(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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sirbountyAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sirbountyAuthor Commented:
Well, adding 5-15 to each field seems to have resolved the problem... : |
jhanceCommented:
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.
sirbountyAuthor Commented:
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...
sirbountyAuthor Commented:
I did some further examination and it seems that I've somehow corrected it by better-identifying the field types.
Thanx for the help. :^)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.