ACC: Strange Characters Appear in Imported dBASE IV Database

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Strange characters, specifically characters Chr(236) and Chr(10), appear approximately every 65th character in a Memo field in an imported dBASE IV database or Paradox database.

CAUSE

These characters are used by dBASE IV so that its memo editor word wraps automatically. The dBASE IV memo editor has a fixed line length of 65 characters, and the mechanism is stored in the data so that the memo editor does not have to force the word wrap.

RESOLUTION

Use the following procedure and sample code to remove the Chr(10) and Chr(236) characters from the data. This procedure will not damage the data, and you can export the data back to dBASE IV later if you want to:

1. Create a module and type the following line in the Declarations section if it is not already there:

         Option Explicit
   

2. Type the following procedure:

         Sub DB4MemoFix (TableName as String, FieldName as String)
            Dim D As Database
            Dim t As Recordset
            'dim t As Dynaset in version 1.x
            Dim oldmemo As String
            Dim newmemo As String
            Dim i As Integer
            Set D = CurrentDB()
            Set t = D.OpenRecordset(TableName)
            'Set t = D.CreateDynaset(TableName) in version 1.x
            t.MoveFirst
            Do Until t.EOF
               If t(FieldName) <> "" Then
                  oldmemo = Trim(t(FieldName))
                  newmemo = ""
                  For i = 1 To Len(oldmemo)
                     If Mid(oldmemo, i, 2) = (Chr(236) & Chr(10)) Then
                        i = i + 1
                     Else
                        newmemo = newmemo & Mid(oldmemo, i, 1)
                     End If
                  Next
                  t.Edit
                  t(FieldName) = newmemo
                  t.Update
               End If
               t.MoveNext
            Loop
            D.Close
            Debug.Print
            Debug.Print "Done"
         End Sub
   
   

3. To test this function, type the following line in the Debug window, (or Immediate window in Microsoft Access versions 2.0 or earlier) and and then press ENTER.

         DB4MemoFix "<tablename>", "<fieldname>"
   

   where <tablename> is the name of the imported dBASE IV table, and <fieldname> is the name of the Memo field.

   For example, for a table called CUST with a Memo field called NOTES, you would type:

         DB4MemoFix "CUST", "NOTES"
   

MORE INFORMATION

dBASE IV is manufactured by Borland International, Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

Keywords          : kbinterop kbprg PgmHowTo IsmXbase
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : kbcode