MS Access – Improved HTML

Here’s yet another long standing demo I’ve been wanting to provide the community for a couple years now.

Origins

Several years ago I developed an e-mailing database.  I originally started out using the Rich Text Format (RTF) provided by Access, but anyone that gets into anything remotely advanced will quickly find out the Microsoft’s implementation falls very short of what is considered proper HTML or nor do RTF fields provide all the bells an whistles you’d expect in any modern tool.

For instance, with RTF, you can’t do simple things like:

  • Justify text
  • Insert links
  • Insert tables
  • Insert images/media
  • Format text
    • Strikethrough
    • Superscript
    • subscript
  • Use predefined formats (p, h1, h2, h3, …)
  • and the list goes on, and on, and …

Then add to that Microsoft doesn’t provide a way to view the raw source code behind the RTF fields and you need to resort to coding to access such information.

The Solution

Instead of trying to reinvent the wheel, I figured that there had to be a way to leverage an existing tool.  So I turned to the web as they have numerous great WYSIWYG (What You See Is What You Get) Editors.

In my demo, I decided to illustrate how easy it can be to able to utilize the TinyMCE WYSIWYG Editor, one of the most popular editors out there.

As you can see by the image below, it is possible to integrate the editor seamlessly within an Access form and provide the full power that HTML has to provide and generate compliant HTML that then will work for other purposes!

and the TinyMCE editor visually creates the content as you wish, but behind the scenes generates HTML such as

<hr />
<p><a href="http://www.cardaconsultants.com" target="_blank" rel="noopener">Another Access demo database from CARDA Consultants Inc.</a></p>
<h1>This is a Header</h1>
<h2>This is a Medium Header</h2>
<p>This is a new paragraph!</p>
<p><strong>This is a new paragraph!</strong> <br /><strong><em>This is a new sentence without a paragraph break, in bold italics.</em></strong></p>
<hr />

which is what is then stored in your database as plain text.

Final Thoughts
Once again, within a day, I was able to develop FAR superior HTML support within Access. So one has to ask why Microsoft ignored years of such requests (both private and public) when a home developer, with limited time & resources, could do it in under a day!

I know, I’m starting to sound like a broken record, but this clearly proves it can be done (with relative ease at the end of the day) and that “where there’s a will, there’s a way!”

Moral of the story, don’t hold your breath for Microsoft to fix things or implement the features we want and need. Instead, do it yourself!

And can someone please tell what the point of RTF is. A half-way baked idea between plain text and proper HTML. Either do things right, or not at all. Don’t give us crippleware solutions.

I hope this proves useful to some of you out there.

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Important!
If you are using the Feature Browser Emulation hack TinyMCE appears to have an issue is emulating Edge. Thus, keep your emulation to IE11 or less to avoid any issues.
Download “Access - Improved HTML” Access_Proper_HTML.zip – Downloaded 10767 times – 614.95 KB

Version History

Version Date Changes
V1.000 2019-10-10 Initial Release
V1.001 2019-10-10 Added missing Font Size and Font Family Selection
V1.002 2019-10-15 Added code to dynamically populate Font selection with currently available fonts from the local PC (instead of a predefined set listing)
V1.003 2019-10-16 MAJOR update
Remove Browser Emulation hack as it isn’t required for this javascript library
Code cleanup
Fixed issue with images
Fixed incompatibility with Edge emulation
V1.004 & 1.005 2022-12-07 Fixed issue with 64-bit compatibility arising from getting the system font listing that was occurring in Access 2019+ installations

Other References on the Subject

TinyMCE

36 responses on “MS Access – Improved HTML”

    1. Daniel Pineault Post author

      Quite right you are. The demo database was done on a 32-bit installation, so you would need to make the necessary adjustments to the APIs to make it compatible. I’m not going to release a x64 bit version of V1.003 as I am almost finish with a new version (and I will do my best to make that one compatible), but to fix V1.003 it is very simple! You need only edit the mod_Fonts module and change

      Declare Function EnumFonts Lib "gdi32" Alias "EnumFontsA" (ByVal hDC As Long, ByVal lpsz As String, ByVal lpFontEnumProc As Long, ByVal lParam As Long) As Long
Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

      to

      #If VBA7 Then
    Declare PtrSafe Function EnumFonts Lib "gdi32" Alias "EnumFontsA" (ByVal hdc As LongPtr, ByVal lpsz As String, ByVal lpFontEnumProc As LongPtr, ByVal lParam As LongPtr) As Long
    Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hdc As LongPtr) As Long
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#Else
    Declare Function EnumFonts Lib "gdi32" Alias "EnumFontsA" (ByVal hDC As Long, ByVal lpsz As String, ByVal lpFontEnumProc As Long, ByVal lParam As Long) As Long
    Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
    Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long
    Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)
