Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Error 3047 "Record is too large" --Please help!

Discussion in 'Information Technology' started by Cruiser, Nov 17, 2005.

  1. Cruiser

    Cruiser
    Expand Collapse
    Guest

    I am in the middle of designing a database to track our sales and while
    testing the form, I received this error message "record is too large". I
    really don't have that much data in this particular record. Is there a limit
    on the amount of characters for each form? My form is large and I hope to
    make it two pages so it will hold a ton of info. What can I do? Thanks in
    advance.
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 14:10:33 -0800, "Cruiser"
    <Cruiser@discussions.microsoft.com> wrote:

    >I am in the middle of designing a database to track our sales and while
    >testing the form, I received this error message "record is too large". I
    >really don't have that much data in this particular record. Is there a limit
    >on the amount of characters for each form? My form is large and I hope to
    >make it two pages so it will hold a ton of info. What can I do? Thanks in
    >advance.


    There is in fact a limit of 2000 characters actually occupied in any
    single record. Memo fields (of any size) take 16 bytes of this limit.

    It sounds VERY much like you're trying to put "a ton of info" into
    many many fields in a single record. This almost surely indicates a
    wide-flat, non-normalized table. Could you describe your table
    structure, and (some examples of) the fields that you're storing in
    it? Note that a 30 column table is EXCEEDINGLY wide in normal
    practice...

    John W. Vinson[MVP]
     
  4. Cruiser

    Cruiser
    Expand Collapse
    Guest

    Thank you for responding. I will try and explain what I am designing.... We
    have a retail appliance business and right now we are using Sales
    tickets/invoices that are filled out by hand. They require a large amount of
    data (i.e.): sold to and deliver to customer name/addr and 4 phone numbers,
    7 fields for model number, color, brand, etc. (ten lines of this type in case
    10 products are purchased), we also have various fields for notes about the
    purchase/delivery info, ticket status, dates, etc.

    Right now in designing this database to mimic our form, essentially, we are
    trying to just have a computerized copy of our tickets. I already have over
    130 fields and I am not done yet. We DO NOT want a customer database, as we
    need the sales ticket to say exactly how it was entered in, so if it was tied
    to a customer database and their address is changed, it would change our
    ticket.

    On the table "Orders" in my database, I have listed 10 lines for product,
    each having their own fields (i.e.) Qty1, ModelNumber1, Color1.......Qty2,
    ModelNumber2, Color2, etc. Some of these fields are a lookup field. There
    are various prices, some with tax and someout with tax, so the unit price and
    extended price are not a calculated field, the salesman will input these
    himself. Our staff is not very computer literate and the easier I can make
    it for them, the better. Also, I have experience in databases, but am
    learning Access as I go along. I think I have a pretty good handle on it.

    I know this is not the normal way of designing a database and now after
    learning of the limitations with only 2000 characters and 2GB of storage for
    all, I am getting scared. We do a great deal of business and would like to
    store all the records indefinitely.

    We would like to start using this database as soon as possible, I would
    greatly appreciate any suggestions that you might have for me. Thanks in
    advance.

    Rita Lucy



    "John Vinson" wrote:

    > On Wed, 16 Nov 2005 14:10:33 -0800, "Cruiser"
    > <Cruiser@discussions.microsoft.com> wrote:
    >
    > >I am in the middle of designing a database to track our sales and while
    > >testing the form, I received this error message "record is too large". I
    > >really don't have that much data in this particular record. Is there a limit
    > >on the amount of characters for each form? My form is large and I hope to
    > >make it two pages so it will hold a ton of info. What can I do? Thanks in
    > >advance.

    >
    > There is in fact a limit of 2000 characters actually occupied in any
    > single record. Memo fields (of any size) take 16 bytes of this limit.
    >
    > It sounds VERY much like you're trying to put "a ton of info" into
    > many many fields in a single record. This almost surely indicates a
    > wide-flat, non-normalized table. Could you describe your table
    > structure, and (some examples of) the fields that you're storing in
    > it? Note that a 30 column table is EXCEEDINGLY wide in normal
    > practice...
    >
    > John W. Vinson[MVP]
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 17 Nov 2005 07:12:57 -0800, "Cruiser"
    <Cruiser@discussions.microsoft.com> wrote:

    >Thank you for responding. I will try and explain what I am designing.... We
    >have a retail appliance business and right now we are using Sales
    >tickets/invoices that are filled out by hand. They require a large amount of
    >data (i.e.): sold to and deliver to customer name/addr and 4 phone numbers,
    >7 fields for model number, color, brand, etc. (ten lines of this type in case
    >10 products are purchased), we also have various fields for notes about the
    >purchase/delivery info, ticket status, dates, etc.
    >
    >Right now in designing this database to mimic our form,


    That's your first mistake.

    Designing table structures to fit a paper form is GUARANTEED to
    produce a non-normalized, faulty design, and that's what you're
    seeing.

    The table structures should be based on a logical analysis of the
    information to be stored: for example, a one to many relationship
    between Customers and Phone Numbers, ESPECIALLY one to many
    relationships between Customers and Orders, and between Orders and
    OrderDetails.

    You're using a relational database. Use it relationally.

    >essentially, we are
    >trying to just have a computerized copy of our tickets. I already have over
    >130 fields and I am not done yet. We DO NOT want a customer database, as we
    >need the sales ticket to say exactly how it was entered in, so if it was tied
    >to a customer database and their address is changed, it would change our
    >ticket.


    Not if you store the address in the Orders table (copying it from the
    Customer table). It all depends on how you decide what Attributes each
    table has - if the Address attribute is "the customer's address at the
    time of the sale, never mind if they changed it afterward" then it's
    an attribute of the Order entity, and needs to be stored in the Order
    table.

    >On the table "Orders" in my database, I have listed 10 lines for product,
    >each having their own fields (i.e.) Qty1, ModelNumber1, Color1.......Qty2,
    >ModelNumber2, Color2, etc. Some of these fields are a lookup field.


    So you're saying that if a customer purchases eleven items, you must
    store their name, address, phone numbers, etc. etc. redundantly in a
    second record?

    This is simply WRONG. You have a one (order) to many (items)
    relationship. Access can do this very easily if you model it as a one
    to many relationship; if you MISUSE Access by jamming the entire one
    to many relationship into one record, you will have trouble (as you
    are in fact having).

    >There
    >are various prices, some with tax and someout with tax, so the unit price and
    >extended price are not a calculated field, the salesman will input these
    >himself. Our staff is not very computer literate and the easier I can make
    >it for them, the better. Also, I have experience in databases, but am
    >learning Access as I go along. I think I have a pretty good handle on it.


    Ummm... you might want to do some reading about normalization, because
    I fear that you are on the wrong track.

    >I know this is not the normal way of designing a database and now after
    >learning of the limitations with only 2000 characters and 2GB of storage for
    >all, I am getting scared. We do a great deal of business and would like to
    >store all the records indefinitely.


    If 2 GB is limiting, consider using SQL/Server or another
    client/server database (Access makes an excellent frontend)... but use
    it relationally. If you insist on your current design, you would
    perhaps be just as well off to store each ticket in a Word document
    with a ten-line Word table for the detail items - what you're creating
    is NOT a relational database!

    >We would like to start using this database as soon as possible, I would
    >greatly appreciate any suggestions that you might have for me. Thanks in
    >advance.


    Read (or reread) some of the guidelines about relational databases
    listed at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

    and seriously consider using a normalized database structure... or
    abandoning relational databases altogether, if you don't want to use a
    normalized structure.

    John W. Vinson[MVP]
     
  6. Cruiser

    Cruiser
    Expand Collapse
    Guest

    John, Thank you for taking the time to help me. I realize that this
    database I am trying to make is not relational, but we really weren't going
    to do many queries on it, just use it for storage basically and occasionally
    look up something, which I think would be doable. I only wish I had known
    the capabilities of Access before I started this! I guess I just assumed
    that Access was more powerful and had tons of storage because it is a
    Microsoft product. Do you think if I continue on with my project, I could
    convert it to SQL with little or no problem? SQL sounds a little more
    stable, but I am still confused as to how it would work with Access. I am
    not a programmer, which doesn't help. if you know of a product that would
    probably do the job better, please let me know that as well. I have some
    experience with Paradox, but it has been a few years.

    Again thank you for any insight, and I appreciate your help.

    "John Vinson" wrote:

    > On Thu, 17 Nov 2005 07:12:57 -0800, "Cruiser"
    > <Cruiser@discussions.microsoft.com> wrote:
    >
    > >Thank you for responding. I will try and explain what I am designing.... We
    > >have a retail appliance business and right now we are using Sales
    > >tickets/invoices that are filled out by hand. They require a large amount of
    > >data (i.e.): sold to and deliver to customer name/addr and 4 phone numbers,
    > >7 fields for model number, color, brand, etc. (ten lines of this type in case
    > >10 products are purchased), we also have various fields for notes about the
    > >purchase/delivery info, ticket status, dates, etc.
    > >
    > >Right now in designing this database to mimic our form,

    >
    > That's your first mistake.
    >
    > Designing table structures to fit a paper form is GUARANTEED to
    > produce a non-normalized, faulty design, and that's what you're
    > seeing.
    >
    > The table structures should be based on a logical analysis of the
    > information to be stored: for example, a one to many relationship
    > between Customers and Phone Numbers, ESPECIALLY one to many
    > relationships between Customers and Orders, and between Orders and
    > OrderDetails.
    >
    > You're using a relational database. Use it relationally.
    >
    > >essentially, we are
    > >trying to just have a computerized copy of our tickets. I already have over
    > >130 fields and I am not done yet. We DO NOT want a customer database, as we
    > >need the sales ticket to say exactly how it was entered in, so if it was tied
    > >to a customer database and their address is changed, it would change our
    > >ticket.

    >
    > Not if you store the address in the Orders table (copying it from the
    > Customer table). It all depends on how you decide what Attributes each
    > table has - if the Address attribute is "the customer's address at the
    > time of the sale, never mind if they changed it afterward" then it's
    > an attribute of the Order entity, and needs to be stored in the Order
    > table.
    >
    > >On the table "Orders" in my database, I have listed 10 lines for product,
    > >each having their own fields (i.e.) Qty1, ModelNumber1, Color1.......Qty2,
    > >ModelNumber2, Color2, etc. Some of these fields are a lookup field.

    >
    > So you're saying that if a customer purchases eleven items, you must
    > store their name, address, phone numbers, etc. etc. redundantly in a
    > second record?
    >
    > This is simply WRONG. You have a one (order) to many (items)
    > relationship. Access can do this very easily if you model it as a one
    > to many relationship; if you MISUSE Access by jamming the entire one
    > to many relationship into one record, you will have trouble (as you
    > are in fact having).
    >
    > >There
    > >are various prices, some with tax and someout with tax, so the unit price and
    > >extended price are not a calculated field, the salesman will input these
    > >himself. Our staff is not very computer literate and the easier I can make
    > >it for them, the better. Also, I have experience in databases, but am
    > >learning Access as I go along. I think I have a pretty good handle on it.

    >
    > Ummm... you might want to do some reading about normalization, because
    > I fear that you are on the wrong track.
    >
    > >I know this is not the normal way of designing a database and now after
    > >learning of the limitations with only 2000 characters and 2GB of storage for
    > >all, I am getting scared. We do a great deal of business and would like to
    > >store all the records indefinitely.

    >
    > If 2 GB is limiting, consider using SQL/Server or another
    > client/server database (Access makes an excellent frontend)... but use
    > it relationally. If you insist on your current design, you would
    > perhaps be just as well off to store each ticket in a Word document
    > with a ten-line Word table for the detail items - what you're creating
    > is NOT a relational database!
    >
    > >We would like to start using this database as soon as possible, I would
    > >greatly appreciate any suggestions that you might have for me. Thanks in
    > >advance.

    >
    > Read (or reread) some of the guidelines about relational databases
    > listed at
    > http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    >
    > and seriously consider using a normalized database structure... or
    > abandoning relational databases altogether, if you don't want to use a
    > normalized structure.
    >
    > John W. Vinson[MVP]
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 18 Nov 2005 12:31:02 -0800, "Cruiser"
    <Cruiser@discussions.microsoft.com> wrote:

    >John, Thank you for taking the time to help me. I realize that this
    >database I am trying to make is not relational, but we really weren't going
    >to do many queries on it, just use it for storage basically and occasionally
    >look up something, which I think would be doable. I only wish I had known
    >the capabilities of Access before I started this! I guess I just assumed
    >that Access was more powerful and had tons of storage because it is a
    >Microsoft product. Do you think if I continue on with my project, I could
    >convert it to SQL with little or no problem? SQL sounds a little more
    >stable, but I am still confused as to how it would work with Access. I am
    >not a programmer, which doesn't help. if you know of a product that would
    >probably do the job better, please let me know that as well. I have some
    >experience with Paradox, but it has been a few years.


    Well, it depends on what you're trying to do. This is not a question
    of "stability"; it's a question of the right tool for the job. Your
    data appears to be perfectly well suited to a relational database
    solution, which could be implemented in Access, SQL/Server, Paradox,
    MySQL, Oracle,... lots of other programs. It appears, though, that you
    do not want a relational database solution; you want some sort of
    massive flat file, non-normalized database. You can get around the
    2000 byte limit by moving the data to SQL - which you can do for free,
    by installing the MSDE database engine from the SQL folder on your
    Office Pro or Access disk; but your data structure will still be badly
    denormalized. If you want to count the number of Size 87 Widgets sold,
    for instance, you'll have to look across ten (or perhaps twenty)
    fields, or build a really complex query.

    The relational data model has been in constant use and development for
    decades. It's a *very good way to handle information*, especially
    information of the type you describe. I can't really recommend another
    tool to do what you want - because I can't imagine actually WANTING to
    do what you're asking, given that there is a much more powerful,
    flexible, and well-developed system at hand.

    John W. Vinson[MVP]
     
  8. Cruiser

    Cruiser
    Expand Collapse
    Guest

    Thank you for all your input. You may see more of my posts if I venture into
    the SQL arena.

    "John Vinson" wrote:

    > On Fri, 18 Nov 2005 12:31:02 -0800, "Cruiser"
    > <Cruiser@discussions.microsoft.com> wrote:
    >
    > >John, Thank you for taking the time to help me. I realize that this
    > >database I am trying to make is not relational, but we really weren't going
    > >to do many queries on it, just use it for storage basically and occasionally
    > >look up something, which I think would be doable. I only wish I had known
    > >the capabilities of Access before I started this! I guess I just assumed
    > >that Access was more powerful and had tons of storage because it is a
    > >Microsoft product. Do you think if I continue on with my project, I could
    > >convert it to SQL with little or no problem? SQL sounds a little more
    > >stable, but I am still confused as to how it would work with Access. I am
    > >not a programmer, which doesn't help. if you know of a product that would
    > >probably do the job better, please let me know that as well. I have some
    > >experience with Paradox, but it has been a few years.

    >
    > Well, it depends on what you're trying to do. This is not a question
    > of "stability"; it's a question of the right tool for the job. Your
    > data appears to be perfectly well suited to a relational database
    > solution, which could be implemented in Access, SQL/Server, Paradox,
    > MySQL, Oracle,... lots of other programs. It appears, though, that you
    > do not want a relational database solution; you want some sort of
    > massive flat file, non-normalized database. You can get around the
    > 2000 byte limit by moving the data to SQL - which you can do for free,
    > by installing the MSDE database engine from the SQL folder on your
    > Office Pro or Access disk; but your data structure will still be badly
    > denormalized. If you want to count the number of Size 87 Widgets sold,
    > for instance, you'll have to look across ten (or perhaps twenty)
    > fields, or build a really complex query.
    >
    > The relational data model has been in constant use and development for
    > decades. It's a *very good way to handle information*, especially
    > information of the type you describe. I can't really recommend another
    > tool to do what you want - because I can't imagine actually WANTING to
    > do what you're asking, given that there is a much more powerful,
    > flexible, and well-developed system at hand.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page