How to translate DATA in MS ACCESS?

[mshakeelattari]

In my database with a table "Students" having two of the fields, "Name in English" and "Name in Urdu", I want a form where the user enters "Name in English" in a text box, and the next textbox "Name in Urdu" automatically gets the translated name in Urdu. How is it possible? Please help. Thanking in anticipation.

[PhilOfWalton]

I have a database that will show the forms, reports and messages into any of up to 100 languages (including Urdu), and if Google translate can translate an English name name into Urdu then the database will handle it. However I have reservations if this is possible.
Is مریم the equivalent of Mary and جان = John.
If so it may be possible

Phil

[mshakeelattari]

Yes, I want the similar thing. Please!

[PhilOfWalton]

Well there are 3 possibilities that I can see.

One is to build a table of all possible English names, use google translate to get the Urdu equivalent, then use the table as a lookup table. This will obviously take some time to build up the initial English Names, the translation time is comparatively quick depending on broadband speeds, but probably 3 or 4 names per second. Using this approach, after entering the English name, looking up the Urdu equivalent is instantaneous as it comes from a lookup table. An advantage of this system is that if the Urdu translation is incorrect. it only needs to be corrected once.

The second possibility is to use Google Translate on the Fly. Advantage is that new names are dealt with on the spot. Disadvantages include that a permanent connection to broadband needs to be available, the translation on a one off basis will be slower and if the translation is wrong once, it will always be wrong.

There is perhaps a compromise situation, where you use the lookup table approach, if that fails to find the correct name, use Google Translate and then add it to your table.

Phil

[mshakeelattari]

I shall prefer the first method or the third one, that is offline. If I have a table having all possible terms in both languages. The issue is that Names may be consisted of more than one parts in my case (First, Middle, Last Names in the same field "Name"). In the table, single words synonyms are present. What VBA Function or else I can use to translate the names using the vocabulary from the table? Thanking in advance for guidance.

[PhilOfWalton]

All I can do is attach a load of code. I hope you are a reasonably experienced programmer, because this will need a lot of modification to get it to work, but hopefully will get you started.

Function TranslateRoutine(InputStr As String, OutLanguage As String, LanguageTranslateTime As Single, _
    LatinLanguage As Boolean, LanguageLegalFrom As Long, LanguageLegalTo As Long, _
    Optional InLanguage As String, Optional ShowIE As Boolean = 0) As String
    'Debug.Print TranslateRoutine("Deutsch", 32, "el", 0.5, False, 880, 1023, "de", 0, -1)
 
    Dim IE As Object
    Dim OutputStr As String
    Dim TmpWord As String
    Dim i As Integer, j As Integer
    Dim TestWord As String
    Dim TranslatedWord As String
    Dim Tries As Integer
    Dim TranslatedText() As String
    Dim Transl_Text() As String
 
    Set IE = CreateObject("InternetExplorer.Application")
 
    IE.Visible = 0
 
    ' TO CHOOSE INPUT LANGUAGE
    If Nz(InLanguage) = "" Then
        InLanguage = "auto"
    End If
 
    ' Strip any leading quotes before translation
    If Left(InputStr, 1) = Chr$(34) Or Left(InputStr, 1) = Chr$(39) Then
        InputStr = Mid(InputStr, 2)
    End If
 
    If Right(InputStr, 1) = Chr$(34) Or Right(InputStr, 1) = Chr$(39) Then
        InputStr = Left(InputStr, Len(InputStr) - 1)
    End If
 
    Marker = 1
 
    'Open website
    'IE.Navigate "http://translate.google.com/#" & InLanguage & "/" & OutLanguage & "/" & InputStr
 
    '### This seems a mor reliable translation ###
    'IE.Navigate "http://translate.google.com/translate_t?langpair=" & InLanguage & "|" & OutLanguage & "&ie=UTF8&text= " & InputStr
 
    IE.Navigate "http://translate.google.com/translate_t?langpair=" & InLanguage & "|" & OutLanguage & "&meta charset=UTF-8&text= " & InputStr
 
    DoEvents
 
    Do Until IE.ReadyState = 4
        apWait LanguageTranslateTime, 0                            ' Wait half .05 seconds
        DoEvents
    Loop
 
    OutputStr = IE.Document.getElementById("result_box").innerHTML
    If TempVars!ConDebug = 3 Then Debug.Print "Translate Routine OutputStr: " & OutputStr
 
    Marker = 2
