How to implement a multi-lingual database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Lysander
    Recognized Expert Contributor
    • Apr 2007
    • 344

    #1

    How to implement a multi-lingual database

    Multi-Lingual Access Databases

    Summary.

    I have been asked many times to produce databases that can be used with different languages, such as English, French, Portuguese, German, Norwegian etc.

    I have developed a system for this which I will share with you now.

    This system will allow all your forms, reports, in-code messages etc to be presented in the language of the user’s choice.

    I have included a stripped down Access 2003 database with all the code and tables and a dummy main menu that can be presented in English or Portuguese.

    This was first developed in Access 2 and works up to Access 2003. There is no reason why it should not work in Access 2007.

    Credit must be given to Paul Litwin who gave me the initial idea at The Access Developers Conference many years ago.

    Assumptions.

    I am assuming a split database, code on each PC, shared data on the server, each PC can run a different language.

    I am assuming that the development is being done in English. Change English to French or whatever if being developed in another tongue.

    Limitations.

    This system does not allow for the translation of data entered.

    This system does not actually do the translation; this must be entered in a table by a linguist.

    This system will not translate windows or access generated messages (such as error messages, or the Yes/No/OK on message boxes), you must run the French or Spanish etc version of windows and Access for that.

    If you are using this system to translate into a non-roman language, such as Arabic, Russian (or in my case Telugu) then you must have the appropriate Language support from Windows XP installed.

    You are limited to 255 chars per phrase or section of text (Unless you want a huge number of memo records).

    The Concept.

    What is needed is a table to hold all text to be displayed anywhere in the system, that can hold this text in all chosen languages.

    This table is tblTranslate and holds a translation key, context where the text came from, English text and then a field for each additional language.

    A method is then needed to retrieve this text and display it in the chosen language. One method cannot do everything, in fact 3 methods are used in this system.

    Text can be displayed in an Access database in the following ways.

    Captions on forms and reports.
    Messages in code.
    Options lists against fields in a table, such as, "Do you have a hospital?" answer to be "Yes; No; Don't Know".
    Data entered by the end-user.

    Data entered by the end user is not considered here. If you want that in multi-language, then create fields for each user and let the user enter each language.

    Option lists. The databases I write tend to present a large number of set options for the user to select against. These would normally be built into the table as a value list against the field name but they now have to be a lookup table in their own right. Each lookup table has identical field names. I'll explain in detail later.

    Messages in code. Instead of using msgbox "This is my message" I use msgbox strTranslate("T R0000003") where TR000003 is the key in tblTranslate for the English text "This is my message" and strTranslate is a routine I'll explain later.

    Captions on forms and reports.
    This is the clever bit. Every control, form, subform, reports, header, footer etc has a TAG property. This is completely unused by Access and it is in this tag property that I put the "TRxxxxxx" key to the translation table.

    This all sounds very tedious and complex, which is why I have written routines to automate as much of this as possible.

    So, lets get started.

    Table Creation.

    In the backend database, you need to create the following :

    tblLanguage is the table that holds the list of languages. It comprises the following fields :

    Language, text field, primary key. This is the name of the language, in English. This will also be a fieldname in the next table so keep the names sensible. English-USA is okay, English("USA") is not because that can't be a field name.

    trKey, text field. This holds the translation key, to translate the language name itself into other languages, IE. English, Angles, Ingles.


    tblTranslate is the table that holds all the language data for the system, in all languages. It has the following fields.
    trKey , text field, primary key. In my design, "TR" followed by nine digits.
    Context, text field. Not used in the system, but used by developer (and linguistic translator) to work out where the text came from.
    English, text field. The English version of the text. A required field.

    Then followed by :
    Portuguese
    French
    Etc
    All text fields, whose name must match exactly the entries in the tblLanguage above.
    These fields are text, required, with a default entry of "Awaiting Translation".

    The above tables hold the translation information, you will also need a set of lookup tables to hold the required data. For example, you might have a field that asks a yes/no question, or a field that expects the answer to be Mother, Father, Sister, Brother.

    The actual data stored in the database will be a number, linked to a text field. EG., a question on a report might look like this.
    Code:
    Q21   Who was present at the incident    0=No-one
                                             1=Mother
                                             2=Father
                                             3=Sister
    On the data entry form, there would be a combo box listing the above options. These options are stored in a look-up table.

    For each and every one of these lookup lists, you need to create a table prefixed by tlk. EG. tlkYesNO, tlkRelation.

    These tlk tables must all have exactly the same field names and structure (for ease of adding options later) as follows :
    Code:
    Field        Type      Comment
    option_id    Byte      Primary key, number will be stored in main tableB
    option_name  Text      The English text to be displayed (actual text will come from tblTranslate)
    trKey        Text(11)  The key to tblTranslate for this data
    then populate the table with data, adding rows into tblTranslate as you go.

    For the front end of the system, on each PC in a multiuser system, there is only one table needed.
    TlkText with the following fields
    Code:
    Field    Type
    trKey    Text(11)  Primary key, will be copied from tblTranslate
    trText   Text      Local language text, copied from tblTranslate
    So, we have our tables created. How do we use them?

    As mentioned above, there are three types of data to display.

    The lookup lists, stored in tlkxxxxx, text messages written in code, labels and text on forms and reports.

    For the lookup lists, the lookup table included trKey which points to a record in tlkTranslate.
    For the text messages, the following function strTranslate is used.

    Example of use
    Code:
            MsgBox strTranslate("TR000000131", "Record Not Saved")
    Function
    Code:
    Function strTranslate(ByVal strKey As String, Optional RealText As String = "") As String
    On Error GoTo strTranslate_Err
    'takes the tran key and returns local text. RealText is ignored
    'check key in right format
    If Len(strKey) = 11 And Left(strKey, 2) = "TR" Then
        strTranslate = DLookup("trText", "tlkText", "trKey='" & strKey & "'")
    Else
        strTranslate = "Invalid Translation Key"
    End If
    strTranslate_Exit:
        Exit Function
    strTranslate_Err:
        MsgBox Err.Description
        Resume strTranslate_Exit
    
    End Function
    Remember, tlkText is the local version of tlkTranslate, holding the data in the language of the users choice.

    For both the lookup tables and the text messages, the next value of trKey has to be entered by hand.

    Finally the labels and text on forms and reports.

    If you don't want a particular label translated, then in the Tag property for that label enter ‘Do Not Translate’ otherwise, just leave it alone.
    When you have finished a days coding, or whenever you want to add the latest translations, run the following code from the immediate window.
    AccessAllForms "Set"

    This calls two other routines, setformtags and setrpttags.

    AccessAllForms "Clear" removes ALL tags and is very final!

    Code:
    Sub AccessAllForms(strSetOrClear As String)
    On Error GoTo AccessAllForms_Err
    'this opens, for edit, all forms in the system, and then sets tags
    If strSetOrClear <> "Set" And strSetOrClear <> "Clear" Then Exit Sub
    Dim i As Integer, j As Integer
    Dim db As Database
    Dim frm As Form, rpt As Report
    Set db = CurrentDb
    For i = 0 To db.Containers.Count - 1
        If db.Containers(i).Name = "Forms" Then
            For j = 0 To db.Containers(i).Documents.Count - 1
                DoCmd.OpenForm db.Containers(i).Documents(j).Name, acDesign
                   Set frm = Forms(db.Containers(i).Documents(j).Name)
                   If strSetOrClear = "Set" Then
                       setformtags frm
                    Else
                        clearformtags frm
                    End If
                DoCmd.Close acForm, db.Containers(i).Documents(j).Name, acSaveYes
            Next
        End If
        If db.Containers(i).Name = "Reports" Then
            For j = 0 To db.Containers(i).Documents.Count - 1
                DoCmd.OpenReport db.Containers(i).Documents(j).Name, acDesign
                   Set rpt = Reports(db.Containers(i).Documents(j).Name)
                   If strSetOrClear = "Set" Then
                       setrpttags rpt
                    Else
                       clearrpttags rpt
                    End If
                DoCmd.Close acReport, db.Containers(i).Documents(j).Name, acSaveYes
            Next
        End If
    Next
    Set db = Nothing
    
    AccessAllForms_Exit:
       Exit Sub
    AccessAllForms_Err:
       MsgBox Err.Description & " in AccessAllForms"
       Resume AccessAllForms_Exit
    End Sub
    The above code goes through the forms and reports containers and calls the appropriate routine for each form and report.

    For forms :
    Code:
    Sub setformtags(frm As Form)
    On Error GoTo setformtags_Err
    'Mark Fisher
    'This finds each control on the form that carries a caption and
    'puts the caption in the trans table and fills in the tag property
    '
    'it ignores tags already set
    '
    Dim ctl As Control, prt As Property, strKey As String
    Dim db As Database, strSQL As String
    Set db = CurrentDb
    
    For Each ctl In frm.Controls
    'go through every control
        For Each prt In ctl.Properties
        'find correct property
            If prt.Name = "Caption" Then
            'only look at controls that have a caption
                If Len(prt.Value & "") > 1 Then
                'only look at controls that have data in the caption
                    If Len(ctl.Tag & "") = 0 Or ctl.Tag = "DetachedLabel" Then
                    'only look at controls that we have not touched yet
                        strKey = nextseq()
                        strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & frm.Name & ":" & ctl.Name & "' AS Expr2, " & Chr(34) & prt.Value & Chr(34) & " AS Expr3;"
                        db.Execute (strSQL)
                        ctl.Tag = strKey
                    End If
                End If
            End If
        Next
    Next
    If Len(frm.Tag & "") = 0 Then
    'set the form tag
        strKey = nextseq()
        strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & frm.Name & ":Header' AS Expr2, " & Chr(34) & frm.Caption & Chr(34) & " AS Expr3;"
        db.Execute (strSQL)
        frm.Tag = strKey
    End If
    
    Set db = Nothing
    setformtags_Exit:
       Exit Sub
    setformtags_Err:
       MsgBox Err.Description & " in setformtags"
       Resume setformtags_Exit
    End Sub
    Nextseq() is used to get the next key in order :
    Code:
    Function nextseq() As String
    On Error GoTo nextseq_Err
    'this generates the next trans key
    nextseq = "TR" & Format(Val(Right(DMax("trkey", "tlkTranslate"), 9)) + 1, "000000000")
    nextseq_Exit:
       Exit Function
    nextseq_Err:
       MsgBox Err.Description & " in nextseq"
       Resume nextseq_Exit
    End Function
    For reports, the code is almost identical
    Code:
    Sub setrpttags(rpt As Report)
    On Error GoTo setrpttags_Err
    'Mark Fisher
    'This finds each control on the report that carries a caption and
    'puts the caption in the trans table and fills in the tag property
    '
    'it ignores tags already set
    Dim ctl As Control, prt As Property, strKey As String
    Dim db As Database, strSQL As String
    Set db = CurrentDb
    
    For Each ctl In rpt.Controls
    'go through every control
        For Each prt In ctl.Properties
        'find correct property
            If prt.Name = "Caption" Then
            'only look at controls that have a caption
                If Len(prt.Value & "") > 0 Then
                'only look at controls that have data in the caption
                    If Len(ctl.Tag & "") = 0 Or ctl.Tag = "DetachedLabel" Then
                    'only look at controls that we have not touched yet
                        strKey = nextseq()
                        strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & rpt.Name & ":" & ctl.Name & "' AS Expr2, " & Chr(34) & prt.Value & Chr(34) & " AS Expr3;"
                        db.Execute (strSQL)
                        ctl.Tag = strKey
                    End If
                End If
            End If
        Next
    Next
    If Len(rpt.Tag & "") = 0 Then
    'set the report tag
        strKey = nextseq()
        strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & rpt.Name & ":Header' AS Expr2, " & Chr(34) & rpt.Caption & Chr(34) & " AS Expr3;"
        db.Execute (strSQL)
        rpt.Tag = strKey
    End If
    
    Set db = Nothing
    setrpttags_Exit:
       Exit Sub
    setrpttags_Err:
       MsgBox Err.Description & " in setrpttags"
       Resume setrpttags_Exit
    End Sub
    Once you have several dozen or hundreds of forms and reports, AccessAllForms can take some time to run, so go have a cup of coffee and come back later:)

    Ok, so now we have a system where all the displayable text is stored, in English in tlkTranslate along with "Waiting Translation" for all the other fields.

    We now send the database to our linguist who uses a simple form frmTranslateLiv e which displays all the text not yet translated, along with the context field and the English text and he or she translates them one by one.

    At this point I should point out a limitation of this system. Each phrase or piece of text is limited to 255 chars, including spaces etc.

    Ok, everything is now translated, how do we use this.

    Using the System

    On the main menu is a button, select language. This opens a simple form displaying a combo box listing the language names from the table tblLanguage. When selected, the following code is run.

    Code:
    Private Sub cboLanguage_AfterUpdate()
    On Error GoTo cboLanguage_AfterUpdate_Err
    'this will delete the existing text data and load in the new one
    Dim db As Database, strSQL As String
    Set db = CurrentDb
    db.Execute ("DELETE tlkText.* FROM tlkText;")
    
    strSQL = "INSERT INTO tlkText ( trKey, trText )SELECT trKey, " & cboLanguage & " FROM tlkTranslate;"
    db.Execute (strSQL)
    db.Execute ("UPDATE tblGlobalCode SET tblGlobalCode.[Language] = '" & cboLanguage & "';")
    Set db = Nothing
    formtran Forms!fmnuMainMenu
    DoCmd.Close
    cboLanguage_AfterUpdate_Exit:
       Exit Sub
    cboLanguage_AfterUpdate_Err:
       MsgBox Err.Description & " in cboLanguage_AfterUpdate"
       Resume cboLanguage_AfterUpdate_Exit
    End Sub
    This is copying the appropriate language from the shared backend database to the local PC.

    tblGlobalCode is a local table holding user specific data. It needs to know what the local language is for some more complex operations, such as running telegu or Arabic script which is beyond the scope of this article.

    So, how is the new language displayed.

    Well, for text messages, strTranslate, as listed above, does the job.

    For reports and combo boxes, the tlkxxxx table is linked to tlkText.

    For example,

    A question asking for multiple births would use this :
    Code:
    SELECT tlkMultipleBirth.option_id, tlkText.trText FROM tlkMultipleBirth INNER JOIN tlkText ON tlkMultipleBirth.trkey=tlkText.trKey;
    Giving this in English :
    Code:
    option_id  trText
    1          SINGLE
    2          TWIN
    3          TRIPLET
    4          Quads
    8          Don't know
    9          No Data Entered
    Or this in Portuguese

    Code:
    option_id  trText
    1          ÚNICO
    2          GÊMEOS 
    3          TRIGÊMEO
    4          Quadruplo
    8          Não sabe
    9          Sem dado preenchido
    For the forms and reports, I have two functions which are called in the form or report on-open event.

    Formtran me or reporttran me.

    Code:
    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Form_Open_Err
    formtran Me
    'called by more than one form. If not cluster, then
        'table name/clusterid/formname/tabanca id/household blah blah
    Code:
    Sub formtran(frm As Form)
    On Error GoTo formtran_Err
    'This returns the language text for each tagged control
    Dim ctl As Control
    
    For Each ctl In frm.Controls
        If Len(ctl.Tag & "") > 0 Then
        'only look at controls that are tagged
            If Left(ctl.Tag, 2) = "TR" Then
            'make sure we have the correct tag
                ctl.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & ctl.Tag & "'"))
            End If
        End If
    Next
    
    If Len(frm.Tag & "") > 0 Then
        frm.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & frm.Tag & "'"))
    End If
    
    formtran_Exit:
       Exit Sub
    formtran_Err:
       MsgBox Err.Description & " in formtran"
       Resume formtran_Exit
    End Sub
    Code:
    Function nulltostring(varTXT As Variant) As String
    On Error GoTo nulltostring_Err
    'converts null value to empty string
    If IsNull(varTXT) Then
        nulltostring = ""
    Else
        nulltostring = varTXT
    End If
    nulltostring_Exit:
       Exit Function
    nulltostring_Err:
       MsgBox Err.Description & " in nulltostring"
       Resume nulltostring_Exit
    End Function
    For Reports :
    Code:
    Sub reportTran(rpt As Report)
    On Error GoTo reportTran_Err
    'This returns the language text for each tagged control
    Dim ctl As Control
    
    For Each ctl In rpt.Controls
        If Len(ctl.Tag & "") > 0 Then
        'only look at controls that are tagged
            If Left(ctl.Tag, 2) = "TR" Then
            'make sure we have the correct tag
                ctl.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & ctl.Tag & "'"))
            End If
        End If
    Next
    
    If Len(rpt.Tag & "") > 0 Then
        rpt.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & rpt.Tag & "'"))
    End If
    reportTran_Exit:
       Exit Sub
    reportTran_Err:
       MsgBox Err.Description & " in reportTran"
       Resume reportTran_Exit
    End Sub
    And that, my friends, is that!

    Conclusion

    This system gives a fast (instantaneous) translation for the end user of your database into any Roman style language.
    It can be used for other scripts such as Arabic, Russian, Greek, Telugu etc but requires more work beyond the scope of this article for those case.

    It is a bit tedious to set up, and if you change a piece of text, say a label, you have to remember to change the translation in tlkTranslate as well, but, if you are working in English, your data entry people speak Portuguese, your field workers only speak Kriol, you have no option but to make a multi-lingual system.

    You are limited to 255 chars for each single phrase or message!

    My thanks again to Paul Litwin who showed me the original concept many years ago.

    Feel free to use and adapt this system as you see fit, but if you do use it, I would appreciate my name and company being acknowledged as the source.

    Mark Fisher
    Lytton Consultants Ltd


    Attched is one of my databases, with most of the functions and data stripped out, but leaving all the translation code and one data entry form and one report so you can see it in action. Obviously, in normal use all the tables except tlkText and tblGlobalCode would be in a backend database.
    Attached Files
    Last edited by Lysander; Feb 21 '11, 04:12 PM. Reason: Finished
  • hameedacpa
    New Member
    • Aug 2014
    • 3

    #2
    it is giving me error when add formtran me in the subform ?

    ---------------------------
    Microsoft Access
    ---------------------------
    Object doesn't support this property or method in formtran
    ---------------------------
    OK
    ---------------------------
    Last edited by zmbd; Aug 19 '14, 04:13 PM. Reason: [hameedacpa{add attachment}][Z{Removed Attachement Repeats Post Text}]

    Comment

    • hameedacpa
      New Member
      • Aug 2014
      • 3

      #3
      another problem when trying to run ListControlsAll Forms


      ---------------------------
      Microsoft Access
      ---------------------------
      The Microsoft Access database engine cannot find the input table or query 'tmpControl'. Make sure it exists and that its name is spelled correctly. in ListControlsAll Forms
      ---------------------------
      OK
      ---------------------------
      Last edited by zmbd; Aug 19 '14, 04:14 PM. Reason: [Z{Removed Attachement Repeats Post Text}]

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3665

        #4
        Mark,

        This is fascinating! Great work. I don't have a need for multi-lingual databases at this time, but your solution sounds amazing!

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32669

          #5
          @Lysander.

          We may need to look at this together. Next time you PM me (The same way you managed to last time) include a contact email address so we can communicate privately. A Skype ID would be very useful too.

          I suspect you just need to clear your cookies for Bytes.com but if we can get communicating properly I'll do what I can to help you.

          -Ade.

          Comment

          • mshakeelattari
            New Member
            • Nov 2014
            • 103

            #6
            This is a fascinating thing. But the thing not discussed here, that is, the translated data on a report in certain language while the original data in the tables is in English. Any idea for this will be appreciated.

            Comment

            • NeoPa
              Recognized Expert Moderator MVP
              • Oct 2006
              • 32669

              #7
              Hi.

              As you correctly point out, that area is not covered in this article.

              I suggest, assuming that's a topic you're interested in discovering more about, that you post a question about it in the Access Answers forum. It would not be appropriate to discuss it here.

              -Adrian.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32669

                #8
                A new question (How To Implement Translation in ACCDE) was incorrectly posted in here and has now been moved to its own Question thread.

                Comment

                • PhilOfWalton
                  Recognized Expert Top Contributor
                  • Mar 2016
                  • 1430

                  #9
                  Interesting.A few months ago a Swiss German friend of mine had a need for a multilingual Db so being bored I wrote something for him.

                  There are major differences between my Db and Lysander's Db in that my Db takes an existing database (written in any language), and with minimal change (mainly on the OnOpen of a form or Report add "Dbt_LoadLa bels me.Name" displays the forms & reports in the required language.

                  The translations are automatically using Google Translate (OK they're not perfect, and can be corrected), so one can get a workable database in about 100 languages with very little effort. The initial forms can be designed in any language (My forms are in English, and my friend's forms are in German)

                  DbTranslator will translate most fixed information in Forms & Reports including
                  1) Labels,
                  2) Captions,
                  3) Command Buttons,
                  4) Check Boxes,
                  5) Option Buttons,
                  6) Control Tip Text,
                  7) Status Bar,
                  8) Pages,
                  9) Combo & List boxes based on a value list,
                  10) Tables which are defined as lookup tables (for example, Months, Jobs, Types of business etc.
                  11) Some Combo & List boxes where there is a query based on a lookup table.
                  12) Some text boxes not based on an underlying record set e.g. “Page 3 of 5 Pages”,
                  13) Most Message Boxes.
                  14) Most Input Boxes

                  The initial translation takes some time, obviously depending on computer speed and internet speed, but allow about 30 seconds per form or report per language. There is a slight delay in loading the translated forms. A complex form with 6 tabs, 10 subforms and 12 combo boxes and about 6 seconds to load.

                  Unlike Lysander's program, Message boxes are translated automatically so no coding is required.

                  If anyone is seriously interested, please PM me and I will let them have a copy to play with.

                  Phil

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32669

                    #10
                    Please post your questions in the correct place.

                    Articles / Insights are not for posting questions. The places to get answers at Bytes are named the Answers forums.
                    Last edited by NeoPa; Aug 18 '19, 05:29 PM.

                    Comment

                    Working...