Record too large

[cindy muth]

Record is too large. (Error 3047)
You defined or imported a table with records larger than
2K. This error occurs when you enter data into the record -
not when you define the table structure. Redefine the
table by making some fields shorter, removing unneeded
fields, or moving some fields to other tables.


I am running a query that attempts to parse a memo field
into 150 line items with a field size of 75.

How can I correct this problem?

Thanks for your help, cm

 

[Douglas J. Steele]

Do you mean you've created 150 fields, each of length 75, and you're trying
to stuff the contents of the memo into those fields? There's no way to do
that: the limit of 2K is hard and fast.

Rather than 150 fields, normalize your data: create a table with 3 columns:
the ID, a field counter, and a text field of 75. Put the first 75 characters
with the field counter set to 1, the next 75 characters with the field
counter set to 2, and so on.

 

[cindy_muth]

What vehicle do I use to populate the contents of the memo
field into this new table design? Thanks for your help! cm

 

[Bas Cost Budde]

What vehicle do I use to populate the contents of the memo
field into this new table design? Thanks for your help! cm

A "simple" VBA procedure will do.

Assuming your original table indeed has some ID field which you can use
for the destination table:

(AIR CODE)

sub convert()
dim rs as recordset
dim cLine as string
dim nCount as integer
dim cID as string ' if it is Long, use Long
set rs=currentdb.openrecordset("sourcetable")
do until rs.eof
cID = rs!ID
cLine = rs!yourmemofield
ncount = 1
do until cline=""
currentdb.execute("INSERT INTO targettable(id,rownum,textvalue)
values('"&cid &" ','"& ncount &"','"& left(cline,75) &"')"),dbfailonerror
cline = mid(cline,76)
ncount = ncount+1
loop
rs.movenext
loop
rs.close
set rs=nothing
end sub