CleanOutputStr:
    TmpWord = ""
    Tries = Tries + 1                                                       ' Try this twice if it fails
    If OutputStr > "" Then
 
        TranslatedText = Split(OutputStr, ">")
        For j = 0 To UBound(TranslatedText)
            If InStr(1, TranslatedText(j), "</", vbTextCompare) > 0 Then
                Transl_Text = Split(TranslatedText(j), "</")
            End If
        Next j
        If TempVars!ConDebug = 3 Then Debug.Print Transl_Text(0)
        OutputStr = Transl_Text(0)
 
        OutputStr = Replace(OutputStr, "&amp;", "&")                        ' Replace undescored shortcut letters
        ' We sometimes get the translated word surrounded by "« " & " »" (Chr$(171) & Chr$(187)
        ' We sometimes get the translated word surrounded by Double Quotes = Chr$(34)
        If Len(OutputStr) > 2 Then                                          ' No check for 2 letter words (Can't be «»)
            If Left(OutputStr, 1) = Chr$(171) And (Mid(OutputStr, Len(OutputStr) - 1, 1) = Chr$(187) & ";" Or Right(OutputStr, 1) = Chr$(187)) Then
                If Left(InputStr, 1) <> Chr$(171) Or Right(InputStr, 2) = Chr$(187) & ";" Or Right(InputStr, 1) = Chr$(187) Then
                    OutputStr = Trim(Replace(OutputStr, Chr$(171), "", , 1))
                    'OutputStr = Trim(Replace(OutputStr, " " & Chr$(187), "", , 1))
                    OutputStr = Trim(Replace(OutputStr, Chr$(187), "", , 1))
                End If
            End If
            If Left(OutputStr, 1) = Chr$(34) Then
                OutputStr = Trim(Replace(OutputStr, Chr$(34), "", , 1))
            End If
            If Right(OutputStr, 1) = Chr$(34) Then
                OutputStr = Trim(Left(OutputStr, Len(OutputStr) - 1))
            End If
        End If
 
        TranslatedWord = StrConv(OutputStr, vbUnicode)
        TestWord = ""
        For i = 1 To Len(TranslatedWord) Step 2
            If TempVars!ConDebug = 10 Then Debug.Print Right("0" & Hex(Asc(Mid(TranslatedWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TranslatedWord, i, 1))), 2)
            TestWord = Right("0" & Hex(Asc(Mid(TranslatedWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TranslatedWord, i, 1))), 2)
            TmpWord = TmpWord & CharW("U" & TestWord, -1)        ' Indicate it's a unicoode character
        Next i
 
            If TempVars!ConDebug = 10 Then Debug.Print StrConv(OutputStr, vbUnicode)
        If LatinLanguage = 0 Then                               ' Not a latin Language, Ignore cidillas. accents in European languages
        ' See if we have Russian, Chinese etc (Returns True for Latin Script, False for Russian, Chinese etc)
            If CheckLanguageOK(TmpWord, LanguageLegalFrom, LanguageLegalTo) = -1 Then     ' Succeded in getting Russian, Chinese etc
                GoTo TranslateRoutine_Exit
            Else
                GoTo ShowExplorer
            End If
        Else
            ' If it is a latin language and the first letter of the original word is a capital letter,
            ' make the first letter of the translated word is a capital letter,
            If Asc(Left(InputStr, 1)) >= 65 And Asc(Left(InputStr, 1)) <= 90 Then
                TmpWord = StrConv(TmpWord, vbProperCase)
            End If
        End If
    Else
        TmpWord = InputStr
    End If
 
    GoTo TranslateRoutine_Exit              ' Success
 
    Marker = 3
 
ShowExplorer:
    If ShowIE = -1 Then                     ' Coming from Form Controls translate
        IE.Visible = ShowIE
        IE.AddressBar = -1
        IE.MenuBar = 0
        IE.Toolbar = 0
        IE.Width = 800                  ' 800
        IE.Height = 750                 '750
        'Stop
 
        OutputStr = ""
        OutputStr = IE.Document.getElementById("result_box").innerHTML
        If TempVars!ConDebug = 3 Then Debug.Print "Translate Routine OutputStr: " & OutputStr
        apWait 2, 0
        'IE.Quit
        If Tries < 3 Then
            GoTo CleanOutputStr
        End If
        TmpWord = InputStr              ' Faulty translation
 
        GoTo TranslateRoutine_Exit
    Else
        IE.Visible = 0
    End If
 
