Problem importing Rich Text from SQL to Access - need VBA to apply HTML encoding

[petcos]

I am programmatically importing data from SQL Server into a local Access table. Several fields contain rich text in SQL, but when they are imported via append query into Memo fields set up with Rich Text in the Text Format property, the Rich Text encoding is not applied (they show up without line breaks).



As a workaround, I can change the Text Format property to Plain Text, then run the append query, then manually change the property to Rich Text in design view on the table. When I do that, a dialog will come up and say: 'The field will be converted to Rich Text, and all the data it contains will be HTML Encoded.' If I click OK, then Access will run through the column and apply the HTML encoding and all is well with the world.

Obviously, it isn't ideal to ask my users to manually change the design of several fields on the local table each time they want to refresh their data.

I've tried to change the Text Format property using VBA (CurrentDb.TableDefs("MyTable").Fields("MyField"). Properties("TextFormat").Value = acTextFormatHTMLRichText), but when I do that, the formatting is not applied. It changes the property, but in my forms the data still shows up without the line breaks.

The only way I have found to get Access to apply the HTML formatting is to manually change the design of the table. Does anyone know a better way to do this? Is there a way to force Access to apply the formatting during the import? I've tried changing the Text Format property in the append query, but that didn't have an impact.

Thanks for any advice!

[ItsMe]

I think you are onto something when you state
"The only way I have found to get Access to apply the HTML formatting is to manually change the design of the table."

This is from Microsoft
"The text in the Memo field is stored and displayed as plain text."

The fact that the field you chose to store your data is of type "Plain Text" is the issue. The field will always dictate how it handles the data and will truncate imported data to suit its own data type.

[petcos]

Thanks for the reply.

I agree that if the Text Format property is set to Plain Text then Access should not apply the formatting, but the problem is that if I set the property to Rich Text (prior to the import), then the formatting is still not applied. The only way (that I can find) to get Access to apply the formatting to the data is to have it run that little routine that it runs when you manually change the design of the table (the one that indicates the data will be 'HTML Encoded').

I'm just hoping someone knows a VBA command that I can use to kick off that routine programmatically, so I can avoid having my users change the table design manually after each refresh.

Thanks again!

[June7]

Importing to a Rich Text field loses the HTML line breaks so you manually change the field to Plain Text then import the HTML coded data and change field back to Rich Text and the HTML formatting shows correctly. Very weird.

Are line breaks the only HTML code?

[petcos]

Yes, from what I can tell the only formatting being applied is the addition of line breaks. But that makes a BIG difference in readability.

This is the text after I run the append query (regardless if the Text Format property is Plain Text or Rich Text):

Here are the steps to bake a cake: 1. Get the ingredients. 2. Break the eggs. 2. Put the ingredients in a bowl. 4. Add milk. 5. Stir. 6. Pour into a pan 7. Bake in oven for 30 minutes. Be sure not to bake for more than 30 minutes or the cake will be burned.

And this is what it looks like after I manually change the property to Rich Text:

Here are the steps to bake a cake:

1. Get the ingredients.
2. Break the eggs.
3. Put the ingredients in a bowl.
4. Add milk.
5. Stir.
6. Pour into a pan 7
7. Bake in oven for 30 minutes.

Be sure not to bake for more than 30 minutes or the cake will be burned.


None of the HTML markup is visible after importing, but it is obviously there because it is applied correctly after manually changing the table design.

I've been working on this issue for about 3 weeks now, and I can't find anything online to programmatically force Access to apply the line breaks after importing the data... or get Access to apply the formatting during the append query.

Thanks again!
Peter

[June7]

What is the HTML code used for line break - <br>?

Maybe do a Search/Replace to change <br> to Chr(13) & Chr(10) and stick with Plain Text field.

[petcos]

Thanks! I've tried that, but still no luck. The HTML markup (<BR> etc) is not visible in the data, so the Replace function can't find it. The markup is there though, because when I change the format property manually, all the line breaks are inserted correctly.

So, Access is bringing in all the text correctly, and it is bringing in the HTML markup (although invisibly), but it does not apply the formatting to the text until you go into design view on the table and manually change the format to Rich Text.

Interestingly, if I import the data into a Memo field that is formatted as Rich Text, then change the Text Format property to Plain Text, it will strip out the HTML encoding (although nothing visibly changes in the field), so that if I then try to change it back to Rich Text, the line breaks are not added.

The only way I have gotten it to work is to import into a field that is formatted as Plain Text, then go into design view on the table and manually change the Text Format property to Rich Text for each field, and let access do the HTML Encoding.

