Access 2007 Specifications

[Kerry]

Hi all,

I currently use Access 2003, but I have previously looked at a beta
version of Office 2007. In the Beta version I was able to create
tables with more than 255 columns. This is a feature I am
particularly interested in. I just noticed that according to
Microsoft http://office.microsoft.com/en-us/access/HA100307391033.aspx
the table limit in 2007 is 255 columns. Can someone who has 2007 let
me know if you can have more than 255 columns? I am also interested
in if there is a 2GB file size limit in Access 2007. Does anyone
know?

Thanks

Kerry

 

[Gina Whipp]

I have Access 2007 and 255 is the max columns and the 2 gig is the max size.

 

[Scott McDaniel]

Hi all,

I currently use Access 2003, but I have previously looked at a beta
version of Office 2007. In the Beta version I was able to create
tables with more than 255 columns. This is a feature I am
particularly interested in. I just noticed that according to
Microsoft http://office.microsoft.com/en-us/access/HA100307391033.aspx
the table limit in 2007 is 255 columns. Can someone who has 2007 let
me know if you can have more than 255 columns? I am also interested
in if there is a 2GB file size limit in Access 2007. Does anyone
know?

Here's the specs:
http://office.microsoft.com/en-us/access/HA100307391033.aspx

And I hafta ask: why in the world would you ever have a table with more than 255 columns ... for that matter, why would
you ever even get close to 255? I've built financial databases, R&D database, lab spec databases, etc etc ...and have
never found the need for more than 50 columns (and then only very, very rarely).

Thanks

Kerry

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[George Nicholson]

As I recall, both of those are limitations of the Jet engine (which Access
utilizes), not Access directly.

Jet hasn't been upgraded for 2007, so those limitations remain.

Of course, you could build an Access front-end that connects to a SQL Server
backend, rather than a Jet mdb file, and the 2gig limitation goes away. You
don't need 2007 to do that.

HTH,

 

[Kerry]

In the company I work at (Survey market research), we have a table in
the database which has respondent data for surveys. It has over 255
columns. It has columns for demographic information, as well as the
survey questions. I do not have privelages to change the database
structure, so I can not use this table in Access.

Sometimes people request information from me, and they want more than
255 columns of data. Generally, no single table they want information
from has more than 255 columns, but the final data they want does.

For both these situations I need to use a different application for at
least some of the request, but it would be easier if it could all be
done in Access.

Is it just me, or is it ridiculous to have Excell 2007 have a
capability of 16,384 columns, and Access 2007 only 255?

 

[Gina Whipp]

Just my opinion...

APPLES: Excel = flat file
ORANGES: Access = relational database design

(On a side note, I wouldn't want to manage my Orders in Excel and the one
and only survey database I created, well it never entered my mind to do it
in Excel.) HOWEVER, whichever I choose would depend on what I need/want my
data to do. I have never in 10 years created a table that came anywhere
close to needing 255 fields. It always surprised me that in a relational
database you got 255 fields!

Some articles on the subject:
http://office.microsoft.com/en-us/help/HA010429181033.aspx
http://www.workplacelife.com/2006/05/09/access-vs-excel-when-to-use-excel/

 

[DAVID]

"Access" was so named because it gave you "Access"
to your data. And, for what it's worth, as soon
as I saw the rhetorical question
"why ... would you ... have ... more than 255 columns"
I immediately thought "to link to a spreadsheet".

In spite of the money spent on the new version,
it's hard to say what MS has in mind for Access.

It's definitely gone down market over the years,
as Access has lost functionality, and SQL Server
has gained functionality.

If you want to persevere, you need to link to
a range in the spreadsheet. Then you can import
the data, then re-assemble it correctly into
related tables. It won't be worth working
directly with the linked data unless it has
repeated ID columns you can use as the primary
key.

The way to do export is to use code to write
CSV files.

Neither of which is a satisfactory solution.

(david)

 

[AnandaSim]

Jet hasn't been upgraded for 2007, so those limitations remain.

Are you sure Jet has not been upgraded to 2007? I thought it had -
it's now called Access Database Engine and has rich text field,
attachment field. The Access team spent a lot of time on Sharepoint
integration of data, not expanding the table columns spec - that
wasn't the focus of their efforts.

Returning to the original poster's question, maybe you could use one
to one related multiple tables and some programming to smooth over the
bumps that technique creates.

Regards

Ananda

 

[Kerry]

Hi Ananda,

The table in the database at my company which has over 255 columns is
poorly designed, and the primary keys are after the 255th column, so
if I link to that table in Access, I can't even see the primary keys.
The table is in an Oracle database. Is there a way in Access to link
to an Oracle table and choose what columns show in the linked table if
the Oracle table has more than 255?

 

[Douglas J. Steele]

If I recall correctly, I was able to link to tables that had over 255
fields, and then create queries on those linked tables, ensuring that the
queries had fewer than 255 fields.

The other, probably better, option would be to create views in Oracle and
then link to the views, rather than the tables.

 

[David W. Fenton]

