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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "WTL" <WTL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:AE750287-7337-448B-892B-7BC18EFD9D57@xxxxxxxxxxxxxxxx
> > 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
>
>
>
.

Relevant Pages

  • Re: Is there a maximum size of field caption property in a table?
    ... Compacting may help temporarily, but it really needs a redesign. ... has had 10 diseases, they appear in 10 records in this table. ... > But at about 140 fields I can not enter anymore captions! ... > does not seem to have a character by character relationship (if I remove ...
    (microsoft.public.access.tablesdbdesign)
  • Re: how do i automatically delete many captions
    ... character". ... "Crios" wrote: ... there a method where I can automatically remove ALL captions rather than ... Thanks for any assistance. ...
    (microsoft.public.word.docmanagement)
  • Re: Change a field name
    ... i'm with you, Duane. ... > I don't mind adding the captions that I want in my forms and reports. ... you use the query field caption with spaces. ...
    (microsoft.public.access.queries)
  • Captions in Legacy Reports
    ... I like the way it handles photos but have been unable to get either captions or descriptions to print in reports generated in Legacy 5 Deluxe. ...
    (soc.genealogy.computing)