Record is Too Large! 1

[khwaja]

This is the message I am getting when I try to add more text to my record. My table has 115 field and most of these are text using permissible length of 255 characters. This cocers lease information and various conditions of a property.

My questions are:

Is there an overall limit of a record (ie 2k ) as advised by the Access Help. If so, i checked my records indivdially and none of these exceedd this. I used LEN function in Excel to catch the bad record but to no avail.

If I were to change the text field to memo field, does this still fall into 2 k requirement of Access? I am assuming that this change will not result into loss of data.

At the end of the day, I would like to be able to add more data to my table. What are the options available to me.

I will appreciate to receive some support from you guys.

Cheers

Arshad

 

[KenReay]

Hi

Help (A97) says

Number of characters in a record (excluding Memo and OLE Object fields) 2,000

so it looks like you could make (some) into memos.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MichaelRed]

But you should (re) consider the structure. 115 fields of 255 characters is CONSIDERABLY more than 2K, so SOME adjustment is required. 115 fields is way much to be working with on a regular basis, so it would appear to be useful to split up the table into several or more related tables, where you can pull up information relative to a specific topic.

Just as importantly, WHY are all fields 255 characters? In general, db design is not that different from everyday life. You would almost certainly NOT design a paper form with space for 255 characters for a "First Name", so why did you do it for a database?



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[khwaja]

Thank you Ken. I might consider changing some fields to memo after further discussion with you and Michael.

Thank you Michael for your feedback. As I mentioned, most are 255 and NOT all fields. This table is used to record primary details of lease document which may include description of various conditions geverning the lease. As most of the time it refers to clauses and legeal references, I needed longer fields for one store. Initially, when I built the application, there were not as many fields but later as it was embraced, users kept increasing the volume of information which is known as lease epitome.

I would love to be ablwe to split the table if there is a logic to it but I am not too sure how to manage one to one relationship so that as soon as I add one record in the primary table, same record is added to the secondary table. I need some guidance on this. I am using this form, to produce a report for each store. As stores are created once only, it requires nominal maintenance. Some fields are dedicated to prividing clause reference so that increaes the number of fields.

Look forward receiving your advice.

Cheers

Arshad

 

[MichaelRed]

Beauty -and database design?- are in hte eye of the beholder. So far, I have not seen any hard rules or even real 'guidelines' concerning how to split a larger table into more manageable groups of smaller tables. The best I can suggest is that the trend (and suggestion) is that deep is prefered to wide (e.g. more ROWS and fewer fields). A classic example is the use of numerous fields which are identified with a dangling suffix (often the "-N&quot, which relates to being "WIDE", as opposed to having TWO fields, one for the value and another which identifies the value as a specific member of the "-N" grouping.

Wheather this is within your capability or not, I don't really know, but it would require some careful study and probably a seperate implementation as the app would need some (significant) revision to accomodate the changes.

As to the approach to simply splitting a table into two (or more) related tables, the concept here is simply to select fields which are always or often used together for inclusion in hte same table. With out detailed review and understanding of the process the app is modeling, no one will be in a good position to advise on that issus. However there is a reat deta of info on how manipulate the related records with respect to the creating the related records properly.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[khwaja]

Thank you Michael for your insightful reply.

I need to be able to have this DB going while reviewing all possible options. so I need your advice on just two aspects.

1. If I change the text filesld to memo field, would this remove the limitation I curently have ie being not able to add more data.

2. Could you guide me on the issue I raised in the last pargraph of my reponse relating to 1 to 1 relationship and whether one could create a corresponding record in the secondary table.

Kind regards

Arshad

 

[MichaelRed]

"Reviewing" your options is best done long before you arrive at this point. You need to know the expected sizes of the data fields BEFORE generating the tables. Simply using the MAX (255 Characters) for EVERY field even in a small db is gross neglience. First step is to review the NECESSARY field sizes and trim them accordingly. Where you need a field size to be "large", consider making these into memo fields. Originally, you mentioned the db was " ... for lease information ... ". I have not worked with using Ms. A. as a document (lease) repository. The little exposure I have had to others working in this arena suggests that the [ACTUAL] lease should be an independent document, perhaps assembled through selections of clauses (or paragraphs). An example of this approach is available in the samples databases supplied w/ Ms. A. and I am sure the example is both clearer and easise to access than advice through public bulliten boards.

The best advice I can offer to you is to drop this app or secure professional (e.g. paid) help from someone who is well qualified in the specifics of the process (Document Management).

I do not mean to be/sound unkind, but my knee jerk reaction to your posts is that you are quite unqualified for this application, and not likely to be able to complete it through advice received here. A LEASE is a legal document and cannot be regarded as just another collection of short data items. Legal documents need to be thoroughly secured with originals and legal signatures.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[khwaja]

Thanks Michael. It is not a fully fledged lease/document management system I am modifying, it is what is known as 'lease epitome' , a shortened version of lease with no legal implcations, primarily used to pull reports based on dates to be exercised for options and some basic reports on lessor and lessee, etc.

Based on earlier advice, I have rationalised field sizes and assigned memo fields where necessary. Application is working fine now.

As the application was embraced long after I built based on original requirements, these change were necessary. Otherwise, I am quite mindful of the fact for completing initial user requirements.

I am not a terribly experinced Access user but based on my growing knowlege and being able to create some good systems, I am motivated to learn and apply. I learn a lot from these forums but sometimes without knowing the full background, I come across strong coments from senior users in this forum but they are well meaning.

I still very much appreciate your help. Just sometimes, try giving benefit of doubt to people like me.

Regards

Arshad