Re: Increase Access Table Size to Mirror Excel

[Bill Mosca, MS Access MVP]

Steve

There really isn't a need to increase the number of columns in an Access
table. If you need more columns you can just create another table and make
it a one-to-one relationship.

By exporting the Excel sheet to a text file it would be a simple matter to
import all columns to their various tables using import specs.

The upside to using import specs is that you have much more control over
data types and column ordering.

 

[Bill Mosca, MS Access MVP]

Kenny

While it may be true for most Access databases, it really isn't unusual to
have a boatload of columns in a table. I deal with tables in SQL Server
databases for enterprise solutions that have hundreds of columns. I believe
the max is 1024.

It's not a matter of poor design. If a record has that many elements then
there is no reason to split them up across multiple tables.

 

[Rick Brandt]

Kenny

While it may be true for most Access databases, it really isn't
unusual to have a boatload of columns in a table. I deal with tables
in SQL Server databases for enterprise solutions that have hundreds
of columns. I believe the max is 1024.

It's not a matter of poor design. If a record has that many elements
then there is no reason to split them up across multiple tables.

While in theory a proper database model might exist that would require
hundreds of fields in one table I have personally never seen one. Taking a
default position that such tables are likely poorly set up is a pretty safe
bet.

 

[BruceM]

Still, "you need to take a class if you have 50 columns" and "you must be
crazy if you need 200 columns" is rather more uncompromising than the
situation demands.

 

[Rick Brandt]

Still, "you need to take a class if you have 50 columns" and "you
must be crazy if you need 200 columns" is rather more uncompromising
than the situation demands.

Agreed. Context is important and *most* of the time the entirety of a post
describing such a table paints a picture of a novice to database design.

 

[David W. Fenton]

While it may be true for most Access databases, it really isn't
unusual to have a boatload of columns in a table. I deal with
tables in SQL Server databases for enterprise solutions that have
hundreds of columns. I believe the max is 1024.

I have found that a lot of the people developing web applications
are complete idiots when it comes to databases -- they denormalize
everything because it makes it easier for them to populate their web
pages, and then have code around it in their apps to keep the data
from getting messed up.

It's not a matter of poor design.

Yes, it's absolutely a matter of poor design. There is no real-world
entity that has 1024 attributes that need to be modelled in a
database record.

If a record has that many elements then
there is no reason to split them up across multiple tables.

On the contrary, there are no real-world entities that I can think
of that are properly modelled with more than 50 or so fields.

 

[Bill Mosca, MS Access MVP]

If a record has that many elements then

On the contrary, there are no real-world entities that I can think
of that are properly modelled with more than 50 or so fields.

Well, since you can't think of any then it must be so. Thanks. I'll let
Eclipsys & Mckesson companies know they are designing their Healthcare
software all wrong.

 

[aaron.kempf]

I've built some databases that legitimately needed 300 + columns.. for
transactions / consistency sake.

I thnk that MDB is hampered by the 255 column limit.

but that's why I use Access Data Projects and SQL Server

-Aaron

 

[aaron.kempf]

you're wrong David.

I'm sorry that you think that the world fits into 3rd normal form.

IT DOESN'T.

I claim that MIcrosoft needs to build support for 'very wide databases'
by allowing us to do simple things like this:

a) display a subset of COLUMNS based on a column name mask
b) display columns in alphabetical order

Maybe MS should come up with a more constructive 'database table
wizard' for analyzign big tables.. because the table wizard in MDB.. if
you pardon the french-- it just flat out sucks dick

-Aaron

 

[Roger Carlson]

If you limit the discussion to a Relational Model, I will agree. However,
Relational Databases can use either a Relational Model or a Dimensional
Model.
Databases built on a Relational Model are good for things like interactive
systems that require Data and Referential Integrity checks. However, they
are horrible for reporting. Database built on a Dimensional Model are use
in things like Data Warehouses. These are not interactive/dynamic systems,
but are static and usually populated daily from an RM source. The advantage
of the Dimensional Model is that most of the relationships are flattened,
which make reporting very efficient.

All that said, to say this: Dimensional Model databases can be VERY wide.
Ordinarily, they are done in products like Oracle and SQL Server, but they
can and are done occasionally in Access.

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

 

[Bill Mosca, MS Access MVP]

One of the SQLS databases I work with has a table with 130 columns. It has
been in use since June and already contains 1.7 million records. The DBA
explained the high number of columns this way:
The table is in constant use and will continue to grow at a rapid rate. By
keeping all those columns in one table the time needed to run a query
against it is greatly reduced as there are no joins to other tables.

He showed me a test database where the table had been split up into smaller
ones. He ran the Estimated Execution Plan in QA against both designs. The
130-column table blew away the joined table structure as far as performance
goes.

 

[aaron.kempf]

databases should be flexible enough to consume any model imaginable

and they are

Dont limit your thinking to the relational model or the dimensional
model.
most databases should fit BOTH

-Aaron