How to implement a multi-lingual database

[Lysander]

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("TR0000003") 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.

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 :

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

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

        MsgBox strTranslate("TR000000131", "Record Not Saved")

Function

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!

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 :

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 :

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

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 frmTranslateLive 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.

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 :

SELECT tlkMultipleBirth.option_id, tlkText.trText FROM tlkMultipleBirth INNER JOIN tlkText ON tlkMultipleBirth.trkey=tlkText.trKey;

Giving this in English :

option_id  trText
1          SINGLE
2          TWIN
3          TRIPLET
4          Quads
8          Don't know
9          No Data Entered

Or this in Portuguese

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.

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

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

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 :

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.

[hameedacpa]

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
---------------------------

[hameedacpa]

another problem when trying to run ListControlsAllForms


---------------------------
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 ListControlsAllForms
---------------------------
OK
---------------------------

[twinnyfo]

Mark,

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

[NeoPa]

@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.

[mshakeelattari]

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.

[NeoPa]

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.

[NeoPa]

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

[PhilOfWalton]

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_LoadLabels 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

[NeoPa]

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.