Enhance Union-Query architecture that total field count more over 255
Please re-design that a Union query of MS Access deal with more over 255 source fields totaly like 1024 or 65535 fields.
Marc E. commented
@Dolly and Joe: I understand Yagi’s request whereas I believe it’s not for union queries but in general for the 255-field limitation in tables and queries plus in imports and exports.
Especially in the finance industry you have many property sheets of financial instruments which easily contain over 500 fields. Nowadays having tables with more than 255 fields is no longer an exception. For Example, Bloomberg Datalicence feeds contain up to 500 fields (csv-line format) and you cannot import them anymore in Access easily but have to write VBA readers instead. Also, you can’t even store the data in one single table for further analysis but need to split it up. Then, you can’t display and export the result of all these data fields in a single query because the fields in queries are limited as well. There are numerous applications whose interfaces import files that contain more than 255 fields. All those files can’t easily be generated out of Access and you have to use multiple queries not exceeding 255 fields and merge them together with VBA in order to generate your export file.
Each and every time somebody raises the 255 limitation, I hear people providing workarounds using pass-thru queries or simply consider that every table having more than 255 fields relates to a poor database design. So what? If you buy professional software and those guys implemented tables with more than 255 fields (maybe because there is no other way and splitting data made no sense because it’s a single and unique record) why should we have to use all these workarounds and not simply be able to work with this number of fields easily? The MS Access users are the guys that need to access the data as easily as possible and be sure that data consistency is guaranteed. However, if you don’t know if the table you try to access has less than 255 fields or more your result will be inconsistent.
Isn’t MS Access called "Access" because it should be able to access and work with data from any data source? Unfortunately, this is no longer the case because Access did not evolve much compared to Excel since the 90s. Why do we have nearly endless columns and rows in Excel now compared to Access? MS Access should be at least as powerful than Excel regarding the amount of data fields (or columns). If you consider that Access should have not more than 255 fields why would Excel need more?
Patrick Honorez commented
Since this amount is over the number of fields allowed in an Access table, I suppose you want to use this with Oracle or SQl Server. In that case, you can use a passthru query, which will let you overcome that limit.
I've never come remotely close to even 255. As already noted ... please give a real life example.
[Deleted User] commented
Could you give a real-life scenario when you need more than 255 fields? I find this very odd and would love to understand the request.