Increasing Database Size

[Increase DB capability]

Hello to everyone!

I have an issue - i need to make a database handle more fields. i was given
a last-minute rush project that is very frustrating. long story short, i
have to transform a html w/javascript into an access database.(it really was
the best solution, considering what they wanted to be able to do.) the
problem is, there are over 700 fields that need to be put onto one table.
there are reasons for that madness, i need to export the data into a text
file that is delimited by lines. i played around with the Upsizing wizard,
but it wouldn't even let me logon to my sql server. this database, other
than the number of fields is not very big, not very fancy. it needs to
replicated, and other than that, it is very simple.
Any suggestions?

 

[John Spencer (MVP)]

MAXIMUM number of fields in a table is 255. No way around it.
MAXIMUM bytes that can be stored in a row is 2000. You can fudge this by using
MEMO fields to store multiple items of data, but that is a B_A_D idea.

Options:
Redesign the structure to meet the normalization rules.
or
Use multiple tables in a one to one relationship (again a BAD idea) since you
will run into the 255 limit again.

Queries can contain a maximum of 255 columns (fields), so you can never
manipulate more than 255 fields at any one time in a query or in a table. Which
means that reports and forms are limited to 255 fields/columns. Again, some of
this can be solved by using subforms and subreports.

Your best bet (if you are going to use Access) is to design a structure of
tables and fields that will combine like types of data.

 

[Chris Mills]

Although John's response is exquisite, saying "multiple tables" is "a BAD
idea" seems to me a bit harsh. There seems to me NO CHOICE (in those
circumstances). "Redesigning the structure" would also, essentially, result in
the same thing? If you have 700+ fields, you've got a problem however you
accommodate it, and the only choice given the restraints seems to me multiple
tables of some sort or other?

For the reasons mentioned, memo fields will assist on data size.

I wasn't sure what the query join-limits are (for a one-to-one), but it
appears that will happen sooner or later. However, my small brain has trouble
imagining what you would want with 700 fields, in the same form or report or
something. In any format, you can only fit a dozen or so (let's not argue,
I'll admit 100!) on an A4 page!

Thinks...how long would it take to fill out 700 fields...
Chris ;-)

 

[John Spencer (MVP)]

Perhaps I wasn't clear.

Using Multiple Tables in a ONE TO ONE relationship with a combined TOTAL of 700
fields is in my opinion a bad solution. It returns you back to the starting
point when you join the tables unless you select the items you need from each table.

Redesigning should result in multiple tables, but not with a total of 700
fields. For instance, (simple example).

Original Table
RecordID
LastName
FirstName
FaxPhone
CellPhone
CellPhone2
Pager
HomePhone
WorkPhone
HomeFax
HomePhone2
PrimaryContactPhone
....

Becomes
People Table
PersonID
LastName
FirstName

PhonesTable
PersonID
PhoneNumber
TypeOfPhoneNumber

This structure allows you to have a person with zero to many phone numbers with
7 fields total versus the other structure with 11 or more fields (many of which
will be blank).

There is a lot more to this than the simple example I have given, but this is
one of the concepts in good design of a relational database.

 

[Chris Mills]

Perhaps I wasn't clear.You were perfectly clear, and much appreciated too John. Both in your last
explanation and this one. (of course, I'm not the original poster).

I wouldn't attempt to claim that structures as you suggest (or
"normalization"), are anything other than the best option.

HOWEVER, and consider this a theoretical observation if you like, there CAN be
cases where the data is "FLAT". Flat flat flat even! Whatever the likelihood
(in the posters case some import from a pre-design), one issue may be how to
extend the physical limits.

Alright then, I'll come clean. I have not struck the known limits yet, but I
happen to have a database (meaning here a table) which is flatter than Texas.
It's a table of FORMS, not Access forms but forms you might fill out for your
taxes, marriages, divorces, mortgages (who cares what the next form will be).
SO, although there are a few ID description and index fields like all good
tables need, the rest are just GP "memo fields". Field001 to Field0060 so far,
just "crap" the user needs to type into a "government form". Now "the
government" keeps bringing out new forms, with more "uninteresting crap" up to
120 fields. Which is still OK in Access of course. But if "government forms"
("governments") go on much more like this, I could see reaching the Access
limit for a flat table.

Remember, I have no idea what "form" the government may bring out next. I
don't care, and I don't care about the entered data I merely provide a "bunch
of stored fields". It would be (ie seem) "silly" to provide a hundred tables
for a hundred different "forms", expanding all the time, when ONE table can
accommodate them all (with appropriate general design).

Perhaps that's an unusual "flexibility" for a database. And I agree that
"normalization" is usually the best (but in this and limited other cases, not
always, or so I suggest) (Whatever gets the job done!)

Of course, I've no idea either why the poster needs 700 fields...SEVEN HUNDRED
*my mother is older than that*
Cheers
Chris ;-)

 

[Joan Wild]

It's a table of FORMS, not Access forms but forms you might fill out for
your
taxes, marriages, divorces, mortgages (who cares what the next form will
be).
SO, although there are a few ID description and index fields like all good
tables need, the rest are just GP "memo fields". Field001 to Field0060 so
far,
just "crap" the user needs to type into a "government form".

So why isn't this two tables? Table1 being the Forms (Form name and
purpose); Table2 being for the crap (as you put it) (with FormID and a
single 'crap' memo field).

Now "the
government" keeps bringing out new forms, with more "uninteresting crap"
up to
120 fields.

So you add one record to the Forms table, and 120 records to the Crap table,
no?

 

[John Spencer (MVP)]

OK, what do you do about the limit of record size of 2000 characters? I guess
you could use all memo fields and that would allow you to have quite a few
fields (225???) before you run into the byte limit.

In other words, if you need flat flat flat and a lot of fields then perhaps
ACCESS is not the correct database tool for what you are doing. It might be,
but ...

Anyhow,

I've just been asked to take a look at a database based on a form. In theory
the designer had allowed over 4000 bytes to be entered into the table (and that
had a lot less than 700 fields). Making them all memo fields would work, but
then there is a large penalty to pay in searching and sorting (no indexes
available). Also the design had multiple instances of repeating data. Fixing
this design and making it do what the customer wants will be interesting.

Well, enough ranting about design.

 

[Chris Mills]

So you add one record to the Forms table, and 120 records to the Crap table,

no?

Please watch your language <GUFFAW>

I really don't think so. Because that would involve using a continuous
sub-report style (continuous whatever) to print the "results", and (the
"official form") simply isn't fomatted that way. It's just a bunch of pages
with "random thoughts and formatting", I have no control. Basically it's flat
and you're not allowed to crumple it, so to speak.

I suppose one could do a transform on the data, there and back, but that would
just be converting it between "flat", which it might as well be in the first
place (accepting db limits not reached).

Interestingly, when the "beaurocracy" proposed fully computerising their own
systems, they did indeed propose a "normalized" data format, with appropriate
forms. So I'm not knocking for the sake of it. But, it's NOT that way so far
(probably since 1750 I imagine). Well, go and grab any "government form" you
can find, and remember you're not allowed to alter it's format AT ALL. Design
works back from that absolute, in this case.

Getting back to the original post (just when I nearly forgot!), certainly
some sort or other of multiple tables is needed to get over the field limit,
which is absolute and probably applies more-or-less to any database.

And I would say much use of memo fields too, because 2k data is an even
more severe limit. Memo fields are just fine (however I neither search nor
sort them).

If the data can reasonably be "normalized", then I'd be very pleased too!

Chris

(Another "case" might be 100 pre-set items of Yes/No category. Perfect for
normalization - and drop-down list - NOT! They can be placed all on the same
page in 4 columns of 25, all visible, and the operator goes
click-click-click-done! (instead of
clickfindclick-clickfindclick-clickfindclick-done!) A bit harder for the
programmer, and a bit harder to maintain, but FAST...who's more important the
operator speed or the programmer?)
"What if there's 101?"
"You squash them up a bit"

 

[Chris Mills]

Also the design had multiple instances of repeating data.

I LOVE those (on a government form!). All you do is identify them and place
multiple fields pointing to the exact same data, (no overhead!), and protect
all but the first one and colour the other ones yellow or something! (to
signify write-protect enter elsewhere)