Record too large

  • Thread starter Thread starter cindy muth
  • Start date Start date
C

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
 
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.
 
What vehicle do I use to populate the contents of the memo
field into this new table design? Thanks for your help! cm
 
What vehicle do I use to populate the contents of the memo
field into this new table design? Thanks for your help! cm
Click to expand...

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
 
Back
Top