Field count limit >255 in Access > 2003>

[Rich]

I know folks on the board like to think that 255 is 'absurdly huge' for a
field count limit, but that just isn't true for large domains of data. In
this case a Z/OS systems database. An example...
NAME COLNO
------------------++------
SQA_SP245_B16M_MIN 255
SQA_SP245_B16M_AVG 256
SQA_SP245_B16M_MAX 257
CSA_SIZE_A16M 258
CSA_SIZE_B16M 259
SQA_SIZE_A16M 260
SQA_SIZE_B16M 261

Table of course could be broken up by sub-pool (SP*), but that isn't
normalization, that's just concession. there are plenty more examples. In
this case the database is Tivoli Decision Support.

Does the limit on field count get any greater on levels above 2003? I'd
hate to think about putting a bunch of views in the catalog tables to support
a weakness in Access.

Regards,

Rich

 

[George Nicholson]

Does the limit on field count get any greater on levels above 2003?

Nope.

 

[Roger Carlson]

No, the field restriction is not any higher it later versions.

This really isn't a weakness in Access. Access is designed as a relational
database and the data example you provided is not normalized. There's
nothing wrong with that if you have a Data Mart of some kind. Mostly data
warehouses and data marts use a Dimensional Model which are often much
wider. But Access isn't really design as a data mart tool. Its forte is
relational databases.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jerry Whittle]

Oracle 10g allows up to 1000 columns per table.

MSDE 1024 columns per table

SQL Server 1024 columns per table

MySQL 4096 columns per table

 

[Albert D. Kallal]

As mentioned you could break the data out into a related table, and as
content of the date is not likely normalized.

However if you move up and use SQL server, then the limit is 1024.

And if you use olap tools (data cube/warehousing) available on the higher
editions of SQL server, then you can certainly effectively deal with these
"longer" types of data tables. However this is just one area that at MS
access is not designed for.

From a human point of view, trying to deal with that many columns of data is
not much workable anyway unless you have a set of tools that can analyze the
data warehouse information in a meaningful way.

Excel has it increased its limits, and perahps you consider that...

 

[a a r o n . k e m p f]

I disagree.

You don't need to normalize.
You need to move to ADP and then SQL Server entirely.

The 255 column limit was probably 80% of why I moved to ADP a decade
ago.
I just love it out here, in SQL Server land.

-Aaron

 

[a a r o n . k e m p f]

bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?
bull shit it's not a weakness in Access. where do you get off on
saying crap like that?

-Aaron

 

[a a r o n . k e m p f]

Again- maybe if you moved to ADP; then maybe- just maybe-- you
wouldn't have to make your customers choose between light cheap and
strong.

-Aaron

 

[Albert D. Kallal]

If ADP projects allow you to deal with, and return more than 255 columns in
a table, then this is certainly a good reason to use ADP's.

Are you telling me that the query designer and tables can return and use
more then 255 columns?

Does a report writer allow this also?

 

[Rick Brandt]

Oracle 10g allows up to 1000 columns per table.

MSDE 1024 columns per table

SQL Server 1024 columns per table

MySQL 4096 columns per table

I think the latest version of UDB400 (the engine on an IBM ISeries) is a
whopping 8000 columns.

 

[Tony Toews [MVP]]

I know folks on the board like to think that 255 is 'absurdly huge' for a
field count limit, but that just isn't true for large domains of data. In
this case a Z/OS systems database. An example...
NAME COLNO
------------------++------
SQA_SP245_B16M_MIN 255
SQA_SP245_B16M_AVG 256
SQA_SP245_B16M_MAX 257
CSA_SIZE_A16M 258
CSA_SIZE_B16M 259
SQA_SIZE_A16M 260
SQA_SIZE_B16M 261

Table of course could be broken up by sub-pool (SP*), but that isn't
normalization, that's just concession. there are plenty more examples. In
this case the database is Tivoli Decision Support.

What does Z/OS, SP245, CSA, SQA, A16M and B16M mean? Without that
information we can't decide what those field names mean.

That said I'd be highly suspicious that those tables are indeed
properly normalized.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

NAME COLNO
------------------++------
SQA_SP245_B16M_MIN 255
SQA_SP245_B16M_AVG 256
SQA_SP245_B16M_MAX 257
CSA_SIZE_A16M 258
CSA_SIZE_B16M 259
SQA_SIZE_A16M 260
SQA_SIZE_B16M 261

Table of course could be broken up by sub-pool (SP*), but that
isn't normalization, that's just concession.

No, that *is* normalization.

The table is really badly designed, as are so many that I've seen
underlying commercial apps. The capability of other db engines to
support massively denormalized tables is really not something that
I'd consider a good thing -- if people couldn't do it, maybe data
would be designed better and aps would run more efficiently.