Record too Large (Oracle to Access 2000)

[Jim Dowling]

I'm working with large us census tables and need to import
them from Oracle 9i to Access 2000. The import fails give
a generic error "Record too large." Both the MS and
Oracle drivers provide the same error. If I use Access
97, the import is successfull, though all numbers are
imported as doubles. The basic table:

FILEID VARCHAR2(6)
STUSAB VARCHAR2(2)
CHARITER NUMBER(3)
CIFSN NUMBER(2)
LOGRECNO NUMBER(7)
P001001 NUMBER(10)
P002001 NUMBER(10)
P002002 NUMBER(10) ..... remaining columns are number(10)
with a total of 227 columns

Is there a workaround that permits the use of Access 2000?

Thanks,
Jim

 

[John Nurick]

Hi Jim,

At a guess you're running into consequences of the not entirely
satisfactory implementation of the Decimal data type (which I think waws
new in Access 2000/Jet 4) and the limit of approximately 2000 characters
per record.

Access 97, which lacks the Decimal type, is importing them as Doubles -
and at 8 bytes per double it can fit all 222 of them and the other
fields into 2000 8-bit characters.

AIUI, Access 2000 is to be trying to create 222 10-byte decimal fields
and hitting the 2000-character limit - which is a bit of a
disappointment, because Access 2000 and later natively store text as
16-bit Unicode, so the 2000-character limit implies up to 4000 bytes per
record.

For a workaround, are the values in the census table small enough to be
stored as Longs? If so, I presume that they could be typecast in the
Oracle view. This would be the neatest way to go.

Alternatively, you could use two tables related 1:1, with about half the
fields in each table (not a nice solution). Or you could normalise it
(I've imported tables with more than 1500 fields this way) to something
like this

FILEID VARCHAR2(6)
STUSAB VARCHAR2(2)
CHARITER NUMBER(3)
CIFSN NUMBER(2)
LOGRECNO NUMBER(7)
FIELDNAME VARCHAR(7) - e.g. "P001001"
FIELDVALUE NUMBER(10)

though if the table is really big you'll then run into the 2GB limit on
the size of an MDB file.