Unable to Change Table Design

[Guest]

I receive survey results from an outside vendor in Excel format. I am
exporting them into an Access database so I can run queries on these survey
answers. My table has 118 fields (all the survey questions). I want to add
three fields. I can't save any changes to the table. I keep getting a message
stating "Record is too large." None of my fields are memo size. I've changed
some of the fields to be way less than 255 characters for the field size. Not
only can I not add my three fields, I can't make any changes to the design at
all.

I'm not running this survey. My job is to analyze the results. I would
really like to just add my three fields and go on, but maybe I should be
doing things differently.

Thank you.

 

[Douglas J. Steele]

118 fields is an unusually large table: most of us will tell you that more
than 20 or 25 fields in a table is unusual.

For a good survey database, see Duane Hookom's "At Your Survey" at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[Guest]

The maximum number of characters in a record is 2,000. That does NOT include
memo or OLE fields. Also even though a text field is set to 255, Access only
uses the actual number of characters in that field. Therfore count up the
number of character in your largest record. I bet that it's getting near
2,000. You might get some breathing room by converting some of the text
fields to memo; however, remember that memo fields don't do things like
sorting and grouping.

Then there's the total number of fields issue. It's 255 fields in a table
maximum; however, there are ways to 'burn up' fields and hit the maximum way
before hitting the 255 limit.

One option in both the cases above is to import part of the Excel
spreadsheets into two tables. You could set up a 1-1 relationship between the
tables and that way basically treat them as one table with twice the number
of fields and characters. To import this way, create name ranges in Excel and
import the ranges and not the sheets.

 

[George Nicholson]

Do a Compact& Repair on the file. Tables have a 255 field limit but any
deleted fields count against that limit until you do a C&R. likewise,
changing the data type or length of a field actually creates a new field,
copies the data and deletes the old field. That deleted field counts against
the 255 field limit until you do a C&R as well. Don't know for sure, but I'd
imagine record length limitations are handled the same way: deleted fields
are included in the length calculation until you do a C&R.

HTH,

 

[John W. Vinson]

I receive survey results from an outside vendor in Excel format. I am
exporting them into an Access database so I can run queries on these survey
answers. My table has 118 fields (all the survey questions). I want to add
three fields. I can't save any changes to the table. I keep getting a message
stating "Record is too large." None of my fields are memo size. I've changed
some of the fields to be way less than 255 characters for the field size. Not
only can I not add my three fields, I can't make any changes to the design at
all.

I'm not running this survey. My job is to analyze the results. I would
really like to just add my three fields and go on, but maybe I should be
doing things differently.

Thank you.

Probably should! "Fields are expensive, records are cheap". The "Record Too
Big" error suggests that you have more than 2000 bytes of data in one or more
records; memo fields are exempt from this limitation, but if you have 118 Text
fields with 20 bytes of text on average, you will not be able to add that
record to the table.

Annoyingly, Access lets you create the table with no problems - just not enter
that much data into it!

If you can't make *design* changes there is something else going on, however.
What error do you get when you try? Have you used Tools... Database
Utilities... Compact and Repair database to recover lost space?

A better design would have one answer *per record* in a related table - see
Duane Hookum's "At Your Survey" database for an example of how to do this:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

John W. Vinson [MVP]

 

[Guest]

Thank you so much! I didn't know about Access counting even deleted fields.
I'm going to try importing the data into two tables by ranges and then
creating a relationship. Thanks again!