Reading in bizarre characters from Word and saving in Access RRS feed

  • Question

  • My experience with Access is limited, so any help you can offer me would be much appreciated.


    I'm writing a code to import text stored in a table in a Word document and save it into an Access database. Here is how the text is retrieved (sample line):


    tempString = Trim$(Left$(oDoc.Tables(BodyTable).Cell(i, j).Range.Text, Len(oDoc.Tables(BodyTable).Cell(i, j).Range.Text) - 2))


    This tempString is then saved directly into the database table by making a RecordSet of this table and accessing the corresponding field's value, then updating:


    objRecordset.Fields("sample").Value = tempString


    The code works fine for normal characters (ASCII), but there are also "strange" characters in the documents, anything from degree symbols to square root symbols, as well as some GR&L characters (not from the standard fonts found in the character map). Whenever these are encountered, they are replaced with left parentheses, i.e. ( or even (((( both within the code (when I add a watch on tempString this is what I see) and within the data table. How can I scan these characters in and make them visible in the table and/or form that displays them?


    Just to give you a little more background, I have long been putting in this description as an OLE for this very reason. However, with more than 20,000 records in the database, you can imagine the kind of space that's taking up. And the idea is for the database to hold 150,000 records like these, so this is particularly worrying. I've been told to store the binary equivalent of the string in the table, and then convert back to text in the form, but can't find much documentation for this. Also, some of the strings I'm reading in are over 255 characters long, which is bit of a problem since this is the limit placed by Access on string storage in tables.


    Any suggestions or help you could provide would be appreciated.

    Thursday, June 26, 2008 2:31 PM

All replies

  • Take look at StrConv function with options:
    vbFromUnicode - returns Byte array from String
    vbUnicode - returns String from Byte array

    Tuesday, July 1, 2008 8:55 AM