[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1243
  • Last Modified:

Alter Column to Rich Text Format

I have never worked with the new Rich Text field in Access 2007.

I have a table in SQL Server.  One of the fields contains HTML.  I need to retrieve it and create a temporary table and then set the control source of a form to that table.

How do I programmatically alter a column in a table in Access 2007 to set the text format to contain Rich Text?
0
wsturdev
Asked:
wsturdev
  • 17
  • 13
  • 12
  • +1
3 Solutions
 
rockiroadsCommented:
does this sql work for you?

alter table <mytable> alter column <mycolumn> memo

memo field can hold rtf and html text
0
 
wsturdevAuthor Commented:
No.  The field is aleady a memo field.  But a memo field can contain either Plain Text or Rich Text, and how the data in the field is handled is apparently based on a "Text Format" property.
0
 
rockiroadsCommented:
ok, gotcha, sorry. Let me look again. Possibly a vba solution, would that be okay?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wsturdevAuthor Commented:
Yes, that is what I am looking for!
0
 
rockiroadsCommented:
I have portable office only and for some reason I am unable to run macros

Ive been looking at tabledefs
something like this

dim tbl as dao.tabledef

set tbl=currentdb.tabledefs("mytable")

tbl.fields("myfields").properties("Text Format")

but until I can figure out how to enable macro's in the portable edition, Im taking educated guesses
0
 
rockiroadsCommented:
ok, sussed it. Found the property to be TextFormat. Change it to reflect your table and column name, obviously

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prp As DAO.Property
    Dim col As DAO.Field
   
    On Error Resume Next
   
    Set db = CurrentDb
    Set tbl = db.TableDefs("Table1")
    Set col = tbl.Fields("Field1")
   
   
    col.Properties("TextFormat").Value = 1
    If Err.Number = 3270 Then
        Set prp = db.CreateProperty("TextFormat", 1)
        col.Properties.Append prp
    End If

0
 
wsturdevAuthor Commented:
Okay, great -- that set the property.  Now tell me why the HTML in the field is being completely ignored!!
0
 
rockiroadsCommented:
how are you setting the value
0
 
rockiroadsCommented:
u confirmed u got html from sql server?
0
 
wsturdevAuthor Commented:
When I open the Temp table straight in Access, I can see the HTML in the contents of the field.  When I select the field, copy it and go to Excel, for example, and paste it, it gets immediately interpretted and displayed as HTML should look.
0
 
Jeffrey CoachmanCommented:

AFAIK, you cannot simple "Display" raw HTML data:
    This is some <b> Bold </b> text.
...as "Formatted" HTML:
    This is some Bold text.
 
...in Access 2007 by simply changing the Format property.
What is also confusing is that it is not the "Format" property that controls this.
It is the new "Text Format" property that must be set to "Rich Text".
Futhermore, even if you try changing this property in temp the table, it still will not display raw HTML as formatted HTML.
Again, as far as I know you can only display formatted HTML in Access 2007 if you:
1. Have the datatype in the table set as Memo, and the "Text Format" property as "Rich Text".
2. Then as you edit the field data in datasheet view, you can actually format the text.
(You can also simply set the The TextFormat Property of the "control" in the form to "Rich Text" and edit the data as  formatted HTML in the form as well.

There may be a way to do this with raw HTML, but in the research I have done so far, I have not found a way straightforward.
I have had a few practical reasons for wanting to do this, so I am curious as well.

;-)

Thoughts anyone...?

JeffCoachman
 
0
 
Jeffrey CoachmanCommented:
wsturdev,

LOL,

Yes, this works fine in Excel and Word.
Why it is not straightforward in Access confuses me as well.

Jeff
0
 
rockiroadsCommented:
I see what you mean. Jeff is right in that it is raw data, it is just html which Access just sees as some data to hold in the db. Only way to view this is via some html renderer. At this point in time, and being a little unfamiliar with any new controls in A2007, if this was in A2003, I would save this html text in a temp file then use the web browser control to display it.

Say u had a form. The form_current event will keep overwriting some local file with the contents of that record. Then refresh the web browser as that url is pointing to this local file.
0
 
wsturdevAuthor Commented:
I think I just figured out I have SOME HTML displaying, just not all that I expected it to show.  I will have to explore what types of HTML will display and what won't.
0
 
rockiroadsCommented:
My reckoning is

Perhaps it works in Word and Excel as they are used as a kind of presentation material, if you know what I mean.

Access is a database, viewing thru tables is just a way to view the raw data. It would expect you to to define via forms to view the data in a format you want. Look at ole fields, you cant see jack in there but need a ole control on a form to view it.
0
 
Jeffrey CoachmanCommented:
... and why they gave this new property a "confusingly" similar name to an existing property is also befuddling.

... and why they did not at least put them next to one another in the property box, leaves me equally flummoxed.

rockiroads, are you similarly perturbed?
;-)

