replace function for the entire table....

[SamDeMan]

Hi all,

I am trying to perform the find/replace function using VBA that i would do when i open a table. so when i open a table in regular view i can go to the top menu and select Edit | Replace and then i get a dialog box that i can either search the whole table or just one field and other options.... i would like to this in VBA. instead of using ODBC and looping through all records i would like to use the replace feature (not the standard replace function) with out opening up the table as a recordset. is this possible. i looked around on the internet and i saw that this was already asked a while ago, but the answer was not positive. please help me out.

thanks,

sam

P.S.
here is the link (i know that Douglas steele is a great, but i am hoping for a miracle)
http://www.dbforums.com/archive/index.php/t-766427.html

 

[boblarson]

Just as an FYI for you - When you "open" the table and then manually use the replace function, what you are actually doing is opening a recordset and then the replace will essentially iterate through that recordset.

When you "open" a table in Access you are actually opening a query to the data. It is just an illusion that you are opening the table, much like what happens when you do something similar in SQL Server.

So, your desire to do something like that is really an illusion too, since you have to navigate via recordsets anyway you look at it.

 

[SamDeMan]

So, your desire to do something like that is really an illusion too, since you have to navigate via recordsets anyway you look at it.

i love your language, thanks a lot, it really clarifies things. i don't think i would have read this on microsofts' website. i hope everybody here in the groups gets to read this one.

thanks,

sam

 

[SamDeMan]

i am now writing the code, and i am noticing that the only way to do it is have a replace statement for each field. is there a shortcut? it means basically i will write some thing like this:

do while not rs1.eof
rs1("Field1") = replace(rs1("Field1"),"'","")
rs1("Field2") = replace(rs1("Field2"),"'","")
etc..
rs1.movenext
loop

is this the correct way?

thanks,

sam

 

[RuralGuy]

Is this just a one time operation on one table or are you going to be doing it often on many tables?

 

[ByteMyzer]

The following Sub will open up an ADODB Recordset in the Current Project for the specified table and perform a global Find/Replace on all text fields:

Public Sub tblReplace( _
    ByVal sTableName As String, _
    ByVal sFind As String, _
    ByVal sReplace As String, _
    Optional ByVal vCompare As VbCompareMethod = vbBinaryCompare)
On Error GoTo tblReplace_Error

    Dim rs As Object
    Dim fd As Object

    Set rs = CreateObject("ADODB.Recordset")

    rs.Open "SELECT * FROM " & sTableName, _
        CurrentProject.Connection, _
        adOpenForwardOnly, adLockOptimistic, adCmdText

    Do While Not rs.EOF
        For Each fd In rs.Fields
            Select Case fd.Type
                Case 8, 129, 130, 200, 201, 202, 203
                    If InStr(1, fd.Value, sFind, vCompare) > 0 Then
                        fd.Value = Replace( _
                            fd.Value, _
                            sFind, _
                            sReplace, , , vCompare)
                    End If
            End Select
        Next fd
        If rs.EditMode <> 0 Then rs.Update
        rs.MoveNext
    Loop

tblReplace_Exit:
    If Not (rs Is Nothing) Then
        rs.Close
        Set rs = Nothing
    End If

Exit Sub

tblReplace_Error:
    If Err.Number = -2147217887 Then
        DoEvents
        Resume
    Else
        MsgBox "Run-time error '" & Err.Number _
            & "':" & vbNewLine & vbNewLine _
            & Err.Description
        GoTo tblReplace_Exit
    End If

End Sub

Example:

tblReplace "MyTable", "OldText", "NewText", vbTextCompare

 

[SamDeMan]

rural guy: on one table. please look on.

BYTEMYZER: thanks a lot. i used it and it works. does the trick. i can't say i fully understand the sub, but it works. will work on understanding it later. i am happy that it works.

thanks all,

sam

p.s.
if someone has a more efficient way please share it.
thanks,
sam