Is there a maximum size of field caption property in a table?

[Guest]

Using access 2000 (because that is what client has). I have a table with a
large # of fields, 209 at present but it will come down a few when test
fields are removed. I am setting a descriptive caption on the fields to
display as labels when they are placed on a form. Most captions are 2 - 4
words but a few are longer until we see the display on the form and finalize
it.

But at about 140 fields I can not enter anymore captions!
I get a "Property value is too large" error (Jet Error 3309) when saving the
table.
If I go back and delete a caption on a previous field then I can enter one
on the next field (which gave the error before). This makes it seem like
there is some limit to the total space allocated for storing captions. It
does not seem to have a character by character relationship (if I remove 1
character from a previous caption I can not add a 1 charcater caption to
another field, but there may be other overhead sotred as well).

Is this a default set somewhere that can be changed or something built in to
Jet that can not be modified? Or have I maybe just screwed up this table
somewhere? Of course I have compacted and repaired it as I have worked on it.

Other info on table, it is normalized despite the large # of fields (they
are all unique things that pertain to 1 specific record), Other than a few
memo fields (none of which have default values set) most of the fields are
numbers and yes/no fields. It has about 110 records and will probably not
have many more than that. (Kind of odd to have more fields than records but
that is the need for this particular table). It will have relationships to
other tables but none are set now. It only has 1 index at this point and I
have removed that for testing which made no difference. The field names are
descriptive but most are between 10-15 characters with a few longer and
shorter. All the fields have descriptions in the table design (which are to
show up at the bottom of the forms) and these are longer than the captions
since they explain more that the short caption does. Generally from 20 - 70
characters.

I know if I get rid of a few test fields (used now as design placeholders
for future work) it will make the table smaller but I only have maybe 10 of
those and I am about 50 fields away from finishing the captions.

I could split this into 2 tables of 100 fields each but it is really just an
arbitary thing since the actual data all applies to each of the 100+ records
and this will greatly complicate future coding since there will be over 100
reports and lots of calculations performed on this limited set of data.

Any suggestions or links to more info on this will be greatly appreciated.

TIA

 

[Allen Browne]

The error message does indicate that Access has run out of space to store
the fields, attributes, and properties of the table and its fields.
Compacting may help temporarily, but it really needs a redesign.

There is no way that this table is normalized.

You say you have a large number of yes/no fields, presumably so you can
select which one apply to this record. I'm picturing something like a
medical database where you have yes/no fields where the user can check the
diseases the person has had, so there are check boxes for Measles, Mumps,
AIDS, and so on.

That really needs to be broken into 3 tables:
- The original table for (the patient in this example);
- A table of disease names (one record for Measles, another for Mumps, ...)
- A junction table containing the PatientID and the DiseaseID. If a person
has had 10 diseases, they appear in 10 records in this table.

The same approach probably applies to many of your text fields.

That's the essential part.

As a really minor issue (probably just a question of style), I'm not sure
that captions in the table are a good idea either. When you view the data in
your table or query, it obfuscates the actual field names, which slows down
the development task.

 

[Guest]

Thanks for your reply Allen.

BTW I have been to your site many times and appreciate the excellent info
you have available there. I highly recommend it to others who may see this
thread.

I am looking for something specific that says exactly how much space Access
has to store this information and maybe where it is stored. If you have some
reference for that it would be great.

It is surprising to me that this is even an issue since nothing I am doing
seems to violate any of the published specs I have found for Access. And
somehow this is the first time I have run into this and that dates back to
the Omega beta. I do have the Q323657 article but that does not have what or
where etc the limits are. Being able to provide that as documentation to the
client will justify other wise odd design decisions (work arounds).

I like your analogy of the medical history and disesases someone may have.
But that is not the case here. There are actually a lot more fields that will
be in the database than what is in this table. Even though this has a lot of
Yes/No fields they are being used to store On/Off data. Think of a large
computer motherboard with lots of jumpers. Most have 2 positions, jumper on
and jumper off. But the configuration of each motherboard is unique and it is
necesary to record both the on and off position of each of these jumpers.
There are some that can have more positions and that data is stored in a
number field. Plus there are some other values of parts on the board and
readings taken during assembly or testing that must be recorded with the
jumper settings in use. This data is unique to each particular motherboard.
The result is a fully populated table with no repeating values except for
on/off or coincidental readings of 10 digit vales that came out the same by
chance.

There are other fields that do have repeating values, like the assembly
team, location, test machine used to get the values, component supplier, date
of manufacture, etc which are (or will be) linked from related tables.

I can split this large table into 2 or more tables with a 1-1 relationship
but they will still be fully popuilated with unique data. The good part is
that there will never be more than 125-150 actual records in the table. That
is the monthly capacity and short of discovering intelligent life elsewhere
it is not going to increase. The current design will archive each months
production data off and start witjh an empty table again.

And yes I realize how odd it is to have a table or database for that matter
where the number of fields far exceeds the number of records. (I don't recall
having this in the last 30 years of DB programming). If this was not a closed
door government project I would think about an article.

This is really just a data display and reporting system. There are about a
dozen forms that display the data and there will be about 100 reports that
give analysis on it. The data itself is gathered in other systems and will be
populated in this system programaticly with some Oracle SQL and VB code. This
is only a system for management to see current operations and results faster.


While displaying captions insetad of field names in a dtatsheet view has
pros and cons that is not an issue here. As a developer I like seeing field
names but generally never show them to end users. But if the captions are
very large then they become useless in a datasheet view which we sometimes do
show to users. In this case the users will never see the data in a datasheet
view (unless they work on the MDB with some other applications which is not
likely). They will have this in a runtime environment only with a custom
command bar menu that controls what they see and do. But with at least 12
forms and 100 reports having the caption set one time in the table is a lot
less work that setting it every time in every other place it will appear.

Thanks again for the guidance and any further light you can shed.

 

[Allen Browne]

Okay, if you have been designing databases for 30 years, you probably know
what normalization is.

There are usually multiple possible design solutions. One option would be a
table of settings related to the item in the main table. Using your
motherboard example, the table could have fields such as:
ComponentID p.k.: which component on the motherboard
MotherboardID foreign key.
ComponentTypeID jumper block, connector, ...
SubID pin number (where applicable)
SubIDValue value (on/off, voltage, whatever)
You may have good reasons why that's less desirable than the approach you
have taken; without knowing more it seems to me that a field for each switch
constitutes repeating fields.

If that's not practical and you are hitting the wall, unessential properties
such as Caption would surely be the first to go. You can still use whatever
labels you want in the interface, or alias the fields in a query.

I've never seen figures on the amount of space set aside to define a
TableDef. It's probably reasonably difficult to define: it would depend on
the field names (lengths), data types (overheads, including pointers for
BLOBs), and properties such as Format, Caption, and Description that don't
exist until you use them, so presumably the available space depends on how
many fields have which properties, and then which properties are actually in
use. Then there's the overhead for the table itself and its properties
(strings such as table-level validation rule/text, optional properties such
as SubdatasheetName, and possibly custom properties), perhaps indexes?
constraints?