Or have you got a trick up your sleeve?

;-)

Jeff
untitled.JPG
0
 
Jeffrey CoachmanCommented:
rockiroads,

"I would save this html text in a temp file then use the web browser control to display it."

Sadly, I have had to do this on occassion as well.

I am gald to know that someone else feels my pain.
;-)

Jeff
0
 
rockiroadsCommented:
Jeff, havent used enough of A2007 for it to bug me. And Im used to MS's inconsistencies and idiocracies so always expect something not to be right.

Only reasoning behind adding new properties after the existing ones is maybe people use indexes to reference properties or the way properties are setup so helps upgrading existing databases
0
 
Jeffrey CoachmanCommented:
wsturdev,

You should have left this Q open for a few days
(remember some Experts actually go out party-ing on the weekends)
;-)

This way another Expert might wake up bright-eyed and bushy-tailed on Monday and post a solution.

If you want to reopen this, you can click the "Request Attention" button.

;-)

Jeff
0
 
wsturdevAuthor Commented:
I have requested the question be reopened.
And I do not even want to start discussing the issues I have dealt with over the last 2 years trying to convert my apps to 2007.  I am not doing it by choice -- someone at my company decided we all needed to upgrade but did not consider the current state of the products.
0
 
rockiroadsCommented:
No worries about reopening. Hopefully someone adds to this thread.
0
 
Jeffrey CoachmanCommented:
Yes,

Like I said, I am curious as well.
;-)

Jeff
0
 
Bob LearnedCommented:
Youuuuuu rannnngggg?
0
 
wsturdevAuthor Commented:
Yes, Lurch.
Where this stands is that SOME HTML displays, just not all that I expected it to show.
Can you give any guidance as to WHICH HTML will be correctly interpretted in an Access "Rech Text" format field?
0
 
Bob LearnedCommented:
There sure are a lot of comments, so let's revisit and verify what that means.

Are you asking how to convert HTML to RTF?   Or, are you asking how to display HTML in a Rich Text field?
0
 
wsturdevAuthor Commented:
How to display HTML in an Rich Text field.
I have HTML in a column in my database.  When I retrieve it into a Rich Text field on an Access form, some of the HTML is interpretted and some is not.
I have attached a zip archive with some sample HTML in it.  Unzip it and change the extension back to .html.  Then double click it and when it opens you will see the expected display.
When I take that HTML string and store it in my DB, then retrieve and display it in a Rich Text format field, I get:
A bold header with no blue color
A numbered list with no red numbers and no indenting

My-HTML-Data-2009-04-15.zip
0
 
Jeffrey CoachmanCommented:
TLO,

Just so we are all on the same page...

I am sure you are aware that in MSAccess 2007 Rich text Fields actually format text as HTML, ...not true Rich Text.


Any edicts, pronouncements, decrees, ...ect, that you may have, are eagerly anticipated.
;-)

JeffCoachman
0
 
rockiroadsCommented:
Im wondering whether its the standard of html that it supports. Perhaps the formatting set within the paragraph breaks eg <p style='margin-left:36.0pt;line-height:10.0pt'> or the embedded css that it is getting confused about.
If you went old school prior to css where you specified the formatting, how does it appear then?
0
 
wsturdevAuthor Commented:
I am not an HTML expert, and have only been working with it to the extant it is absolutely necessary, so I do not know what "old school" is.  If you could take what I have submitted and translate it to "old school" I will be happy to try it out.
0
 
rockiroadsCommented:
after the footy m8, I will give it a go. right now Im just briefly checking ee
0
 
Bob LearnedCommented:
Access 2007 only supports a very small subset of the full HTML specification.

Reference:

