2000 Character Record Limit

[Guest]

Ok so I accept that there can only be 2000 characters across a record. (all
columns adding characters in sequence)..

My question is with this limitation, how best can I test for an average of
characters across records?

Is there a utility out there?

I am splitting one table into two tables now using ONLY my best judgement,
but that seems inadequate, there must be a more solid way of doing this.
Worst case scenario is that the split will happen but I still have two tables
that are two large.

FYI: I inherited this mess..

 

[Guest]

Len([Field1]&[Field2]&....Fieldx])

 

[Larry Linson]

Ok so I accept that there can only be 2000 characters across a record. (all
columns adding characters in sequence)..

Too bad that you are so accepting, because the limit was increased from 2048
to 4096 when Access began to support UniCode (16-bit) characters with Access
2000, two versions back from current. And, as UniCode text fields, by
default, have UniCode compression set to True, you actually get advantage of
most of the increase.

My question is with this limitation, how best can I test for an average of
characters across records?

Is there a utility out there?

I am splitting one table into two tables now using ONLY my best judgement,
but that seems inadequate, there must be a more solid way of doing this.
Worst case scenario is that the split will happen but I still have two tables
that are two large.

If your record is too large, Access will tell you.

But, the limit is not as stringent as it may seem because careful design
will limit the number of Fields in a Record, which will limit total Record
size, and the contents of Memo Fields are stored separately, so they don't
use up bytes toward the record size at all in proportion to the Memo Field
size.

A little testing should show you what the error code is for "Record too
large." Field the error, log it, and review the log to see if it is
happening enough to warrant taking further action. There's not really a
workaround, however, other than distributing the Fields from one Table into
two or more Tables.

Larry Linson
Microsoft Access MVP

 

[dbahooker]

wake up to the 90s and dont use MDB for anything

Access Data Projects are AWESOME!!

you can use SQL Server-- which has much larger / wider limits that
silly old mdb