TranslateRoutine_Exit:
    TranslateRoutine = TmpWord
    If TempVars!ConDebug = 3 Then Debug.Print "Translation Routine Input: " & InputStr & "    Output: " & TmpWord
 
FinalTranslateRoutine_Exit:
    IE.Quit
    Exit Function
 
Translate_Err:
    If Err = -2147467259 Then                       ' Automation error
        Resume FinalTranslateRoutine_Exit
    ElseIf Err = -2147023179 Then                   ' Automation error The Interface is unknown
        Resume FinalTranslateRoutine_Exit
    ElseIf Err = -2147417848 Then                   ' The object invoked has disconnected from its clients.
        Resume FinalTranslateRoutine_Exit           ' Exit
    ElseIf Err = -2147467259 Then                   ' Automation Error Unspecified error
        Resume FinalTranslateRoutine_Exit           ' Exit
    ElseIf Err = -2147023706 Then                   ' A system shutdown has already been scheduled
        Resume FinalTranslateRoutine_Exit           ' Exit
    ElseIf Err = 462 Then                           ' The remote server machine does not exist or is unavailable
        Resume FinalTranslateRoutine_Exit           ' Exit
    ElseIf Err = 3022 Then                          ' Duplicate
        Resume FinalTranslateRoutine_Exit
    ElseIf Err = 5 Then
        Stop
        'Resume CheckStrings
    ElseIf Err = 9 And Marker = 2 Then              ' Subsript out of range
        OutputStr = TranslatedText(0)
        Resume Next
    Else
        Call LogError(Err, Error$, "TranslateRoutine", Marker, -1)
        Resume FinalTranslateRoutine_Exit
    End If
 
End Function
 

Function CheckLanguageOK(Word As String, LanguageLegalFrom As Long, LanguageLegalTo As Long) As Boolean
 
' ###########################################
' ## CHECK LETTERS ARE IN CORRECT LANGUAGE ##
' ###########################################
    Dim UnicodeChar As String
    Dim i As Integer
    Dim TestWord As String
    Dim DirtyWord As Boolean
 
    If LanguageLegalFrom = 0 Or LanguageLegalTo = 0 Then    ' Language not foun
        Exit Function
    End If
 
    If Right(Word, 1) = ";" Then                            ' strip any semi colons
        Word = Trim(Left(Word, Len(Word) - 1))
    End If
 
CleanWord:
    DirtyWord = 0
    If Right(Word, 1) = Chr$(39) Then                       ' Strip any single quotes
        Word = Trim(Left(Word, Len(Word) - 1))
        DirtyWord = -1
    End If
 
    If Right(Word, 1) = Chr$(34) Then                       ' Strip any double quotes
        Word = Trim(Left(Word, Len(Word) - 1))
        DirtyWord = -1
    End If
 
    If Left(Word, 1) = Chr$(39) Then                        ' Strip any single quotes
        Word = Trim(Mid(Word, 2))
        DirtyWord = -1
    End If
 
    If Left(Word, 1) = Chr$(34) Then                        ' Strip any double quotes
        Word = Trim(Mid(Word, 2))
        DirtyWord = -1
    End If
 
    If DirtyWord = -1 Then                                  ' Try again
        GoTo CleanWord
    End If
 
    TestWord = StrConv(Word, vbUnicode)
    CheckLanguageOK = -1
 
    For i = 1 To Len(TestWord) Step 2
        UnicodeChar = Right("0" & Hex(Asc(Mid(TestWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TestWord, i, 1))), 2)
        If TempVars!ConDebug = 10 Then Debug.Print Right("0" & Hex(Asc(Mid(TestWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TestWord, i, 1))), 2)
 
        If UnicodeChar <> "0020" And (UnicodeChar > "0030" Or UnicodeChar < "0039") Then                    ' Space or number
            If ChangeHexToDecimal("&h" & UnicodeChar) < Val(LanguageLegalFrom) Or ChangeHexToDecimal("&h" & UnicodeChar) > Val(LanguageLegalTo) Then
          '  Stop
                CheckLanguageOK = 0
                Exit For
            End If
        End If
    Next i
 
End Function
 

 
Function ChangeHexToDecimal(HexIn$) As Long
 
    Dim Stg As String
 
    If Left(HexIn, 1) = "#" Then          ' Hex value entered
        Stg = Right(HexIn, Len(HexIn) - 1)
        Stg = "&h" & Stg
        ChangeHexToDecimal = CLng(Stg)                 ' Convert to decimal
    Else
        ChangeHexToDecimal = CLng(HexIn)
    End If
 
End Function
 

Phil