none
Retrieving & displaying Rich Text from DB2 BLOB fields RRS feed

  • Question

  • OK. So I already know that, in order to format a text box as Rich Text, the underlying data source must be a Memo field (Long Text in Access 2013).

    But I need to retrieve and display some DB2 BLOB fields, which I believe roughly correspond to Long Text. When I make my ODBC connection to the table, though, the BLOB fields appear formatted as Short Text. And of course, because it is an ODBC-linked table, I cannot change the type.

    I go into design view of my query, right-click anywhere in the field, go to Properties, and set Text Format to Rich Text. But even then, I cannot change the type of a text box bound to this field from Plain Text to Rich Text.

    So I suspect this probably has something to do with some ODBC DSN parameter that must be configured to recognize BLOB fields as Long Text. But I can find no option that seems related.

    Or do I have to open the connection and pull the data programmatically using an ADO connection in VBA?

    Note that these are not pictures embedded in the field. It is just text with formatting: bold, underline, different font colors, etc.
    Friday, December 4, 2015 9:45 PM

Answers

  • The goal, as per my original post, is to programmatically obtain RichText directly from DB2 via the ODBC driver. Trying to automate an export from DB2 to Excel, then importing the Excel file into Access--also programmatically from within VBA in Access, would be a nightmare.

    I suspect that recognition of BLOB fields as Rich Text is simply not part of DB2's ODBC driver, even though the actual field content in DB2 is entirely RTF-compliant (as proven by a manual copy and paste).

    So I will post the question to a DB2 forum.

    But thank you!

    Thursday, December 10, 2015 5:42 AM

