ACC: Unexpected Characters Appear in Imported dBASE IV Database

ID: Q99399

The information in this article applies to:

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

SYMPTOMS

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

Unexpected 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.

Additional query words:

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

Last Reviewed: November 21, 1998