Using vba on queries

[tytlwkltc]

Assuming that I have two tables in my database.

table: A

Sentence
Tom is a boy.
Mary is a girl.

table: B
BeforeWords|||AfterWords
Tom ||| Peter
Mary ||| Betty
is ||| was

After running the queries, it should have
Afteradjust
Peter was a boy.
Betty was a girl.

Table B is a converter which change a word into other.
I know Replace function query can be used... but it turns messy if i have over thousand of records.
How can transform it using VBA? (so weak in it!:banghead

 

[namliam]

how about "simply" running an update statement in a loop ?
somewhat pseudo code to get you started:

Do while not rs.eof
    currentdb.execute "Update tablea set sentence = replace(sentence, ...)"
    rs.movenext
loop

 

[CJ_London]

It could be very slow and may result in grammatical errors but something like the following will get you started

Put the following in a module

Public Function ConvertSentence(Sentence as String) as String
Dim rst as DAO.Recordset
Dim sarr() as String
Dim i as Integer
Dim tmpStr as Sting
 
tmpStr=""    
if Sentence<>"" then
    sarr=split(sentence," ")'split sentence into separate words
      for i=0 to ubound(sarr)-1
        set rst=currentdb.openrecordset("SELECT C FROM Hehe WHERE B='" & sarr(i) & "'"
        if not rst.eof then 'a conversion exists
            Tmpstr=tmpstr & rst!c & " "
        else ' use the original word
            tmpstr=tmpstr & sarr(i) & " "
        end if
        set rst=nothing
    next i
End if
ConvertSentence=tmpstr
 
end function

Another option would be to read the whole of the Hehe table into memory but this could be quite slow