By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

How to translate DATA in MS ACCESS?

P: 90
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.
Dec 26 '17 #1
Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Dec 27 '17 #2

P: 90
Yes, I want the similar thing. Please!
Dec 27 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Dec 27 '17 #4

P: 90
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.
Dec 27 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
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.
Expand|Select|Wrap|Line Numbers
  1. Function TranslateRoutine(InputStr As String, OutLanguage As String, LanguageTranslateTime As Single, _
  2.     LatinLanguage As Boolean, LanguageLegalFrom As Long, LanguageLegalTo As Long, _
  3.     Optional InLanguage As String, Optional ShowIE As Boolean = 0) As String
  4.     'Debug.Print TranslateRoutine("Deutsch", 32, "el", 0.5, False, 880, 1023, "de", 0, -1)
  5.  
  6.     Dim IE As Object
  7.     Dim OutputStr As String
  8.     Dim TmpWord As String
  9.     Dim i As Integer, j As Integer
  10.     Dim TestWord As String
  11.     Dim TranslatedWord As String
  12.     Dim Tries As Integer
  13.     Dim TranslatedText() As String
  14.     Dim Transl_Text() As String
  15.  
  16.     Set IE = CreateObject("InternetExplorer.Application")
  17.  
  18.     IE.Visible = 0
  19.  
  20.     ' TO CHOOSE INPUT LANGUAGE
  21.     If Nz(InLanguage) = "" Then
  22.         InLanguage = "auto"
  23.     End If
  24.  
  25.     ' Strip any leading quotes before translation
  26.     If Left(InputStr, 1) = Chr$(34) Or Left(InputStr, 1) = Chr$(39) Then
  27.         InputStr = Mid(InputStr, 2)
  28.     End If
  29.  
  30.     If Right(InputStr, 1) = Chr$(34) Or Right(InputStr, 1) = Chr$(39) Then
  31.         InputStr = Left(InputStr, Len(InputStr) - 1)
  32.     End If
  33.  
  34.     Marker = 1
  35.  
  36.     'Open website
  37.     'IE.Navigate "http://translate.google.com/#" & InLanguage & "/" & OutLanguage & "/" & InputStr
  38.  
  39.     '### This seems a mor reliable translation ###
  40.     'IE.Navigate "http://translate.google.com/translate_t?langpair=" & InLanguage & "|" & OutLanguage & "&ie=UTF8&text= " & InputStr
  41.  
  42.     IE.Navigate "http://translate.google.com/translate_t?langpair=" & InLanguage & "|" & OutLanguage & "&meta charset=UTF-8&text= " & InputStr
  43.  
  44.     DoEvents
  45.  
  46.     Do Until IE.ReadyState = 4
  47.         apWait LanguageTranslateTime, 0                            ' Wait half .05 seconds
  48.         DoEvents
  49.     Loop
  50.  
  51.     OutputStr = IE.Document.getElementById("result_box").innerHTML
  52.     If TempVars!ConDebug = 3 Then Debug.Print "Translate Routine OutputStr: " & OutputStr
  53.  
  54.     Marker = 2
  55. CleanOutputStr:
  56.     TmpWord = ""
  57.     Tries = Tries + 1                                                       ' Try this twice if it fails
  58.     If OutputStr > "" Then
  59.  
  60.         TranslatedText = Split(OutputStr, ">")
  61.         For j = 0 To UBound(TranslatedText)
  62.             If InStr(1, TranslatedText(j), "</", vbTextCompare) > 0 Then
  63.                 Transl_Text = Split(TranslatedText(j), "</")
  64.             End If
  65.         Next j
  66.         If TempVars!ConDebug = 3 Then Debug.Print Transl_Text(0)
  67.         OutputStr = Transl_Text(0)
  68.  
  69.         OutputStr = Replace(OutputStr, "&amp;", "&")                        ' Replace undescored shortcut letters
  70.         ' We sometimes get the translated word surrounded by " " & " " (Chr$(171) & Chr$(187)
  71.         ' We sometimes get the translated word surrounded by Double Quotes = Chr$(34)
  72.         If Len(OutputStr) > 2 Then                                          ' No check for 2 letter words (Can't be )
  73.             If Left(OutputStr, 1) = Chr$(171) And (Mid(OutputStr, Len(OutputStr) - 1, 1) = Chr$(187) & ";" Or Right(OutputStr, 1) = Chr$(187)) Then
  74.                 If Left(InputStr, 1) <> Chr$(171) Or Right(InputStr, 2) = Chr$(187) & ";" Or Right(InputStr, 1) = Chr$(187) Then
  75.                     OutputStr = Trim(Replace(OutputStr, Chr$(171), "", , 1))
  76.                     'OutputStr = Trim(Replace(OutputStr, " " & Chr$(187), "", , 1))
  77.                     OutputStr = Trim(Replace(OutputStr, Chr$(187), "", , 1))
  78.                 End If
  79.             End If
  80.             If Left(OutputStr, 1) = Chr$(34) Then
  81.                 OutputStr = Trim(Replace(OutputStr, Chr$(34), "", , 1))
  82.             End If
  83.             If Right(OutputStr, 1) = Chr$(34) Then
  84.                 OutputStr = Trim(Left(OutputStr, Len(OutputStr) - 1))
  85.             End If
  86.         End If
  87.  
  88.         TranslatedWord = StrConv(OutputStr, vbUnicode)
  89.         TestWord = ""
  90.         For i = 1 To Len(TranslatedWord) Step 2
  91.             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)
  92.             TestWord = Right("0" & Hex(Asc(Mid(TranslatedWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TranslatedWord, i, 1))), 2)
  93.             TmpWord = TmpWord & CharW("U" & TestWord, -1)        ' Indicate it's a unicoode character
  94.         Next i
  95.  
  96.             If TempVars!ConDebug = 10 Then Debug.Print StrConv(OutputStr, vbUnicode)
  97.         If LatinLanguage = 0 Then                               ' Not a latin Language, Ignore cidillas. accents in European languages
  98.         ' See if we have Russian, Chinese etc (Returns True for Latin Script, False for Russian, Chinese etc)
  99.             If CheckLanguageOK(TmpWord, LanguageLegalFrom, LanguageLegalTo) = -1 Then     ' Succeded in getting Russian, Chinese etc
  100.                 GoTo TranslateRoutine_Exit
  101.             Else
  102.                 GoTo ShowExplorer
  103.             End If
  104.         Else
  105.             ' If it is a latin language and the first letter of the original word is a capital letter,
  106.             ' make the first letter of the translated word is a capital letter,
  107.             If Asc(Left(InputStr, 1)) >= 65 And Asc(Left(InputStr, 1)) <= 90 Then
  108.                 TmpWord = StrConv(TmpWord, vbProperCase)
  109.             End If
  110.         End If
  111.     Else
  112.         TmpWord = InputStr
  113.     End If
  114.  
  115.     GoTo TranslateRoutine_Exit              ' Success
  116.  
  117.     Marker = 3
  118.  
  119. ShowExplorer:
  120.     If ShowIE = -1 Then                     ' Coming from Form Controls translate
  121.         IE.Visible = ShowIE
  122.         IE.AddressBar = -1
  123.         IE.MenuBar = 0
  124.         IE.Toolbar = 0
  125.         IE.Width = 800                  ' 800
  126.         IE.Height = 750                 '750
  127.         'Stop
  128.  
  129.         OutputStr = ""
  130.         OutputStr = IE.Document.getElementById("result_box").innerHTML
  131.         If TempVars!ConDebug = 3 Then Debug.Print "Translate Routine OutputStr: " & OutputStr
  132.         apWait 2, 0
  133.         'IE.Quit
  134.         If Tries < 3 Then
  135.             GoTo CleanOutputStr
  136.         End If
  137.         TmpWord = InputStr              ' Faulty translation
  138.  
  139.         GoTo TranslateRoutine_Exit
  140.     Else
  141.         IE.Visible = 0
  142.     End If
  143.  
  144. TranslateRoutine_Exit:
  145.     TranslateRoutine = TmpWord
  146.     If TempVars!ConDebug = 3 Then Debug.Print "Translation Routine Input: " & InputStr & "    Output: " & TmpWord
  147.  
  148. FinalTranslateRoutine_Exit:
  149.     IE.Quit
  150.     Exit Function
  151.  
  152. Translate_Err:
  153.     If Err = -2147467259 Then                       ' Automation error
  154.         Resume FinalTranslateRoutine_Exit
  155.     ElseIf Err = -2147023179 Then                   ' Automation error The Interface is unknown
  156.         Resume FinalTranslateRoutine_Exit
  157.     ElseIf Err = -2147417848 Then                   ' The object invoked has disconnected from its clients.
  158.         Resume FinalTranslateRoutine_Exit           ' Exit
  159.     ElseIf Err = -2147467259 Then                   ' Automation Error Unspecified error
  160.         Resume FinalTranslateRoutine_Exit           ' Exit
  161.     ElseIf Err = -2147023706 Then                   ' A system shutdown has already been scheduled
  162.         Resume FinalTranslateRoutine_Exit           ' Exit
  163.     ElseIf Err = 462 Then                           ' The remote server machine does not exist or is unavailable
  164.         Resume FinalTranslateRoutine_Exit           ' Exit
  165.     ElseIf Err = 3022 Then                          ' Duplicate
  166.         Resume FinalTranslateRoutine_Exit
  167.     ElseIf Err = 5 Then
  168.         Stop
  169.         'Resume CheckStrings
  170.     ElseIf Err = 9 And Marker = 2 Then              ' Subsript out of range
  171.         OutputStr = TranslatedText(0)
  172.         Resume Next
  173.     Else
  174.         Call LogError(Err, Error$, "TranslateRoutine", Marker, -1)
  175.         Resume FinalTranslateRoutine_Exit
  176.     End If
  177.  
  178. End Function
  179.  
