locked
Number of columns per table limit reached RRS feed

  • Question

  • Hi

    I have reached the limit of columns in one of my tables. How can I add more columns to this entity? Can I for example use a second table and do a 1 to 1 link with the first table?

    Any advise would be appreciated.

    Thanks

    Regards

    Tuesday, March 10, 2015 1:27 PM

Answers

  • Yes you can do that provided you can keep the primary keys matched, but any time someone reaches the column limit it is usually because they have created a "spreadsheet" instead of a relational database. You might want to read up on Normal form in relational databases before you continue.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, March 10, 2015 2:46 PM

All replies

  • Yes you can do that provided you can keep the primary keys matched, but any time someone reaches the column limit it is usually because they have created a "spreadsheet" instead of a relational database. You might want to read up on Normal form in relational databases before you continue.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, March 10, 2015 2:46 PM
  • Hi

    Thanks.

    Its just a Staff table and we need a lot of info on the staff. 

    Regards

    Tuesday, March 10, 2015 3:12 PM
  • You will save yourself a lot of headaches down the road if you follow Bill's advice and re-think your design. Even if you use multiple tables, what would you then do with that? Queries have the same 255 field limitation. Tables or queries are used as the source for forms and reports, so you won't be able display or edit the data.
    Tuesday, March 10, 2015 3:55 PM
  • It sounds like you want to record over 255 different pieces of information (>255 fields) on each employee. Is that correct? If that is correct, then you need to redesign. You may confusing what a table record is versus what a table field is.

    Tuesday, March 10, 2015 5:43 PM
  • Its just a Staff table and we need a lot of info on the staff. 

    You need to break info down into categories and use related tables for the information instead of in a single record for individual staff member.

    If the info was on certifications then a table that has the employee ID and a separate RECORD (not field) for each certification.

    ADDED --

    Then you would use a form/subform to view individual/certifications and another for certification/individuals.


    Build a little, test a little


    Wednesday, March 11, 2015 4:53 PM
  • Can you show us the first 100 fields?

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, March 12, 2015 2:08 AM
  • I can't think of a reason you would need more than 255 columns in Access.  As others have stated, you need to rethink your schema.  In SQL Server, you can have way, way, way more than that, if that's what you really want.

    https://msdn.microsoft.com/en-us/library/ms143432.aspx?f=255&MSPPError=-2147217396


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 13, 2015 2:55 AM
  • True, SQL Server allows more than 255 fields, but the 255 limit would still apply to queries, etc., if using Access as the FE.

    Additionally remember that there is also a record size limit in both Access (2k) and SQL Server (8k).

    That means that if you had 255 fields in Access containing 8 bytes each, you would be at the limit. So, there is another reason for not having so many fields in one table.

    Friday, March 13, 2015 10:24 AM