Is it just me, or is it ridiculous to have Excell 2007 have a
capability of 16,384 columns, and Access 2007 only 255?

No, not at all. Excel is a spreadsheet, and Access/Jet a
*relational* database. There is no properly defined entity that
truly has 255 attributes. There are few that have more than 50.

If your data is properly normalized, the 255 limit is more than
enough.

If it's not, you're using your database like a spreadsheet, which is
by definition going to be denormalized (it can't easily relate data
in separate worksheets).

 

[David W. Fenton]

Jet hasn't been upgraded for 2007, so those limitations remain.

This is patently false.

Access 2007 was the release of a new fork of the Jet database
engine, the ACE, with the new file format using the ACCDB extension.

Jet 4 is the legacy version, and it is currently maintained by the
Windows development group, because it is bundled in all current
versions of Windows because Jet 4 is used for the data store for
Active Directory.

The new file format in A2K7 could have been called Jet 5 (or 4.5),
but the Access team chose to identify the new db engine more closely
with Access itself.

The new database engine is backwardly compatible with legacy Jet
data (as have been all versions of Jet, actually).

Jet is alive and well and looks to me to have a long life expectancy
(as long as Access exists).

 

[David W. Fenton]

The other, probably better, option would be to create views in
Oracle and then link to the views, rather than the tables.

Seems to me that a competent DBA would already have done this,
having already recognized the horrid design and all the problems it
would cause with client applications.

 

[Rick Brandt]

No, not at all. Excel is a spreadsheet, and Access/Jet a
*relational* database. There is no properly defined entity that
truly has 255 attributes. There are few that have more than 50.

If your data is properly normalized, the 255 limit is more than
enough.

If it's not, you're using your database like a spreadsheet, which is
by definition going to be denormalized (it can't easily relate data
in separate worksheets).

While all of that is true, SQL Server 2005 supports 1024 columns per table
and the database engine on an IBM ISeries (UDB400) supports a whopping 8000
columns in a table. So this specification in Access does become an issue if
you are trying to work with external engines with higher column limits.
While 255 is a ridiculously high limit on table columns, it would be nice on
occassion if the same limit was not applied to queries and externally linked
(ODBC) tables.

 

[Douglas J. Steele]

While all of that is true, SQL Server 2005 supports 1024 columns per table
and the database engine on an IBM ISeries (UDB400) supports a whopping
8000 columns in a table. So this specification in Access does become an
issue if you are trying to work with external engines with higher column
limits. While 255 is a ridiculously high limit on table columns, it would
be nice on occassion if the same limit was not applied to queries and
externally linked (ODBC) tables.

OTOH, database servers are appropriate for "data warehouses", where the data
is deliberately denormalized.

I'm not sure Access would be an appropriate choice for a data warehouse.

 

[Rick Brandt]

OTOH, database servers are appropriate for "data warehouses", where the data
is deliberately denormalized.

I'm not sure Access would be an appropriate choice for a data warehouse.

But is it not an appropriate choice to query and/or report against a warehouse?
That was my point. That in a warehouse you might very well have an
intentionally de-normalized view or table for reporting that exceeds 255 columns
and Access will not be able to work with it.

Additionally one needs to realize that not all data resides in "databases" and
that "relational" is not the only kind of legitimate database design.

 

[David W. Fenton]

While all of that is true, SQL Server 2005 supports 1024 columns
per table and the database engine on an IBM ISeries (UDB400)
supports a whopping 8000 columns in a table. So this
specification in Access does become an issue if you are trying to
work with external engines with higher column limits. While 255 is
a ridiculously high limit on table columns, it would be nice on
occassion if the same limit was not applied to queries and
externally linked (ODBC) tables.

For queries, yes, it would be helpful when one needs to denormalize
(web presentation often requires it in order to keep the number of
db connections to a minimum).

But for tables, never -- it just makes no sense at all, except to
allow people to create monstrosities that should never exist.

 

[David W. Fenton]

Additionally one needs to realize that not all data resides in
"databases" and that "relational" is not the only kind of
legitimate database design.

Huh? While I agree with you that it would be worthwhile to be able
to query those external tables and also be able to have more than
255 fields in a query, I don't see that any of this is an argument
for changing the design of Jet itself to allow more than 255 fields.
It is, after all, a relational database.

And that was, after all, what was being asked for by the OP.

 

[George Nicholson]

But for tables, never -- it just makes no sense at all, except to

allow people to create monstrosities that should never exist.

....but if we prevent people from making monstrosities, how will I feed my
family?

 

[AnandaSim]

I'm not in a position to test because I don't had an Oracle DB to play
with, but I remember there are several ways to link external tables.

One is the obvious way of File > Get External Data

Another is to store and pass an Oracle SQL string - start a new blank
Query, then go to the Query menu item, choose SQL Specific >
Passthrough

Similar is to start a new blank Query, display the Query Properties
dialog and figure out the Connect String.

If Access won'l display 255 fields in onw query, you can use more than
one query and type in the names of each field that you want.

HTH
Ananda