A) 140 fields in a record -- OK ?? --- B) Do empty fields use disk space?

[MyEmailList]

1 - I've got a record with 140 fields - should I expect any problems
because of so many fields in each record?

2 - How does Access treat fields in which the user enters no data? if
it is a text field with 50 charcters of space allowed... does Access
see that it is an empty field... or a field in which on a few
characters are entered... and compact it... or does it simply save 50
bytes for the field even though none or only some of them are used?

thanks for any help.

Mel

 

[KARL DEWEY]

1 - I've got a record with 140 fields - should I expect any problems
because of so many fields in each record?
140 fields sounds like a spreadsheet and not a normal database structure.
The number of fields should not be a problem but if it is spreadsheet
methodology you will have problems when you need to rollup the data.

2 - How does Access treat fields in which the user enters no data? if it is a text field with 50 charcters of space allowed... does Access see that it is an empty field... or a field in which on a few characters are entered... and compact it... or does it simply save 50 bytes for the field even though none or only some of them are used?

My understanding is that it reserves the total space but Access can be up to
2Gb in the newer versions.

 

[Douglas J. Steele]

1) I can't imagine a properly normalized table that would require 140
fields. 30 fields is considered a large number for a properly normalized
table. Access is capable of handling that many fields (it can handle up to
255 fields), but yes, you could run into problems if you've got a lot of
text fields, and the total storage requirement for a row is in excess of
2000 bytes.

2) Access takes as much space as is required for the amount of text being
stored. This relates to what I said above. Access won't complain at all if
you create a table with 10 fields of 255 bytes each, despite the fact that
that exceeds the maximum size allowed for a row. It's not until you try and
store the 2001st byte in that row that you'll get an error.

 

[Albert D. Kallal]

My understanding is that it reserves the total space but Access can be up

to
2Gb in the newer versions.

No, ms-access does not pre-allocate the space for text fields.

If you define a field with 5,chars or 255 chars, it takes up the same space.
Ms-access does NOT pad, or store the extra length.....

The fields are thus variable length, and not fixed length like the "older"
databases that were on pc's many years ago (like Dbase, FoxPro etc).

So, if you define 20 fields of 5 chars, or 20 fields of 255 chars..the
spaced used is the same. (ms-access does not save, or store the extra space
in a field, but only the characters actually typed in).

 

[MyEmailList]

See my post "140 fields in a single Record - or What ?"

thanks for the help