Alter Table/Column to RTF

[Robin]

Is it possible to change an existing text field to Memo and specify the Text
Format to be Rich Text programatically?

I've tried various syntax but only receive errors or no result.

dbs.Execute "ALTER TABLE InvoiceDetail ALTER COLUMN OtherDescription
Memo;", RichText

This only changes the table from text to Memo, but Memo field is plain text.

....Alter Column OtherDescription MEMO Text Format Rich Text;"

gives syntax error as well as any other combination I've tried. Can't find
anything on google or in help regarding the "options" listed by the DLL
helper.

Perhaps I need another approach. I'm trying to create a Table Update
routine to add new fields to tables and edit the existing fields to take
advantage of Access 2007 new RTF feature.

Any suggestions would be welcomed.

Thank you,
Robin

 

[Allen Browne]

I would be very surprised if MS had updated JET's DDL to support this
property.

However, you can set the property in DAO. The example below creates a
table, with a memo field, and sets its TextFormat property to rich text.

Function CreateRichTextField()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

'Create the table with a memo field
Set db = CurrentDb()
Set tdf = db.CreateTableDef("_TestRichText")
Set fld = tdf.CreateField("NotesRichText", dbMemo)
tdf.Fields.Append fld
db.TableDefs.Append tdf

'Set the property of the field
Set prp = fld.CreateProperty("TextFormat", dbByte, _
CByte(acTextFormatHTMLRichText))
fld.Properties.Append prp

'Clean up
Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow
End Function

 

[Peter Hibbs]

Robin,

If you want some code to add new fields, tables, relationships, etc,
etc to the back-end file (you have split your database haven't you!)
then have a look at :-
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=185

It does not cater for Rich Text fields in A2007 but you may be able to
modify the code with Allen's suggestions to do what you want.

HTH

Peter Hibbs.

 

[Robin]

I am grateful for the information that you both have shared but still have
not been able to iron out either approach. I have tried to use the ube
solution by modifying it to add my TextFormat = RichText and can see it being
carried on as expected in the locals window but the change doesn't take
place, always remains plain text.

This is still an invaluable tool and see that besides Peter Hibbs, Allen
Browne was also a contributor. As I intend to distribute this project to
approximately 10 users when I am done please let me know if there is any
license that I need to purchase as I found nothing on the website where I
received the download.

So I tried to adapt the CreateProperty portion of the DAO example from Allen
Browne into the ube and received an error 3367 that I can't append as the
item already exists.

Went to using strictly the DAO example and of course to create the table,
field, and assign it to MEMO as RTF works fabulously. I've tried to adjust
it to just change the text format property as follows:

Function ChangeToRTF()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

'Create the table with a memo field
Set db = CurrentDb()
Set tdf = db.TableDefs("Customers")
Set fld = tdf.Fields("CallLetterText")
' tdf.Fields.Append fld
' db.TableDefs.Append tdf

' Set the property of the field
Set prp = fld.CreateProperty("TextFormat", dbByte, _
CByte(acTextFormatHTMLRichText))
fld.Properties.Append prp

'Clean up
Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow

End Function

However now I get the same Runtime error 3367 Cannot Append. Object with
that name already exists in the collection. The locals window indicates
invalid operation for prp.

I'm assuming the collection is refering to the TextFormat property rather
than the FieldName. I figure if I can pin down this aspect the I should be
able to adapt it into the ube code.

For what it's worth I've been to the local bookstore, another $100.00 for
refernce manuals ( library now at 45 ), 14 additional hours of
study/attempts. And still can't quite get this last piece. (of course Allen
and Peter have already practically given me the solution). I mention this
not for pity, as I've learned a great deal, but just to let you know I'm not
just hitting the keyboard with, "Oh I can't get it to work, what's next"

Or am I still trying to do something that isn't quite exposed? Can the DAO
code "change" the Text Format Property to RTF for existing fields and I'm
missing the syntax? Or will I have to create New Memo fields in order to set
Text Format as RTF?

Thank you,
Robin

 

[Peter Hibbs]

Robin,

There is no license required to use my BE update code, just use it as
you see fit.

Regarding the RTF field problem, are you saying that the code from
Allen works on its own but you cannot get it to work with the ube
code? If so I will have a look at it in the next few days but I cannot
promise a solution at this stage as I have not tried my code out on
A2007 yet (although I do have Office 2007).

Peter.

 

[Robin]

Peter,

The code that Allen provided creates the table, creates the field, creates
the property, and appends the rtf property. And this part works perfectly.
But I'm trying to only set the RTF property to existing Memo Fields. Simply
changing the TextFormat property to RichText.

I tried to incorporate into the ube what I thought was needed from Allen's
suggestion. When I didn't get the result I need or I get the error message.
I thought I would go back to basics and try to adjust Allen's example to
change the property rather than creating everything.

So from Allen's code I removed the Create portion of the code for the
TableDef and Field to use the existing TableDef and Field in the Current
Database. I've tried both the CreateProperty then Appending the property,
which gives the error that it already exists. And just setting the property
which does not change the property but I receive no error.

So I figure worst case scenario is if I can get Allen's DAO example to
change the TextFormat property then I can run Code after the ube creates the
new fields. But I'm pretty much certain that if it will run independently
then I should be able to adapt it to the ube.

Problem appears to be that I am not currently sophisticated enough, yet, to
change the DAO code to only change the property on existing fields. But God
knows I'm working on it.

Thank you,
Robin

 

[Robin]

Peter,

One more note. As I have been using the UBE for testing this TextFormat
problem. I have Vista Ultimate and Access 2007 set to the ADO Library. The
UBE has performed flawlessly on this platform thus far. In my BE update I
needed to add 4 tables, 100+ fields, changed the Field Type or property of a
couple dozen or so, input mask, field size, allowzerolength, etc.

I've done this at least 50 times, copying the old datatable in place and
running the UBE to see if my TextFormat fields are changing, and never a
glitch. Even when I know the "Can't Append" error is happening inside
nothing shows to the user ( I forgot to open my test database with the shift
key a couple times when I had the debug trigger set looking in the background
and UBE ran as the end user would see it)

Hope this info is helpful.
Thanks,
Robin