"Record is too large" error on only 294 fields all set to Memo?

[ArgarLargar]

I don't understand this. There must be a setting I can change
somewhere.

I have a spreadsheet with 294 fields. I use an import specification
to bring the data from a text file. I am receiving an error called
"Record is too large" but I don't know why?

Any suggestions?

Thanks,

Nick

 

[ArgarLargar]

I need to make this clearer. I have data with 294 fields. It is
contained in a text file which is called by an access import
specification macro.

 

[Jerry Whittle]

Fields or Records? An Access table can only have a maximum of 255 fields in
a table. If you exceed either the number of fields, you can not import it
into a single Access table.

 

[John W. Vinson]

I need to make this clearer. I have data with 294 fields. It is
contained in a text file which is called by an access import
specification macro.

An Access Table (or Query) can have a maximum of 255 fields (an absurdly huge
width for any sort of normalized table); and the sum of the sizes of all the
fields in each record must not exceed 2000 bytes. Each Memo field contributes
16 bytes of overhead to that count, I believe.

You will need to import this monster into more than one table, probably using
VBA code.

John W. Vinson [MVP]

 

[ArgarLargar]

Thanks all!

 

[Guest]

fields in each record must not exceed 2000 bytes.

Still? Page size now 4K, but record limit still 2K?

 

[Klatuu]

From Access (2003) Help - Access Specifications:
Number of characters in a record (excluding Memo and OLE Object fields) when
the UnicodeCompression property of the fields is set to Yes 4,000

Maybe you are thinking of an older version.

 

[John W. Vinson]

Still? Page size now 4K, but record limit still 2K?

It is in A2003; I don't know about A2007, don't have it installed.

John W. Vinson [MVP]

 

[John W. Vinson]

From Access (2003) Help - Access Specifications:
Number of characters in a record (excluding Memo and OLE Object fields) when
the UnicodeCompression property of the fields is set to Yes 4,000

Maybe you are thinking of an older version.

Thanks, Dave - obviously I was!

John W. Vinson [MVP]

 

[John Nurick]

From Access (2003) Help - Access Specifications:
Number of characters in a record (excluding Memo and OLE Object fields) when
the UnicodeCompression property of the fields is set to Yes 4,000

Help is forgetting people who don't use the roman alphabet. I guess it
should read something like this:

Number of characters in a record (excluding Memo and OLE
Object fields) when the UnicodeCompression property of the
fields is set to Yes *and* all characters are in the Windows
(Western) character set: 4,000

 

[David W. Fenton]

Help is forgetting people who don't use the roman alphabet. I
guess it should read something like this:

Number of characters in a record (excluding Memo and OLE
Object fields) when the UnicodeCompression property of the
fields is set to Yes *and* all characters are in the Windows
(Western) character set: 4,000

Anyone who is worrying about those limits has a badly-designed
schema.

 

[Guest]

Anyone who is worrying about those limits has a badly-designed

schema.

Well I guess, although I think some people actually use their database
to contain data, not just pointers to data, nor data about data.

The problem I have is that some of the databases I manage contain
simple multi-dimensional data. I can see that if I had complex
multi-dimensional data, I would want to switch to a non-relational
model, but for simple two-dimensional data, it's easier just to put
the audit or regulatory dimension inline.

Since there is, by definition, no updates or deletions to audit
data, there are no update or delete anomalies. Since there
has always been less than 0.5GB data, there have never been
any data size issues.

You can just replace all of your tables with super-index tables,
but that makes the primary dimension significantly more difficult,
and you loose the basic advantage of a relational database.


(david)