All replies

  • How is the formatting entered in the DB2 attribute? It could be Microsoft's RTF, html or perhaps some other formatting standard. I seem to remember an Access rich text box only works with RTF. You can display html-formatted text with a Microsoft Web Browser ActiveX control. If you're not sure what the format is, show us a short sample piece of stored text.

    Paul

    Saturday, December 5, 2015 12:58 PM
  • I am pretty sure this is MS RTF. In fact, if I save the text below as an .rtf file, it opens, correctly formatted, in WordPad. Here is the first section of content from the BLOB field, as extracted directly as text by query within DB2. But how do I get Access to recognize the correct field type? When I connect via ODBC, the field is automatically recognized as Short Text.

    {\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 Tahoma;}{\f1\fswiss\fprq2\fcharset0 Tahoma;}{\f2\fswiss\fprq2\fcharset0 Calibri;}{\f3\froman\fprq2\fcharset0 Times New Roman;}{\f4\fscript\fprq2\fcharset0 Brush Script MT;}{\f5\fmodern\fprq1\fcharset128 MS Gothic;}{\f6\fswiss\fprq2\fcharset0 Arial;}{\f7\froman\fprq2\fcharset0 Bookman Old Style;}{\f8\froman\fprq2\fcharset2 Symbol;}{\f9\fnil\fcharset0 Calibri;}}
    {\colortbl ;\red0\green0\blue0;\red31\green73\blue125;\red0\green0\blue255;\red112\green48\blue160;\red255\green0\blue0;\red54\green95\blue145;\red0\green176\blue80;\red0\green84\blue160;\red0\green64\blue128;\red0\green0\blue128;\red0\green0\blue160;}
    \viewkind4\uc1\pard\ltrpar\cf1\lang1033\f0\fs16 Dec 04,2015  5:28 PM    UserID
    \par \cf0\b\f1\fs20 From:\b0  SenderName [mailto:sender.name@domain.com] 
    \par \b Sent:\b0  Friday, December 04, 2015 2:13 PM
    \par \b To:\b0  Recipient Name
    \par \b Cc:\b0  CC Name
    \par \b Subject:\b0  Re: Your Account
    \par \f2\fs22 
    \par \cf2 Hi Recipient,
    \par 
    \par I am pretty sure so-and-so will have a remittance record available Monday. 
    \par 
    \par SenderName 
    \par Sales Account Manager 
    \par Sender Company 
    \par 000.000.0000 Office 
    \par 111.111.1111 Mobile 
    \par 222.222.2222 Fax 
    \par \cf3\ul sender.name@domain.com <mailto:sender.name@domain.com>\cf0\ulnone\f3\fs24 
    And here is the formatted output within DB2 or WordPad (except everything got double-spaced when I copied and pasted here):

    Dec 04,2015  5:28 PM    UserID


    From:

    SenderName [

    mailto:sender.name@domain.com]


    Sent:

    Friday, December 04, 2015 2:13 PM


    To:

    Recipient Name


    Cc:

    CC Name


    Subject:

    Re: Your Account



    Hi Recipient,

    I am pretty sure so-and-so will have a remittance record available Monday.

    SenderName

    Sales Account Manager

    Sender Company

    000.000.0000 Office

    111.111.1111 Mobile

    222.222.2222 Fax


    sender.name@domain.com <

    mailto:sender.name@domain.com>

    Saturday, December 5, 2015 2:26 PM
  • OK, that's definitely RTF text. I'm sorry, but I don't know how to display it in Access. Hopefully you can find something searching on the web. Or maybe try an OLE ActiveX control and see if Word can display it on your form. I think Access is using html for what they call rich text formatted textboxes now, so that wouldn't display correctly. Maybe there are also RTF -> html converters, and then you could use a web ActiveX control to display the converted html?

    Paul

    Saturday, December 5, 2015 4:11 PM
  • I am pretty sure this all has to do with the ODBC driver. Access sees the field as Short Text instead of Long Text. I just though some other Access junkie might have dealt with this before and found an ODBC parameter that would allow Access to see the BLOB fields as Long Text (which, I  believe, would allow RTF viewing).
    Saturday, December 5, 2015 4:45 PM
  • Before you go down that route you might want to do a simple test. Create an Access database with a table containing a Memo/Long Text field set to rich text. Load your sample data value and see if it can be displayed correctly.

    Paul

    Saturday, December 5, 2015 8:03 PM
  • Good thought, although I need a better definition of "load" to test exactly.

    If I do a query directly in DB2, then copy and paste the content of the BLOB field into a blank document and save it as an .rtf file, I can open it in WordPad. In WordPad, it appears as formatted text:

    Dec 04,2015  5:28 PM    UserID

    From:SenderName [mailto:sender.name@domain.com]
    Sent: Friday, December 04, 2015 2:13 PM
    To: Recipient Name
    Cc: CC Name
    Subject: Re: Your Account

    Test #1: copy all and then paste from WordPad into a RT-formatted text box bound to a Memo field in Access. All appears exactly as it does above (all text is formatted).

    Test #2: When I then look at the table field directly (i.e. not through the control on the form), I get this:

    <div><font face=Tahoma size=1 color=black>Dec 04,2015 &nbsp;5:28 PM &nbsp;&nbsp;&nbsp;UserID</font></div>
    
    <div><font face=Tahoma size=2 color=black><strong>From:</strong> SenderName [<a
    href="mailto:sender.name@domain.com">mailto:sender.name@domain.com</a>] </font></div>
    
    <div><font face=Tahoma size=2 color=black><strong>Sent:</strong> Friday, December 04, 2015 2:13 PM</font></div>
    
    <div><font face=Tahoma size=2 color=black><strong>To:</strong> Recipient Name</font></div>
    
    <div><font face=Tahoma size=2 color=black><strong>Cc:</strong> CC Name</font></div>
    
    <div><font face=Tahoma size=2 color=black><strong>Subject:</strong> Re: Your Account</font></div>
    
    <div>&nbsp;</div>
    
    <div><font color="#1F497D">Hi Recipient,</font></div>
    
    <div>&nbsp;</div>

    Test #3: copy and paste from WordPad directly into the Access table. I then see this on the form:

    Dec 04,2015  5:28 PM    UserID From: SenderName [mailto:sender.name@domain.com]  Sent: Friday, December 04, 2015 2:13 PM To: Recipient Name Cc: CC Name Subject: Re: Your Account

    That is, it seems it pasted only the text (no formatting).

    Test #4: copy and paste directly this from the DB2 query results directly to the field in the table:

    {\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 Tahoma;}{\f1\fswiss\fprq2\fcharset0 Tahoma;}{\f2\fswiss\fprq2\fcharset0 Calibri;}{\f3\froman\fprq2\fcharset0 Times New Roman;}{\f4\fscript\fprq2\fcharset0 Brush Script MT;}{\f5\fmodern\fprq1\fcharset128 MS Gothic;}{\f6\fswiss\fprq2\fcharset0 Arial;}{\f7\froman\fprq2\fcharset0 Bookman Old Style;}{\f8\froman\fprq2\fcharset2 Symbol;}{\f9\fnil\fcharset0 Calibri;}}
    {\colortbl ;\red0\green0\blue0;\red31\green73\blue125;\red0\green0\blue255;\red112\green48\blue160;\red255\green0\blue0;\red54\green95\blue145;\red0\green176\blue80;\red0\green84\blue160;\red0\green64\blue128;\red0\green0\blue128;\red0\green0\blue160;}
    \viewkind4\uc1\pard\ltrpar\cf1\lang1033\f0\fs16 Dec 04,2015  5:28 PM    UserID
    \par \cf0\b\f1\fs20 From:\b0  SenderName [mailto:sender.name@domain.com] 
    \par \b Sent:\b0  Friday, December 04, 2015 2:13 PM
    \par \b To:\b0  Recipient Name
    \par \b Cc:\b0  CC Name
    \par \b Subject:\b0  Re: Your Account

    This results in the form simply displaying the raw .rtf code & text exactly as shown above (no formatting applied)--exactly as it appears when extracted from DB2 and as it appears in the table.

    Sunday, December 6, 2015 6:14 AM
  • Hi Brian,

    >> Retrieving & displaying Rich Text from DB2 BLOB fields

    I think we could not achieve your requirement directly. Based on your description, rich text value are stored as “{\rtf1\fbidis\ansi\” which is string in DB2, but in Access, you could check the value stored in long text. It has shown the formatted value like “Test”. It would not work if you copy the value from DB2 to Access.

    For a workaround, I suggest you convert the rtf string to html string, and then set the html string to rtf control.

    For converting rtf to html, you could refer the link below:
    # Convert rtf format to html format
    https://social.msdn.microsoft.com/Forums/office/en-US/141fb4cb-5dd8-4b01-bd83-8146a05cf54f/convert-rtf-format-to-html-format?forum=accessdev

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, December 7, 2015 6:40 AM
  • As I first noted, the problem is more basic than that: when I actually open the field in the linked table in Access, I do not get the same thing as I get when I query directly in DB2. This is undoubtedly because Access automatically configured the field as Short Text, not Long Text, so all I see is a string of alphanumeric characters, and only 255 of them. The ODBC driver is not making the BLOB field available within Access as Long Text. So I return to my base question, which is how to get the ODBC driver to present a BLOB field as Long Text.

    That is, if I am getting only the first (or last) 255 characters of a much longer string of data, I cannot even get as far as determining the .rtf format, converting to html, or anything else.

    I know that, technically speaking, this is a DB2 question, since DB2 is responsible for providing the ODBC driver. But I am pretty sure that if I ask the question on a DB2 forum, I will be told that the question is Access-specific (which is also true). I asked the question here, then, in case anyone had prior experience with ODBC drivers, DB2 or other, that needed to have a parameter set in the DSN in order to recognize BLOB fields as Long Text.

    This is the problem I have every time I run into a non-Microsoft ODBC question from within Access: the general users of the DB platform do not understand Access, and the Access forum tends to be a better place to post the ODBC/Access questions. But I do often have to work my way back to the base question like this.

    Monday, December 7, 2015 4:16 PM
  • Hi Brian,

    >> which is how to get the ODBC driver to present a BLOB field as Long Text.

    As you have known, we have no ways to set it while linking the table. Without DB2, I failed to test this situation, I suggest you try to export the table from DB2 to Excel file, then import Excel file into Access database to check the data in table.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 9, 2015 8:28 AM
  • The goal, as per my original post, is to programmatically obtain RichText directly from DB2 via the ODBC driver. Trying to automate an export from DB2 to Excel, then importing the Excel file into Access--also programmatically from within VBA in Access, would be a nightmare.

    I suspect that recognition of BLOB fields as Rich Text is simply not part of DB2's ODBC driver, even though the actual field content in DB2 is entirely RTF-compliant (as proven by a manual copy and paste).

    So I will post the question to a DB2 forum.

    But thank you!

    Thursday, December 10, 2015 5:42 AM