If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is there a maximum size of field caption property in a table?
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 |
#2
|
|||
|
|||
Is there a maximum size of field caption property in a table?
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" wrote in message ... 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 |
#3
|
|||
|
|||
Is there a maximum size of field caption property in a table?
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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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. "WTL" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Microsoft Access Limits (file size, table records, users) | Mike | General Discussion | 4 | November 4th, 2005 03:01 AM |
Update table | Kevin | Using Forms | 4 | September 29th, 2005 04:12 PM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Get data from combo box to popluate the next box | Lin Light | Using Forms | 4 | December 30th, 2004 05:01 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |