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

[NauticalGent]

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

I think you just might surprise and impress yourself!

 

[CJ_London]

Sound like you need 3 or 4 tables

customers
Meetings
Perhaps tables for meeting types/locations etc

 

[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.

 

[Uncle Gizmo]

Not a myth.

ArnelGP has already apologized:-

apology to mr.alphonseg.

 

[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.

 

[Uncle Gizmo]

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

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.

 

[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.

 

[Uncle Gizmo]

I found the Thread where I was struggling with this and posted for help on AWF:-

Joining large tables together

I have a small problem. I keep running into error >>>> Record is too large. (Error 3047)

www.access-programmers.co.uk

I have copious notes on my about it in my Google Drive and I may turn it into a Blog one day, not sure because I am still smarting that I did not get it working as I wanted. In other words it would be be a blog about a failure not a success.

 

[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.

 

[Uncle Gizmo]

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

Reading back through my notes on this problem with the comma separated value import, I spotted this note I made where I must have mentioned it to my mum!

What I said to mum about this job!​

I decided to make a new product with MS Access. It’s not going well… I keep getting error messages. You know it's just something small, but it nearly drove me mad! It's taken me 2 days to do half a day's work.

I keep getting problems with it and it drives you nuts. You know when you start doing something, it goes wrong. You keep going. By the time you get into the second day, you’re thinking … I should stop this, forget it, it’s a bad job. But you don't, you keep going just to beat it. I've had a couple of days like that, so I'm sitting here really regretting it. You know it's a waste of time but still in some ways it is not a waste of time you learn something!

 

[Pat Hartman]

 

[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!

 

[Pat Hartman]

If the app really is a survey, what are we talking about. Each question should be a row. That is the best and most flexible solution.