#End If

      It will now work in both x32 and x64 versions of Access.

    2. vlad gon

      Unfortunately, it doesn’t work for me. I am using TinyMCE editor version 5.11. There are new features that I would like to use. There are none in the 4th version of the editor.

      1. vlad gon

        I’ll add it. The editor just doesn’t show up.
        In version 4, with the same files, everything works.

        1. vlad gon

          Thanks a lot Daniel. The emulation through the registry works fine. And I was able to use the 5th version of TinyMCE.

  4. Jack Stockton

    Not working for me even with the updated API calls on Win10 with O365 Access 64bit Version 2004 (Build 12730.20024).

    App immediately crashes creating a backup file before the HTMLEditor form is displayed.

    The form OnLoad event
    Call Test_TinyMCE_EnumFonts, then crashes on
    EnumFonts GetDC(Application.hWndAccessApp), vbNullString, AddressOf TinyMCE_EnumFonts, 0

    If you don’t run the Test_TinyMCE_EnumFonts function, the app appears to work just fine.

    1. Daniel Pineault Post author

      Not sure what to say. I just downloaded the zip file into my 2010 x64 VM, updated the API Declarations and compacted the db and it worked just fine. Sadly, I don’t have any other x64 VMs to test things, but it should work fine unless MS has changed things to break compatibility.

  5. Rob Koelmans

    Thanks, Daniel. Very interesting. I haven’t looked in detail but I’ like to have this editor run as a website that can be run in an HTML-iFrame. We store html-templates in an AWA and have Flow replace placeholders with the data from the record the user is on in an AWA-form. The result is fast responding html-pages for millions of records (depending how many records are in the database. We just take any HTML from WordPress etc, construct the tables and queries for the placeholders and have a new data-oriented website in just a couple of minutes but offering a simple html-editor within AWA for creating a template would complete it more as web content management system.

    Can it simply be deployed as website as far as you can tell?
    Kind regards
    Rob

    1. Daniel Pineault Post author

      The webpage being displayed is 100% standard html so it could be ported to any website. Actually the WYSIWYG Editor I used is a very common one used in thousands, if not millions, of website already.

      1. Rob Koelmans

        Thanks, we’ll try to implement it in a website and have AWA load and store the HTML tekst from the record that it’s on.

  6. abood

    really thnx
    using ur solution and string builder class for VBA, I create a memo DB, now my stuff can export a fully formatted document with holly Tables :), in HTML and doc file extension.

    ‘==================================================================================
    Public Sub ExportFile(FolderDirection As String, FileName As String, FileExtention As String, HtmlBody As String)
    Dim fs As Object, a As Object
    Dim FullDir As String
    FullDir = FolderDirection & “\” & FileName & FileExtention
    Set fs = CreateObject(“Scripting.FileSystemObject”)
    Set a = fs.CreateTextFile(FullDir, True)
    a.WriteLine (HtmlBody)
    a.Close
    End Sub

    ‘============================================================
    https://codereview.stackexchange.com/questions/67596/a-lightning-fast-stringbuilder

  7. David H

    Daniel,

    This is fantastic—thank you for sharing it. I do have two questions. First, is there anything I can do about the cursor? Whenever I move my mouse within the text editor area, the cursor rapidly flickers between the traditional “I-beam” text-editing cursor, a loading wheel, a pair of thick horizontal lines, and something red and white (maybe the MS access icon? It changes too quickly to say.). It’s a bit distracting.

    Second, there is a similar (commercial) solution that allows you to paste images directly into the text editor. The images are then encoded as blobs and stored in the HTML field itself. Do you have any suggestions for how to add that functionality? (Another option might be to set up a directory for images when you configure the HTML editor. Pasting an image into the editor would create a corresponding image file and insert the HTML to embed that file. Deleting that HTML and updating the record would delete the corresponding file. Either way I would need to figure out how to accept and process pasted images.)

    1. Indigo

      Hello David,

      Regarding your first question, I have the same problem and I was able to reduce it by setting the Screen.MousePointer value to anything other than 0 (= default). See https://docs.microsoft.com/en-us/office/vba/api/access.screen.mousepointer

      The best approach is to use Got/LostFocus to set it:

      Private Sub WebBrowser_GotFocus()
      ‘ Avoid flickering of mouse cursor
      Screen.MousePointer = 1
      End Sub
      Private Sub WebBrowser_LostFocus()
      ‘ Restore default cursor
      Screen.MousePointer = 0
      End Sub

      Regarding your second question, I was able to get image past as inlined-base64 HTML using CKEditor 4 and the pastebase64 plugin (because I’m way more familiar with this editor). Such a feature should also exists in TinyMCE I would assume.

      Good luck

  8. andreas

    hello daniel, really a good tool that you have made. however, the database crashes when I use an apostrophe in the text, for example “it’s”. according to google tinymce has a problem with this, is there a workaround?

  9. Naphta

    Hello

    Very good work , perfect
    thank you Daniel from another Daniel

    in : Function fixHTML(vInput As Variant) As String
    I just add this
    fixHTML = replace(fixHTML, Chr(39), “’”)

    Regards

  10. Jack Stockton

    Demo app is not working with Access 2021. Made the change for 64bit Access. Now when the app opens in immediately shuts down.

    I have the emulation mode set correctly, because your newer Access – Google Maps Basics demo is working. In a Web Browser control, https://whatismybrowser.com reports Edge on Windows 10.

    1. Daniel Pineault Post author

      No clue. It works fine in 2010, but bombs in 2019+. Something has obviously changed between versions, but I have no way of knowing what. You could try integrating another WYSIWYG editor.

      It doesn’t even report an error, just crashes.

    1. Daniel Pineault Post author

      After doing some more testing, it would appear that TinyMCE (at least the version implemented) is incompatible with the Web Browser’s emulation of Edge. Thus, you need to ensure that you are using emulation of IE11, or less and then it works fine.

      As for the reporting of Javascript, I can say for sure, but at one point I needed to implement https://www.devhut.net/everything-you-never-wanted-to-know-about-the-access-webbrowser-control/#Local

      I had looked into updating the MCE to the latest version (6…), but it doesn’t work in IE, and emulation of Edge is sketchy at best and doesn’t support many features Edge is supposed to support, so I have left it as is for now, as it works fine for my clients as is. But feel free to try updating it if you would like, testing different emulation mode, … What I provide is a proof of concept you are free to take it from there to suit your needs and as an MVP I’m sure you are more than capable.

    1. Daniel Pineault Post author

      Not sure. I do know based on user group demonstrations that it will require rewriting a lot of existing app to be able to port them into the new browser. I had hoped they would have made the process seamless, but that won’t be the case. I fear that will cause lots of problems, for many developers, but as always time will tell.

  13. Marcus Dieterle

    Thank you for this great tool. It’s quite helpful.
    Is there a way to get the source code directly without using the button and copy it by hand? That would be really great. Like displaying it in a textbox.

    Marcus

    1. Daniel Pineault Post author

      I just tried it out, but sadly I just get a blank screen? Not sure I’m going to put any time into troubleshooting at this point in time though.

  16. Bruce Jonson

    Hi Daniel,
    Amazing stuff!
    I want to use this for an in-database help system.
    But the I can’t work out is… I need to have a Read Only view
    Your amazing demo is Read/Write
    e.g I want most users to see the content RO
    and then based on security, SOME edit users can then open the content in a RW form (your version)
    help I can find says
    readonly: 1,
    But I can’t work out the syntax to add it to your index.html

    Thanks in advance
    Bruce

  17. Axel

    Hello Daniel,
    A huge thank you for this solution.
    It is exactly what I was looking for and it works perfectly for me out of the box with MS Access 2021 on Windows 10.

    The only thing that I did not manage to do is regarding the video files.
    What I would like to achieve is
    – to create a link (href…) to open a mp4 video file that would be on my local drive,
    – and/or to embed this local file directly in the editor.
    Do you think you could help me with this?

    And by the way, I am in Montréal as well. 😉
    Axel.

  18. Harris

    Hi Daniel,
    Is it possible to display the result in a Report?
    I tried, but being a novice, could not get it to display correctly.
    Any advice will be great, thanks!