Solved Workaround for 255 fields in a "table" limit

[MajP]

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

All questionnaire databases I have built or seen (and that is a whole lot) do not have questions as fields, they are built (as previously mentioned) as Entity Attribute Value models. NO one is ever going to build a 400 field table even if you could. That would be a total PITA, it would be impossible to manage, update, and highly inefficient.

Basically it is a couple of fields. Take a look at this design. Very efficient and extremely flexible.

At Your Survey

At Your Survey (AYS) is a full featured application that allows users to create their own surveys...

www.rogersaccesslibrary.com

You can argue if an EAV is normal or not, but sometimes there is a tradeoff of normality vs practicality.

 

[alphonseg]

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.

Not a myth. As mentioned by others, long text fields are not included in the limit.

 

[The_Doc_Man]

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

The question isn't whether SQL server could do it. It certainly could. The question is whether Access could still be used as a front-end. Using a large number of views to subdivide the big table into (slightly overlapping) chunks, you might be able to make it work, but it would not be pleasant. If Jack555 wants to use a non-Access FE (such as a web connection) then sure, that works. But since this IS an Access forum, I chose to answer as I did.

 

[BananaRepublic]

The question is whether Access could still be used as a front-end.

The maximum number of fields in an Access recordsource is 255, so that limit applies to Access queries, forms and reports. An Access frontend would not be a viable solution unless the OP uses the suggested Entity Attribute Value design. Creating a column for each answer is not a good design.

 

[Isaac]

I've recently run into this limitation, and am reminded that wide tables aren't always the result of poor design. Sometimes we have to ingest source/vendor/external data files that are huge .... The one I am dealing with now is >290 columns. Even though I will be transforming this data in subsequent steps, there are still early/initial raw upload steps where a wide table is helpful to use.

Of course I solved for this by convincing the client to use SQL Server, and simply not using Access in any scenario where it needed to represent >255 columns in a Table, Linked Table, Query, etc.

Fortunately, in my client's case, the end-goal is to produce a file (for upload to another vendor) which has about 100 columns on it.
However, this is another example of a potential legitimate requirement that does not suggest poor design: A vendor might require a file (and thus, a query) with >255 columns. Again, the solution would be to limit Access's involvement, and use other tools ...

Sometimes these things come up when dealing with external sources or destinations and cannot simply be wished away.

 

[Isaac]

The question isn't whether SQL server could do it. It certainly could. The question is whether Access could still be used as a front-end. Using a large number of views to subdivide the big table into (slightly overlapping) chunks, you might be able to make it work, but it would not be pleasant. If Jack555 wants to use a non-Access FE (such as a web connection) then sure, that works. But since this IS an Access forum, I chose to answer as I did.

At a major bank I worked at previously, this was common - the SQL Server team would hand off Views for consumption by people who exclusively used Access to connect to the data. Each view would have <255 columns so it all worked out. Even though the source tables were wider than that - they had been intentionally denormalized for datamart/reporting purposes.

 

[jack555]

Awhile back, possibly a couple of years ago I was working on a utility to import large comma separated value files (CSV) with over 255 columns. I'd be interested in resurrecting this project and using it in a real-world situation like this one if you're interested.

I also have a utility for converting spreadsheet style data into a format more suited to MS Access. Again, I would be happy to run through this with you.

As suggested earlier, I have split into multiple tables with linking. it works at the moment. if running into difficulty, I will return back to you. thank you very much for the support.

 

[NauticalGent]

be a blog about a failure not a success.

Sometimes knowing what NOT to do is just as helpful as knowing what to do.

So...you do you and try not to get too deep in doo-doo.

 

[Galaxiom]

SQL-Server has a limit of 1K columns per table, so why would it not be a viable alternative?

Access can't handle linked tables with more than 255 fields.

BTW SQL Server can handle 30,000 columns if they are Sparse. Terrifying thought.

 

[Isaac]

SQL Server can handle 30,000 columns if they are Sparse. Terrifying thought

Would love to see a project manager schedule handling that mapping exercise.
Then again, some of those PM's are pretty impressive!