Solved Workaround for 255 fields in a "table" limit (1 Viewer)

[jack555]

Understand tables can have a maximum of 255 fields. I have around 400 fields which all are unique and cannot be reduced by normalising.

How this limit can be overcome without affecting any queries or forms linked? Thanks in advance.

 

[arnelgp]

it will definitely affect the Query, 'coz same with table the limit is 255 columns?
you can make small tables that can be Linked through a common field.

400 fields?, are saving character per character on each field?

think of the table as Questionaire, each field is an item to answer.
does a for job application questionaire have 400 questions?
or applying for a license, housing loan, etc?

 

[NauticalGent]

have around 400 fields which all are unique and cannot be reduced by normalising.

Not to be contrary, but I find this highly doubtful. If you sincerely seek some assistance and/or advice on this matter, I would be interested in taking look. I'd have to admit I'm curious too.

 

[Uncle Gizmo]

I have around 400 fields which all are unique and cannot be reduced by normalising.

I don't believe that is possible, it is more likely that you have missed a trick.

See my blog "Excel in Access" for more information:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[jdraw]

I agree with the others that a properly structured database table with 400 fields is extremely rare and highly unlikely.
Please tell us about the proposed application and table(s) in simple, plain English so we understand your issue.

 

[alphonseg]

In addition to what others have stated, I would just like to point out the the number of fields is not the only limitation to consider. A record has a limit of 2k, excluding memo (long text) fields. So, you could have only 32 fields of short text, each filled with 64 chars each, and would hit the 2k limit.

 

[arnelgp]

2k limit.

it's 2G if i am correct?

 

[alphonseg]

2G is FILE size. RECORD size is 2k.
The_Doc_Man is correct. it is now 4000. I've been trying to post back, but keep getting the message 'Your content can not be submitted...', no matter what. So, I thought I would try editing my original post.

 

[arnelgp]

never heard of 2K record size limit.

 

[Uncle Gizmo]

never heard of 2K record size limit

The workaround for that is to import into a memo field, which seems completely illogical to me, but it seems to work.

 

[isladogs]

2G is FILE size. RECORD size is 2k.

I'd never heard of a record size limit and indeed couldn't make any sense of the concept.
I've rechecked the Access specifications and there's no mention of anything like that. See Access specifications - Access (microsoft.com)
There are items with a limit of 2048 (2k) including the number of characters in a label and in a validation rule as well as the number of open tables.
Can you see something else I've missed?

 

[The_Doc_Man]

I think the limit is 4K, not 2K, for record size. That is PROBABLY because Access uses a 4K buffer size for disk operations. I don't think it has a work area for ordinary records that COULD exceed 4K bytes.

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

It says the recordsize is 4000 bytes when Unicode compression is YES - AND it doesn't count OLE or Long Text fields. The reason is that a Long Text or Binary Large Object (BLOB) is stored separately from the main record and the only vestige of the big field/object is a pointer in the main record.

However, there are other issues to be considered. Queries have the same 255 field limit, so these tables' content cannot be managed monolithically by standard table actions and they cannot be managed monlithically by query actions. That would imply that there cannot be a query for sorting the whole table, nor can the whole table be UNIONed or JOINed. EVERYTHING would have to be done piecemeal, which opens the way for all sorts of normalization issues.

The way the U.S. Navy did this was to recognize that their large number of fields could be grouped into topical categories, not all of which applied equally to every sailor. So they grouped the fields and broke up the tables into at least three separate parts, no one of which ever exceeded any of the field-count limits. Then when they needed to correlate the parts, they used JOIN logic in queries to deal with the main table and one secondary table at a time, JOINed across the sailor's military service number. The trick was to assure that no two recordsets, when JOINed this way, also exceeded the 255-field limit.

However, this was done with an ORACLE database and a mainframe front-end database tool other than Access. Oh, they had limits, but they were higher than for Access. The principle might still apply but it would take a LOT of design work to break up the table into pieces-parts.

