Solved

Alter Column to Rich Text Format

Posted on 2009-04-10
50
1,095 Views
Last Modified: 2013-11-27
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
Comment
Question by:wsturdev
  • 17
  • 13
  • 12
  • +1
50 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116401
does this sql work for you?

alter table <mytable> alter column <mycolumn> memo

memo field can hold rtf and html text
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24116446
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116473
ok, gotcha, sorry. Let me look again. Possibly a vba solution, would that be okay?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24116514
Yes, that is what I am looking for!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116577
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116701
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24116791
Okay, great -- that set the property.  Now tell me why the HTML in the field is being completely ignored!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116986
how are you setting the value
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24116993
u confirmed u got html from sql server?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24117112
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24117165

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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24117205
wsturdev,

LOL,

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

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24117208
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24117209
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24117229
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24117301
... 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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24117353
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24117385
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 24117391
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24117644
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24117749
No worries about reopening. Hopefully someone adds to this thread.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24119226
Yes,

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

Jeff
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24149580
Youuuuuu rannnngggg?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:wsturdev
ID: 24149852
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24151298
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24151586
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24151929
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 24152000
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24152074
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24152089
after the footy m8, I will give it a go. right now Im just briefly checking ee
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 300 total points
ID: 24152138
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24152152
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24152246
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
 
LVL 1

Author Closing Comment

by:wsturdev
ID: 31568935
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24152377
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24152383
Lots of interesting info.

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

;-)

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24152408
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24152566
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24152669
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24154208
Yes,

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

JeffCoachman
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24155271
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24156590
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
 
LVL 1

Author Comment

by:wsturdev
ID: 24156732
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24156789
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24163677
"One Day" I'm gonna update that site!
;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24174307
Hmmmmm...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now