Removing a character from anywhere on a table

[scottetombleson]

Is there any way to delete all occurrences in any field of an apostrophe in a table? I'm specifically looking for this to happen before a large import from a MySQL db into an access db.

Thanks for your help in advance.

 

[Golom]

You could try something like

UPDATE tbl SET
    Fld1 = Replace ( Fld1, "'", "" ),
    Fld2 = Replace ( Fld2, "'", "" ),
    Fld3 = Replace ( Fld3, "'", "" ),
    etc.

This works in Access (i.e. AFTER the import). I'm not familiar with mySQL so I don't know if there's an equivalent of the Replace function there.

 

[raskew]

The following procedure will find and replace instances of a particular string within all text and memo fields in a specified table. Written for A97 (which lacks the Replace() function). However, it should work in later versions. You'd do well to test thoroughly with a copy of your imported table since, contrary to the example shown, there'd be no reversal of the process when replacing a specified character with "".

HTH - Bob

Public Sub ReplaceOmatic(pTable As String, _
                         pString As String, _
                         pRepwith As String)
'*************************************************
'Purpose:  Search and replace for all text
'          and memo fields in specified table
'Coded by: raskew
'Arguments:
'          ptable:   Table to search
'          pString:  String to replace
'          pRepWith: Replacement string
'To call: from debug window:
'         call replaceomatic("Products1","o","~'~")
'To recover:
'         call replaceomatic("Products1","~'~","o")
'*************************************************

Dim db       As DATABASE
Dim rs       As Recordset
Dim td       As TableDef
Dim strSQL   As String
Dim namefix  As String 'contents of namehold
Dim namehold As String 'field name
Dim typeHold As Integer 'field type
Dim lenhold  As Integer 'field size
Dim i As Integer, n As Integer
Dim j As Integer, k As Integer, l As Integer
Dim lefthold As String, righthold As String

    Set db = CurrentDb
    Set td = db.TableDefs(pTable)
    
    'loop through the table's fields, select text or memo fields
    i = td.Fields.Count
    
    For n = 0 To i - 1
        If td.Fields(n).Type = dbText Or td.Fields(n).Type = dbMemo Then
            'dbText = 10; dbMemo = 12
            namehold = td.Fields(n).Name
            lenhold = td.Fields(n).Size
            typeHold = td.Fields(n).Type
    
            strSQL = "SELECT " & namehold & " FROM " & pTable & " WHERE" _
                 & " Instr([" & namehold & "], """ & pString & """)>0;"
            Set rs = db.OpenRecordset(strSQL)
            l = 0
            'test for empty record set
            If Not rs.BOF Then
                rs.MoveLast
                rs.MoveFirst
                l = rs.RecordCount
            End If
            If l > 0 Then
                Do While Not rs.EOF
                    j = Len(pString)
                    namefix = rs(namehold)
                    Do While InStr(namefix, pString) > 0
                        k = 0
                        k = InStr(namefix, pString)
                        lefthold = Left(namefix, k - 1) & pRepwith
                        righthold = RTrim(Mid(namefix, k + j))
                        namefix = lefthold & righthold
                    Loop
                    'test for field length
                    If typeHold = 12 Or Len(namefix) <= lenhold Then
                        '12 = memo field
                        rs.Edit
                        rs(namehold) = namefix
                        rs.Update
                    End If
                    rs.MoveNext
                Loop
            End If
    
        End If
    
    Next n
    
    rs.Close
    db.Close
    Set db = Nothing

End Sub

 

[Eupher]

Bob,

Dim db As DATABASE

???

;-)

Ken S.

 

[Golom]

Eupher
Database is a legit DAO object although it may be better to use

Dim db   As DAO.Database

Just for the sake of being explicit.

 

[raskew]

Ken & Golom -

Ken -
Dim db as DATABASE?
Published prior to your explanation showing how to make this annoying issue go away.

Golom -
I agree! Working in a strictly A97 environment, it isn't much of an issue. However, in a multi-version environment such as this forum, failing to specify could lead to problems. Thanks for pointing it out!

Best wishes, Bob

 

[Eupher]

Hi, Golom,

My post was an inside joke to Bob - in another thread, he posted a question about an irritating issue in which, no matter what case he typed it in code, the word "database" automatically switched to all uppercase.

Ken S.