Microsoft Access Team Blog - Rich formatted text that includes data from fields
http://blogs.msdn.com/access/archive/2008/01/16/rich-formatted-text-that-includes-data-from-fields.aspx

<Quote>
For a number of reasons the rich text control doesn't support the full range of HTML but rather a limited subset of HTML focused around formatting text. If you stick to the list of HTML tags and attributes below you should be safe:</Quote>

<font face="Arial Black" size=3 color="#A5A5A5" style="BACKGROUND-COLOR:#FFFF00"> 
<strong> 
<em> 
<u> 
<br> 
<div align=right dir=RTL> 
<blockquote> 
<ol> and <li> for number 
<ul> and <li> for bulleted lists

Open in new window

0
 
Bob LearnedCommented:
I created a small test form that has a Plain Text control, and a Rich Text control, used the formatting toolbar buttons with the Rich Text control, and then saw how the HTML is generated in the Plain Text control.

Example:


<div><font size=4 color="#5C83B4"><strong><u>Title</u></strong></font></div>
 
<div>&nbsp;</div>
 
<ul>
 <li>Line 1</li>
 <li>Line 2</li>
 <ul>
  <li>Line 2a</li>
  <li>Line 2b</li>
 </ul>
 <li>Line 3</li>
</ul>

Open in new window

0
 
wsturdevAuthor Commented:
rockyroads -- are you in the UK?  "footy m8" sounds like a British-type reference to some sort of sports tournament.
TLO -- I went to the link you posted and found another quote I especially liked:
<QUOTE>Lastly, there is a quick way to generate the HTML you need and make sure it is compliant with the Rich Text control. Create a test table with one rich text control field on it. Create a simple form with two controls bound to the rich text field. Change one of the text boxes to Text Format = Plain Text. Now you can see the formatted version and the HTML.  </QUOTE>
0
 
wsturdevAuthor Commented:
TLO - Right on!
rockiroads - thanks for the clue about version
and boag2000 - thanks for the suggestion to reopen the question until after the holiday.
0
 
rockiroadsCommented:
yes wsturdev. football has now finished so I came back to do it but Im too late, TLO has done it. I see TLO has verified what I thought anyway, my first thoughts when I saw your html was the way it was created, it probably wasnt supported.

0
 
Jeffrey CoachmanCommented:
Lots of interesting info.

I'll look at it all when I get home.

;-)

Jeff
0
 
rockiroadsCommented:
wsturdev, this is not a moan about points but your original question was how to programmatically alter a column to handle html which was answered by my post earlier. Im wondering for PAQness sake whether you should of accepted that post also. I wouldnt worry about it now though, just thought, maybe next time eh.
0
 
wsturdevAuthor Commented:
rockiroads -- you are right about your point.  I hate these complex post threads.  I much prefer quick "ask question -- get answer" interchanges with one expert.  I did not go back far enough in the thread to find and evaluate the responses.
I extend my apologies and wish I could make amends.
0
 
rockiroadsCommented:
u can by reopening but dont bother now, someone reading this in the future may be able to find the answer otherwise if not all posts read, the solutions accepted dont really match the question and they might get confused.
0
 
Jeffrey CoachmanCommented:
Yes,

I will even sacrifice my points the PAQ Gods, if it helps.

JeffCoachman
0
 
rockiroadsCommented:
Lol Jeff, I wasnt talking about points, but for those less experenced reading this, they may not find the right answer. Remember the audience of EE, not all are that technical. Even it it was accepted with 0 points its fine by me.
0
 
Jeffrey CoachmanCommented:
Yes, that is the advantage to being relatively high up on Maslow's Pyramid.
You are free to focus on the question and not the points.
;-)
0
 
wsturdevAuthor Commented:
In the interest of a proper question-answer chain, I will be happy to make whatever adjustments you folks deem proper.  Please guide me.
0
 
rockiroadsCommented:
lol Jeff
By the way, I was checking out your website and your animation on the home page. It is a good thing my mind isnt filthy and I can see the drill clearly otherwise I would be thinking that bloke is doing something reserved for adult sites ;)
0
 
Jeffrey CoachmanCommented:
"One Day" I'm gonna update that site!
;-)
0
 
Jeffrey CoachmanCommented:
Hmmmmm...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 17
  • 13
  • 12
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now