Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    Can there be too many columns in an Access database? I have a database in which there is a linked SQL table that has 137 columns (THAT I DESPERATELY WANT TO BREAK OUT INTO SEPARATE TABLES). I am beginning to get a conflict wherein updates to the table in the records that have most of the fields populated it says that another user has updated the record at the same time that I updated it. Would I like to copy to the clipboard, or discard changes?

    That's my sad tale. Any ideas?

    thankx much.

    jd

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    I don't think the number of columns would cause that "already updated" message. It should mean that from the time you first read the record until you updated it, someone else updated it.

    And you are right, you should break that record into separate records. I have trouble believing a record with 137 fields is properly normalized!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    137 columns in an SQL 'table' doesn't necessarily mean 137 fields.
    You're probably viewing a query which has many calculated fields based on the actual data.
    If you do have 137 fields in 1 table, I'm sorry. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    There is an upper limit on Access fields (columns) - for 2000 and for 97 (I believe, but can't check it here) it is 255, so that shouldn't be a problem. You don't indicate the version of SQL you are working with, but if it is 6.5 or 7, there is a limit of about 2000 characters in a single record. A record must fit on a page (unless you are using memo or OLE object fields - they are stored on a chain of separate pages), and the page size is 2048 bytes. In SQL 2000 this goes up to 8096, so you have a bit more leeway. Splitting your tables is the best way to resolve these kind of issues. I'm assuming that you are using ODBC linked SQL tables. If any of my assumptions are incorrect, please post and let me know what the real situation is and I'll try to help further.
    Wendell

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    JM,

    I have to develop on the Server and on the front-end. It has 137 fields. No, it is not properly normalized, but I'm doing battle with , er I mean development for, a bunch of people that are accustomed to a Mainfram flat file, so I have to keep them as happy as possible. I knew the day would come when I would have (read "get") to normalize to some extent. I just want to have my facts before I embark on a major project.

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    Wendell,

    Thanks. It's nice to have some numbers to throw around when I have to justify what I'm going to do. I did, however, trash my test data and re-append from production. It appears that some of the records I was using were corrupt. As an example of a corruption, if a bit field (yes/no) on the server does not have a default value, it can be left as null-which is impossible. This will cause the same error as mentioned in my first post here. thanks much.

    jd

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    You are correct - SQL Server (and presumably others) do allow a null for bit columns unless the designer says it isn't allowed, and Jet doesn't like that much. Is it possible that they are giving you a view rather than letting you see the table directly. That could explain the 137 fields, but it's possible that they still think SQL Server is a flat file database too! Good luck.
    Wendell

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    Nope W, it's a table. It's a transaction database, not just reporting. I am responsible for back-end and front-end development (i.e. creating triggers, DTS packages, stored procedures, etc adding fields/tables to the server-in a test area). Essentially, I have taken over as developer for the DBA. He orginally built the db, now he just maintains the server, and it's functionality (like that is not enough). Anyway, thanks much for the input.

    jd

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Can there be too many columns (Access 97 SR2 on NT 4 or Win95)

    You are right. There is a limit of 255 fields in an Access97 table, and the recordsize is limited to 2000 characters (excluding memo and OLE object fields).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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