ACC2000: Unexpected Characters Appear in Imported dBASE IV Database (210542)

RESOLUTION

1. In the Access 2000 database or project that contains the imported dBASE IV file, type or paste the following procedure in a new module:

   Option Compare Database
   Option Explicit
   
   Sub subMemoFix(strTblName As String, strFldName As String)
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       'This code requires a reference to the following object library:
       '
       '   Microsoft ActiveX Data Objects 2.x Library
       '
       'where 2.x is equal to 2.1 or higher.
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       Dim con As ADODB.Connection
       Dim rst As ADODB.Recordset
       Dim strOldMemo As String
       Dim strNewMemo As String
       Dim intStart As Integer
   
       Set con = CurrentProject.Connection
       Set rst = New ADODB.Recordset
   
       rst.Open strTblName, con, adOpenDynamic, adLockOptimistic
       rst.MoveFirst
   
       Do Until rst.EOF
           If rst(strFldName) <> "" Then
               strOldMemo = Trim(rst(strFldName))
               strNewMemo = ""
   
               For intStart = 1 To Len(strOldMemo)
                   If Mid(strOldMemo, intStart, 2) = (Chr(236) & Chr(10)) Then
                       intStart = intStart + 1
                   Else
                       strNewMemo = strNewMemo & Mid(strOldMemo, intStart, 1)
                   End If
               Next
   
               rst(strFldName) = strNewMemo
               rst.Update
           End If
           rst.MoveNext
       Loop
   
       rst.Close
       con.Close
       
       Set rst = Nothing
       Set con = Nothing
   
       Debug.Print
       Debug.Print "Done"
   End Sub
   					

2. To test this function, type the following line in the Immediate window

   subMemoFix "<tablename>", "<fieldname>"
   					

   where <tablename> is the name of the imported dBASE IV table and <fieldname> is the name of the Memo field, and then press ENTER.
   
   For example, for a table that is named CUSTOMERS that has a Memo field that is named NOTES, type:

   subMemoFix "CUSTOMERS", "NOTES"