@jack555 - I mentioned the one case I ever encountered that really could not be normalized down below 255 fields - but like the others in the forum, I find it absolutely unlikely that a table could legitimately have 400 fields that cannot be normalized to a more tractable structure. Normally I would ask to see the database - but you cannot do that because you cannot possibly have built it. Access would have stopped you. Do you have a list of fields that you were proposing?

 

[BananaRepublic]

A possible workaround could be to store all the values as an array within a LongText field and use subscripting to read/write each data element within the array, or simply store each data element as a record within a table. If that's not feasible, then use a db server like SQL-Server which supports larger capacities, UDF's, collections aka MVF's, etc.

 

[The_Doc_Man]

@BananaRepublic and @jack555

Unfortunately, a Long Text field is unstructured TEXT, not some structured object container. An array, to be used as such, requires an Array Descriptor structure, which you will not be able to store in a LongText field. (You cannot put an object in a text field. Wrong data type.) Therefore, that is a non-starter. However, if there is anything in this long table for which an array would be suitable, it is possible that the array contents would be equally suitable as a child table, which would eliminate the need for exceeding Access limits.

Moving the back end to SQL server doesn't solve the problem of how to use the oversized table. You would need to develop a web app (probably) to handle that data size, because Access as a front-end would still be unable to use/manage a table with more than 255 fields.

I'm thinking that the Entity-Attribute-Value structure might be a way to deal with this.

Normalization / Approach question – arbitrary characteristics (ETA: Entity Attribute Value EAV)

Greetings Access Wizards – been many years since I used Access, started to seem like the right tool again LAST February but then… well, you know, and my work shifted radically. Now things are getting back on track at my desk, so I’m again trying to scrape the rust off my brain. Which leads me to...

www.access-programmers.co.uk

User Interface for Custom Properties

In my db, ive created a feature I'll call Custom Properties. This feature is intended to allow additional User Defined information to be captured about a particular entity and then the value(s) assigned to these properties to be used at decision points in processing logic. An individual...

www.access-programmers.co.uk

Split table to multiple 1:1 relationships?

Hi! I would be very grateful for your help regarding my issue: I have Expense Tracking Database. Main Table is list of Expenses with value of transactions: tblExpenses ExpenseID (PK) CategoryID (FK) Note (text) Value (Currency) 1 1 Bla bla bla -256 USD For a lot of expenses in this...

www.access-programmers.co.uk

Without more detail on these putative 400 fields, I can't tell whether another approach is possible.

 

[arnelgp]

So, you could have only 32 fields of short text, each filled with 64 chars each, and would hit the 2k limit.

I don't know the source of this myth.
here is a table with 255 fields (254 of them are Long text).
Each Long text fields are then appended with 64K characters.
nothing happens.

 

[Galaxiom]

I don't know the source of this myth.
here is a table with 255 fields (254 of them are Long text).

Long text is not included in the record length limit because it is stored in the BLOB.

 

[The_Doc_Man]

The published limits for Access say that the 4 Kb record-size limit excludes OLE objects and LongText fields. That same exclusion is why you cannot build an index in a LongText field. It isn't stored with the rest of the record.

 

[arnelgp]

oh,, so it is true!
on my test it is not 4K, more or less 2K (as in post #6), Table2 on the db.
the the Fields (short text, T1 to T8) is already 1863 characters, 1.8193359375K. (1863 / 1024).
plus the 1 autonumber field.

so for full 255 characters + 1 autonumber field, you can only have 9 Fields.
note that T8 is not full (78 characters only). see mTest() in Module1.

apology to mr.alphonseg.

 

[BananaRepublic]

SQL-Server has a limit of 1K columns per table, so why would it not be a viable alternative?
docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

 

[jack555]

Thank you everyone for the answers.

As all understood, it's like a questionnaire data filled by someone. The whole history of one person/customer from the first date of visit, purpose, the outcome of the meeting and subsequent events with more fine information.

As suggested I will try to break it down into multiple tables with the unique linking identifier.