Expand|Select|Wrap|Line Numbers
  1. Function CheckLanguageOK(Word As String, LanguageLegalFrom As Long, LanguageLegalTo As Long) As Boolean
  2.  
  3. ' ###########################################
  4. ' ## CHECK LETTERS ARE IN CORRECT LANGUAGE ##
  5. ' ###########################################
  6.     Dim UnicodeChar As String
  7.     Dim i As Integer
  8.     Dim TestWord As String
  9.     Dim DirtyWord As Boolean
  10.  
  11.     If LanguageLegalFrom = 0 Or LanguageLegalTo = 0 Then    ' Language not foun
  12.         Exit Function
  13.     End If
  14.  
  15.     If Right(Word, 1) = ";" Then                            ' strip any semi colons
  16.         Word = Trim(Left(Word, Len(Word) - 1))
  17.     End If
  18.  
  19. CleanWord:
  20.     DirtyWord = 0
  21.     If Right(Word, 1) = Chr$(39) Then                       ' Strip any single quotes
  22.         Word = Trim(Left(Word, Len(Word) - 1))
  23.         DirtyWord = -1
  24.     End If
  25.  
  26.     If Right(Word, 1) = Chr$(34) Then                       ' Strip any double quotes
  27.         Word = Trim(Left(Word, Len(Word) - 1))
  28.         DirtyWord = -1
  29.     End If
  30.  
  31.     If Left(Word, 1) = Chr$(39) Then                        ' Strip any single quotes
  32.         Word = Trim(Mid(Word, 2))
  33.         DirtyWord = -1
  34.     End If
  35.  
  36.     If Left(Word, 1) = Chr$(34) Then                        ' Strip any double quotes
  37.         Word = Trim(Mid(Word, 2))
  38.         DirtyWord = -1
  39.     End If
  40.  
  41.     If DirtyWord = -1 Then                                  ' Try again
  42.         GoTo CleanWord
  43.     End If
  44.  
  45.     TestWord = StrConv(Word, vbUnicode)
  46.     CheckLanguageOK = -1
  47.  
  48.     For i = 1 To Len(TestWord) Step 2
  49.         UnicodeChar = Right("0" & Hex(Asc(Mid(TestWord, i + 1, 1))), 2) & Right("0" & Hex(Asc(Mid(TestWord, i, 1))), 2)
  50.         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)
  51.  
  52.         If UnicodeChar <> "0020" And (UnicodeChar > "0030" Or UnicodeChar < "0039") Then                    ' Space or number
  53.             If ChangeHexToDecimal("&h" & UnicodeChar) < Val(LanguageLegalFrom) Or ChangeHexToDecimal("&h" & UnicodeChar) > Val(LanguageLegalTo) Then
  54.           '  Stop
  55.                 CheckLanguageOK = 0
  56.                 Exit For
  57.             End If
  58.         End If
  59.     Next i
  60.  
  61. End Function
  62.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. Function ChangeHexToDecimal(HexIn$) As Long
  3.  
  4.     Dim Stg As String
  5.  
  6.     If Left(HexIn, 1) = "#" Then          ' Hex value entered
  7.         Stg = Right(HexIn, Len(HexIn) - 1)
  8.         Stg = "&h" & Stg
  9.         ChangeHexToDecimal = CLng(Stg)                 ' Convert to decimal
  10.     Else
  11.         ChangeHexToDecimal = CLng(HexIn)
  12.     End If
  13.  
  14. End Function
  15.  
Phil
Dec 29 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.