Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Error 3190 "Too Many fields defined"

    Hi,

    I am using ACCESS 97 at work place. And i need to add some columns to the database.

    When i add columns to the empty database which is having 0 rows, i am able to add columns to the table.But i am not able to add columns to the table which is having around 260 rows and database size is 2900 KB only.

    I am wondering why access database is not allowing to add columns if there is some data in the table. Currently the table is having only 236 columns and i am trying to add 5 columns but it is throwing the error 3190. And as mentioned earlier, if table is empty i am able to columns.

    Any help can be appreciated.

    Thanks
    Raju

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is a finite limit to the number of columns in any one tabel or query.. its around 250ish.
    mind you apart form one b awkward app I've never had to have that nube rof columns in a query

    theres limits in many bits of software usually the desinger couldn't envisage anyone getting near that limit.

    so there are several options
    1) dump the other columns into another table and have a one to one ri link between the two tables
    2) revist your tabel design... do you really need the 241 columns you are proposing, is this limit beng blown because the design is flawed
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    "only 236 columns". ... ..

    Agreed with healdem
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2008
    Posts
    2
    Quote Originally Posted by healdem
    there is a finite limit to the number of columns in any one tabel or query.. its around 250ish.
    mind you apart form one b awkward app I've never had to have that nube rof columns in a query

    theres limits in many bits of software usually the desinger couldn't envisage anyone getting near that limit.

    so there are several options
    1) dump the other columns into another table and have a one to one ri link between the two tables
    2) revist your tabel design... do you really need the 241 columns you are proposing, is this limit beng blown because the design is flawed

    Thanks for the Information. I cannot change the database design now and this database is critical for us. Does you have any other alternate approaches to rebuild the database. I have tried to rebuild by taking new database and importing all the columns but is not working correctly.

    Let me know if there are any other approaches to rebuild the database.

    Thanks in advance.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why can't you change the design?

    If your import method worked, wouldn't you just be re-creating the same problematic design?

    I think healdem is suggesting that you look at rebuilding the database professionally, from the ground up... MANUALLY.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you cannot change the design AND you need 241 columns then frankly you are stuffed. There is no magic bullet here. its possible that you may be able to stuff a few more columns in the table by possibly reducing the number of indexes. I can't think why a tbale design shold collapse at 241 items... its not a magic number (256 is.. being 8 bit binary ) so its possible that the limit is 256 objects within a table. I don't know if thats the case but it may be worth a try. if you MUST persist with your current design.

    the least painful resolution toyour problme is to stuff some columns into another table and do one to one join using the same PK.

    In all the years Ive been doing db work Ive only ever once seen a table this big and that was the result of poor physical design. Im not saying you design is or isn't poor, after all we've not seen enough details (well any details).

    Generally when you hit a stumbling block such as this its you that has to change not the development environment.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed... and for the record, the limit is 255 fields but I also think that each index counts as a field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    260 rows and almost as many columns? My money is on a major first normal form violation.

    OP - please can you post some (please not all!) of the structure of this table including the names of these six new columns?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •