Maximum fields in table problem

[Don Wiss]

I am using Access 2002.

I am trying to design a table that will store the input, and some output,
from a spreadsheet. I have 239 fields. Because newer records will get saved
into the same table, it will be much easier if I keep it all in one table
for ease of deleting the older records.

After entering 203 fields I tried to save the design. Excel claimed I had
too many fields. I found out that Doubles and Long Integers count as two.
So I changed my Doubles to Currency. I still got the error. I changed all
to text. Same Error. I deleted 121 fields. Same error. Then I closed out my
design without saving and I found a design with the fields that were left
after deleting, but the types that I had originally. Was this error
completely bogus?

I need to redesign. I've learned that Currency is better for me than
Double. But I don't want to recreate this and have the same problem. So,
the big question: Does Currency count as two fields when counting to my max
of 255 fields? As this is simply saving spreadsheet data, I could make
everything text. But I want to try with Currency first.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[John W. Vinson]

I am using Access 2002.

I am trying to design a table that will store the input, and some output,
from a spreadsheet. I have 239 fields. Because newer records will get saved
into the same table, it will be much easier if I keep it all in one table
for ease of deleting the older records.

Excel is a spreadsheet. Access is not. It is NOT "Excel on steroids." It's a
relational database; a good design for a spreadsheet (as in this case) is
likely to be a VERY BAD design for a database, and vice versa.

60 fields is an ENORMOUSLY wide relational table. If you're actually going to
*use* this data in Access (rather than just have it as archival storage for
Excel) you really should consider storing the data as multiple normalized
tables. "Fields are expensive, records are cheap!"

After entering 203 fields I tried to save the design. Excel claimed I had
too many fields. I found out that Doubles and Long Integers count as two.

Incorrect, at least within Access! There is a limit of 255 fields (hard limit,
no getaround) in a table - and, more subtly, a limit of 2000 bytes *actually
occupied* in any single record.

So I changed my Doubles to Currency. I still got the error. I changed all
to text. Same Error. I deleted 121 fields. Same error. Then I closed out my
design without saving and I found a design with the fields that were left
after deleting, but the types that I had originally. Was this error
completely bogus?

Well... misleading. You get 255 fields, but every time you add OR CHANGE a
field definition, another of the 255 slots get used up. You must use Tools...
Database Utilities... Compact and Repair database to free up the occupied
"slots".

I need to redesign. I've learned that Currency is better for me than
Double. But I don't want to recreate this and have the same problem. So,
the big question: Does Currency count as two fields when counting to my max
of 255 fields?

Neither Double nor Currency "count as two fields" and I'd be curious to know
where you got the information that they did.

As this is simply saving spreadsheet data, I could make
everything text. But I want to try with Currency first.

Create the table empty, in table design view, without changing field types
after they've been entered; Compact the database after creating the table;
then import the data from Excel. If you don't hit the 2000 byte limit (each
Double or Currency takes 8 bytes so you *might* be OK if you don't have a lot
of large text fields too).

 

[Don Wiss]

Excel is a spreadsheet. Access is not. It is NOT "Excel on steroids." It's a
relational database; a good design for a spreadsheet (as in this case) is
likely to be a VERY BAD design for a database, and vice versa.

I am fully aware of this.

60 fields is an ENORMOUSLY wide relational table. If you're actually going to
*use* this data in Access (rather than just have it as archival storage for
Excel) you really should consider storing the data as multiple normalized
tables. "Fields are expensive, records are cheap!"

Access is simply archival storage. Other that to pull out the result fields
quarterly (for an insurance reserve report) we plan to do nothing with the
database. This is why all fields could be in text.

We may though have an empty spreadsheet be able to read back a record and
populate the spreadsheet.

Incorrect, at least within Access! There is a limit of 255 fields (hard limit,
no getaround) in a table - and, more subtly, a limit of 2000 bytes *actually
occupied* in any single record.

Well, after 203 fields I got the error was trying to save my design. There
is no data in the table. After I get the table designed I have VBA code
that will add a record to it when the user presses a button.

Well... misleading. You get 255 fields, but every time you add OR CHANGE a
field definition, another of the 255 slots get used up. You must use Tools...
Database Utilities... Compact and Repair database to free up the occupied
"slots".

By change you mean changing from text to number is a change? If I can't
leave design mode how can I compact and repair.

Neither Double nor Currency "count as two fields" and I'd be curious to know
where you got the information that they did.

From the PCHelps support line that my employer subscribes to.

Create the table empty, in table design view, without changing field types
after they've been entered;

Okay. I see now what I did. I first entered 122 of them. Then I went back
and changed their type. I see what you are saying is I have to enter them
and set the type before I leave that row.

Now. I deleted a chunk in the middle trying to get out and save the design.
If I insert them back they will be text and changing will blow me past.
What I can do is simply leave them as text. Those fields are rarely going
to have any data, so an empty text field is best for not blowing past my
max. For that matter I should make many of the number fields text.

Compact the database after creating the table;
then import the data from Excel. If you don't hit the 2000 byte limit (each
Double or Currency takes 8 bytes so you *might* be OK if you don't have a lot
of large text fields too).

Thank you for clarifying how this all works.

One question. I had thought that setting a text field to a certain length
meant that the full length was used for storage. Reading Data Types help
(when trying to solve this problem) it appears that storage space is just
what the text length actually is. So what advantage is there to reducing
the default text length, or why not use 255 for that matter, if no space is
wasted?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Rick Brandt]

One question. I had thought that setting a text field to a certain
length meant that the full length was used for storage. Reading Data
Types help (when trying to solve this problem) it appears that
storage space is just what the text length actually is. So what
advantage is there to reducing the default text length, or why not
use 255 for that matter, if no space is wasted?

Data integrity. If I have a field for say part numbers that are only
allowed to be 15 characters long then making the field that large prevents
the user from entering a string that would be longer than the rules allow.

 

[Allen Browne]

John is correct: Access uses only the disk space needed to store the actual
characters.

You have the option to define a smaller size because you don't need 255 char
for things like surnames or city names. You can prevent users typing
inappropriate amounts of stuff in a field amounts. More importantly, you can
design the table so that it cannot suddenly become too wide if the user does
fill up all the fields.

For example, the Database Issue Checker utility does check for this kind of
issue with your tables:
http://allenbrowne.com/AppIssueChecker.html

Whether the width is 2k or 4k characters depends on factors such as your
version of Access, regional settings, and unicode compression. The
documentation on this is confusing (contradictory?), but you can probably
get by with 4k in the US with recent versions of JET.

You can loop through the Fields in the TableDef, summing the Size of each
one to see if your table could fail with some data.

 

[Don Wiss]

Data integrity. If I have a field for say part numbers that are only
allowed to be 15 characters long then making the field that large prevents
the user from entering a string that would be longer than the rules allow.

In my case I only use Access as a repository. I mainly use it to store
parameters. I design an Excel worksheet to make it easy for the user to
update the parameters. Then I write Excel VBA to convert it onto a sheet
into the format that I want to store it into Excel. This table design is
based on how I will use Excel VBA to pull it back out. Then I import this
sheet into Access. Or in other words, I'm an Excel guy.

Thanks all, Don <www.donwiss.com> (e-mail link at home page bottom).

 

[John W. Vinson]

Okay. I see now what I did. I first entered 122 of them. Then I went back
and changed their type. I see what you are saying is I have to enter them
and set the type before I leave that row.

Yes. That should let you create the whole table. You could also enter part of
the fields, change a few if necessary - just so you don't add 255 fields
including adding new fields AND changing old fields; close the table; compact
the database; and resume adding fields.

Now. I deleted a chunk in the middle trying to get out and save the design.

Compact the database after doing so and you'll be able to continue.

One question. I had thought that setting a text field to a certain length
meant that the full length was used for storage. Reading Data Types help
(when trying to solve this problem) it appears that storage space is just
what the text length actually is. So what advantage is there to reducing
the default text length, or why not use 255 for that matter, if no space is
wasted?

Well, not much. If you will actually be using the database for other than
archival storage, indexing and searching are more efficient if the size of the
field is limited; and it's a tool for quality control, to prevent users from
entering invalid data if the valid data will never be more than two bytes
long, for example. It may not matter a bit in your case.

 

[Don Wiss]

Well, not much. If you will actually be using the database for other than
archival storage, indexing and searching are more efficient if the size of the
field is limited;

Okay. In my tables I do set indexes. Often the state abbreviation with be a
field. Date or year another. And maybe a single letter for a type. Or a
short string for a line of business. In this table I've only indexed the
claim and contract numbers. So reducing the text fields will have a benefit
for me on those fields.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Jeff Boyce]

Don

You've been describing why you feel you need to use Access to store data
from Excel.

Is there a reason you aren't simply linking to the (original) Excel data
instead of importing it into Access?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Don Wiss]

You've been describing why you feel you need to use Access to store data
from Excel.

Is there a reason you aren't simply linking to the (original) Excel data
instead of importing it into Access?

I often do write harvesting macros that go through folders and grab data
from all found workbooks and aggregate on a worksheet. In this case for
every reinsurance layer there are two calculated numbers that they need to
capture and summarize each quarter. Then they need to capture the claim
numbers to be sure each claim is included. For now this is all they are
going to do. There are three departments involved with this project: my
pricing department, claims, and reserving. Not being an actuary I don't
know all what they may want to do in the future.

I finished the project this morning in an hour. I got the table designed.
Then I tested the code that pushes a record into the table and it worked.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[tygrus _]

Access has a problem when you change fields (name, datatype, number) in a large table. It cannot have more than 255 fields in a transaction. So if you have 200 fields then change 100 fields you have 300 fields in the transaction which exceeds the 255 limit.
If you have 200 fields already you cannot change more than 55 of them at a time. So change 50, (close design) save, (open design) and change another 50 fields .. and repeat.
Just for backup of Excel .. better to copy sheets elsewhere or wnole files to an archive.
Saving transactional data for summary .. start transaction in database, create Excel file with intial data, save link in Access database, action (eg. button) in Access or Excel file to update data in Access with final figures.

You could create separate tables for different portions of the Excel worksheet with a unique identifier for reference (you can't put 200+200 fields in a query but could process them sequentially).