There is a PlainText() function, but there is no equivalent RichText() function, which I think is what Access uses when you change the table design. I'm just hoping that function is exposed through VBA somehow.

Thanks again!!

Peter

[ItsMe]

... The HTML markup (<BR> etc) is not visible in the data, so the Replace function can't find it. The markup is there though, because when I change the format property manually, all the line breaks are inserted correctly...

I am having a hard time following this statement. If the replace function can not see it then how does it get imported? How are you changing the format manually and at what point in the procedure?

[petcos]

Sorry, I know it's confusing.

Look above to my cake baking example to see how the text looks when it is imported. Notice how there are no HTML markup elements (<BR> etc). It's just one long stream of text.

After the import, I open the table in design view, scroll to the Text Format property, and change it from Plain Text to Rich Text. Access then pops up a dialog saying the contents will be HTML Encoded, I click OK, and see a quick hourglass as it runs through the contents of the field and applies the formatting, and when I reopen the table in datasheet view I see the results with all the right line breaks (like in the second half of the cake bake example).

So:

Step 1: Delete all content in the table (or create a new table from scratch)
Step 2. Change the Text Format property on the Memo field to Plain Text (or leave the default on a new table)
Step 3: Run an append query from SQL Server
Step 4: View the table in datasheet view, you will see a large glob of text with no line breaks similar to the Cake Bake example above.
Step 5: Open the table in design view, change the Text Format property to Rich Text and click OK.
Step 6: Close and save the table, open it again in Datasheet view
Step 7: Now you see the nicely formatted data with line breaks.

Weird, I know, but this is how it works. Somehow the HTML markup is being imported, but it is not visible in the data. I've verified this on several different machines with several versions of Access (back to Access 2007).

Another approach I've tried is to use SendKeys to change the table design on behalf of the user, but there are a bunch of these fields and it quickly grows untenable.

Thanks again for any advice!
Peter

[June7]

Still scratching head

I am at a total loss on solution.

[ItsMe]

At this point I would consider the ODBC driver and see if there is a better version that will recognize the correct standard (ANSI etc.) to match the data in SQL server. I can not explain the behavior you see when changing from Plain to Rich. It sounds like a conversion that Access is doing but I can not replicate. I would guess the elements are not there (never were imported) and Access is placing them there. This guess does not seem like a good guess though.

[NJPhillips01]

Sorry, I know it's confusing.

Weird, I know, but this is how it works. Somehow the HTML markup is being imported, but it is not visible in the data. I've verified this on several different machines with several versions of Access (back to Access 2007).

Another approach I've tried is to use SendKeys to change the table design on behalf of the user, but there are a bunch of these fields and it quickly grows untenable.

Thanks again for any advice!
Peter

I am having the EXACT same problem using SharePoint as a backend... when I bring over a multi-line field into Access, it is brought in as a single line without line-breaks. The ONLY way I can get line breaks is to manually change the field to Rich Text... did you ever find a solution?

[amrut]

I do not have any experience with SQL Server/HTML. I did a small test after reading this post. Created a table with memo field set to Rich text. In case of Access, the options for Rich text formatting doesn't include line breaks though bulleted lists are supported. I created couple of records, used Ctl+Enter to insert a line break in table. Used a query to append these to another table and set that table as recordcsource of a form. Popped a messagebox to show the value in a textbox ( Rich text format). See the results


The difference I find is of <div> tag.

[jstui]

I know this is a very old issue, but I am adding this in case someone else finds this.
I would bet that the issue here is not what the user thought it was.
I believe that when viewing the data directly in the table, if there were html codes, then they would have been visible.
Instead I suspect the issue here is one of Windows vs Linux (and other OSes and other apps) new line characters.
The default representation of a new line in Windows is a combination of two character codes. Carriage return (code 13) and line feed (code 10).
In many other operating systems and some applications in Windows a new line is only represented by the latter, a line feed.
In fact, even message boxes in windows typically only use a line feed character.

I had a similar experience and found that my problem was that the data did not contain the proper representation for a Windows new line.
I had to address the problem by simply executing a Replace on the value.

Replace(Replace(SourceField, Chr$(10), Chr$(13) & Chr$(10)), Chr$(13) & Chr$(13), Chr$(13))

You will notice that I included an extra replace in case the value was already in proper Windows format.

Note that I determined this by parsing the data using ASC() and MID$() to see what the actual character codes were. e.g. ASC(MID$(SourceField, 20, 1))
You can use that in an access query to see the results, or in VBA when getting the value in a textbox.
In addition, if you display html in a textbox where the format is set to plain text, it will show you all of the html codes in the data.