vb problem reading text from Word tables

I wrote a program that reads the cells of Word (2007) tables and puts the results into a database. Currently using Access 2007 memo fields. Everything is fine except that the carriage returns and line breaks don't carry over into the database. for some reason they are stripped so the resulting text is one long string. even a blank line in the text gets stripped out when it goes into the database.

RBECKMNAsked: 2012-12-26

Who is Participating?

Solutions

LVL 40

Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented: 2012-12-27

Word does not records the line breaks, it records only the carriage return with one character only, Chr(13).

Access memo fields record line breaks as the more standard combination of 2 characters, Chr(13) Chr(10).

I would try something like the following:

textInBd = textInWord.Replace(Chr(10), Chr(13) & Chr(10))

LVL 74

Jeffrey CoachmanMIS LiasonCommented: 2012-12-26

Make sure the "Text Format" property (Not to be confused with the "Format" property) of the memo field is set to "Rich Text"

Can you give us some insight into your "Program" and how it inserts the text into the field?
If setting the Text Format to Rich text does not work, then another option would be to insert the text via SQL Inserts.

Something roughly like this:
CurrentDb.Execute "INSERT INTO Yourtable (YourMemoField) VALUES ('SomeText1' & '<br>' & 'SomeText2')", dbFailOnError

...With your "Program" converting whatever character the cr/lf is in Word, ...to <br> for Rich Text (HTML)

JeffCoachman

RBECKMNAuthor Commented: 2012-12-28

-- original code that was causing the problem:

TheString = theTable.cell(ThisRow, 1).Range.Text

-- the code now is:

TheString = Replace(theTable.Cell(ThisRow, 1).Range.Text, Chr(13), Chr(13) & Chr(10))

-- followed by:

therecordset(n)=theString, where n is the field number in the recordset