[AccessD] FW: Removing quotes

Rocky,

    It is nice to note that your problem stands resolved. However, you might like to test the subroutine named P_ClearOuterQuotes(), as given below and confirm the outcome. It is expected to be very fast, as no looping through any recordset is involved.

    This routine builds and executes an update query. While doing so, the field names are also cleared of outer quotes if any. The update query takes care of field values, strips the outer quotes and replaces any inner ones by the word " In", using function Fn_ClearOuterQuotes(), also given below.

    Typical syntax for calling this procedure would be as follows (T_Imported is the assumed name of table containing imported data, while ID is the name of primary key field that gets inserted by Access):

    P_ClearOuterQuotes "T_Imported", "ID"

    The last argument is a comma separated string having names of fields desired to be excluded from corrective action. If the table does not have any primary key field and there are no fields desired to be excluded from corrective action, the last argument would get replaced by zero length string as follows:

    P_ClearOuterQuotes "T_Imported", ""

Best wishes,
A.D.Tejpal
---------------

P_ClearOuterQuotes()
================================
Sub P_ClearOuterQuotes(ByVal _
                TableName As String, ByVal _
                ExcludedFieldsList As String)
    ' TableName is the name of table containing
    ' imported data
    ' ExcludedFieldsList is a comma separated
    ' list of field names not to be subjected to
    ' removal of outer quotes.
    ' THIS MUST INCLUDE THE NAME OF
    ' PRIMARY KEY FELD if any, and any other
    ' fields desired to be excluded. If there is no
    ' excluded field, simply put a zero length string
    Dim Qst As String, Fnm As String
    Dim Cnt As Long, Txt As String
    Dim BaseLength As Long
    Dim tdf As TableDef, fld As Field
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set tdf = db.TableDefs(TableName)
    ' Note - If tdf is set simply against CurrentDb,
    '            the object is not found persistent.
    
    ' Build update query
    Txt = "UPDATE " & TableName & " SET"
    BaseLength = Len(Txt)
    For Each fld In tdf.Fields
        ' Clear outer quotes from field name
        Fnm = Fn_ClearOuterQuotes(fld.Name)
        fld.Name = Fnm
        If InStr(ExcludedFieldsList, Fnm) > 0 Then
        Else
            ' If field name is not on excluded list,
            ' include in update query
            Txt = Txt & " " & Fnm & " = " & _
                    "Fn_ClearOuterQuotes(" & _
                    Fnm & "),"
        End If
    Next
    
    CurrentDb.TableDefs.Refresh
    ' Note - CurrentDb preferred over db so as
    '            to get the latest instance
    
    If Len(Txt) > BaseLength Then
        Qst = Left(Txt, InStrRev(Txt, ",") - 1) & ";"
    
        ' Execute update query, clearing outer quotes
        CurrentDb.Execute Qst, dbFailOnError
    End If    
  
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub
================================

Fn_ClearOuterQuotes()
================================
Function Fn_ClearOuterQuotes(ByVal _
                FieldValue As Variant) As Variant
    Dim Rtv As Variant, Txt As String
    Dim Pfx As String
    
    Rtv = FieldValue        ' Default
    Txt = Rtv & ""
    Pfx = Left(Txt, 1)
    If Pfx = Chr(34) Or Pfx = Chr(39) Then
        If Right(Txt, 1) = Pfx Then
            Rtv = Mid(Txt, 2, Len(Txt) - 2)
            ' Replace any inner quotes by " In"
            Rtv = Replace(Rtv, Chr(34), " In")
            Rtv = Replace(Rtv, Chr(39), " In")
        End If
    End If
    
    Fn_ClearOuterQuotes = Rtv
End Function
================================

  ----- Original Message ----- 
  From: Rocky Smolin at Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, June 01, 2007 19:53
  Subject: Re: [AccessD] FW: Removing quotes


  Dear list:

  In case anyone might find it useful, here are the three routines I wrote to clean the quotes from the db.  

  The first routine strips the quotes from the field names which came in quote delimited from the import wizard. 

  Private Sub cmdStripQuotes_Click() strips the leading and trailing quotes from any field that has them.  

  Finally, Private Sub cmdReplaceQuoteWithIn_Click() replaces all occurrences of quotes with 'in.' in the description field.

  Any suggestions for refinements welcome.  The routines work but could probably be optimized.  The table had 18,000+ records for a test, but the real data will have hundreds of thousands so quicker is better.


  BTW the table contains 104 fields.

  Thanks and happy Friday,

  Rocky

  <<SNIPPED>>