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

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?

--
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:47C1FD3F-0016-4A8C-B372-5C958F26A354@xxxxxxxxxxxxxxxx
> 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 dtat*** 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 data*** view which we sometimes
> do
> show to users. In this case the users will never see the data in a
> data***
> 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.
>>
>> "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.


.

Loading