title: How to convert a text field in an Access table to a rich text memo using VBA
body: <p>My users have a number of backend .accdb databases (which I can't access directly). I need to code some vba to modify the structure of some of the tables in these databases to convert Text Fields to Rich Text memos. (The fields already contain text including Access "rich-text" i.e. the relevant html coding).</p>
  
  <p>I need to:</p>
  
  <ol>
  <li>Modify the field to be a rich text memo.</li>
  <li>Modify the existing contents (if applicable) to display correctly as Access rich text in forms, datasheets and reports.</li>
  </ol>
  
  <p>I can write a SQl statement that will modify a field from TEXT (255) to MEMO:</p>
  
  <pre><code>ALTER TABLE tblSource ALTER COLUMN Detail1 MEMO
  </code></pre>
  
  <p>However, this leaves the resultant memo field as a plain text memo.</p>
  
  <p>I have considered creating a new Rich Text Field and then copying the contents of the old one (using a SQL CREATE TABLE statement followed by an UPDATE statement that applies the Plaintext function to the contents of the old field and then copies the result to the new field, and then further SQl to delete the old field and rename the new) but can't find out how to create a rich-text memo (default seems to be plain text).</p>
  
  <p>Extensive web searches haven't shown up any additional techniques I can deploy. This is a process that will be run once for each file, so it doesn't need to be elegant or quick but it does need to be bomb-proof!</p>