• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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?


0
sirbounty
Asked:
sirbounty
  • 4
  • 2
3 Solutions
 
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.
0
 
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.
0
 
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sirbountyAuthor Commented:
Well, adding 5-15 to each field seems to have resolved the problem... : |
0
 
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.
0
 
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...
0
 
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. :^)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now