MSSQL RTF

Question

I have a client application (written by me) which is written in vb.net. The client updates/stores RTF data in an MS-SQL 2008 database as VCHAR(MAX).The client works great, allows the user to copy/paste from MS-Word into the client and it recognizes the formatting.

I want to allow users to use MS-Access as a front end to the database so they can write custom reports. The problem is that the text control in Access reporting does not print the RTF correctly – even with the TEXT FORMAT property set to RICH TEXT, I get RTF codes in the report output (e.g., “\rtf1.....”), not formatted text. Would you have any suggestions on how I might solve this problem? I suspect it may be that the MS-SQL field is not in the correct format, but I am not sure – I have tried VCHAR(MAX) and TEXT. Neither work.

I would appreciate any assistance you could give.

Answer 1

Try a ntext field.

I think you have to use columns that support uni-code.

Now, keep in mind if I use ntext, then for the report wizard, yes, I do NOT see the formatted rich text as you state UNLESS I go into the report and set the particular text box as rich text.

So, after I open up the report in design mode, and set that particular text box as rich text, then it does work. Here is a screen shot.

I would let the wizard create a form on that same linked table, and then edit the rich text in the access form (access will display rich text edit control box). Or you could also test a cut/paste. Then try the report. (also, does a form show the rich text correctly? - test this since it might the the rich text format).

I would also take a quick look at the ntext field in sql studio and see how it looks. For me, I see for the above first row: 

<div>Greater then 5000</div>

---

<div><font size=4><strong>This is bold</strong></font></div>

I am doing the above in a2010 with sql server 2005. I suspect this should work the same in access 2007.

So, try ntext. I guessing this might be a uni code.

Also, try the built in rich text editor that access displays when editing a form and the cursor moves into a rich text text box (it might be formatting, and this test will save you a big wild goose chase). So, try using access to enter some rich text to determine if this is an access formatting issue, or rich text format issue.

I suppose this is possible a 2007 issue. I do have access running in VPC somewhere, and if after trying the above you have no luck, then I will go to all the trouble to test this under access 2007, but I would try above first.

Note that I am using a standard linked table to sql server 2005, and I am NOT using a ADP (access data project).

--

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

*** Email address is removed for privacy ***

Answer 2

I think that Access rich text fields use HTML encoding instead of RTF encoding. 

I think that you will have to find an add-in for Access that will handle rtf encoding or write a vba routine that converts the rtf encoding into html encoding.  I don't think that will be a trivial exercise, but I don't know enough about the actual schemas to say if you could just build a simple table of equivalencies and step through that table (in the proper order) and use the replace function to change one encoding for another encoding.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County