By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 826 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to implement a multi-lingual database

Expert 100+
P: 344
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.
Expand|Select|Wrap|Line Numbers
  1. Q21   Who was present at the incident    0=No-one
  2.                                          1=Mother
  3.                                          2=Father
  4.                                          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 :
Expand|Select|Wrap|Line Numbers
  1. Field        Type      Comment
  2. option_id    Byte      Primary key, number will be stored in main tableB
  3. option_name  Text      The English text to be displayed (actual text will come from tblTranslate)
  4. 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
Expand|Select|Wrap|Line Numbers
  1. Field    Type
  2. trKey    Text(11)  Primary key, will be copied from tblTranslate
  3. 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
Expand|Select|Wrap|Line Numbers
  1.         MsgBox strTranslate("TR000000131", "Record Not Saved")
Function
Expand|Select|Wrap|Line Numbers
  1. Function strTranslate(ByVal strKey As String, Optional RealText As String = "") As String
  2. On Error GoTo strTranslate_Err
  3. 'takes the tran key and returns local text. RealText is ignored
  4. 'check key in right format
  5. If Len(strKey) = 11 And Left(strKey, 2) = "TR" Then
  6.     strTranslate = DLookup("trText", "tlkText", "trKey='" & strKey & "'")
  7. Else
  8.     strTranslate = "Invalid Translation Key"
  9. End If
  10. strTranslate_Exit:
  11.     Exit Function
  12. strTranslate_Err:
  13.     MsgBox Err.Description
  14.     Resume strTranslate_Exit
  15.  
  16. 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!

Expand|Select|Wrap|Line Numbers
  1. Sub AccessAllForms(strSetOrClear As String)
  2. On Error GoTo AccessAllForms_Err
  3. 'this opens, for edit, all forms in the system, and then sets tags
  4. If strSetOrClear <> "Set" And strSetOrClear <> "Clear" Then Exit Sub
  5. Dim i As Integer, j As Integer
  6. Dim db As Database
  7. Dim frm As Form, rpt As Report
  8. Set db = CurrentDb
  9. For i = 0 To db.Containers.Count - 1
  10.     If db.Containers(i).Name = "Forms" Then
  11.         For j = 0 To db.Containers(i).Documents.Count - 1
  12.             DoCmd.OpenForm db.Containers(i).Documents(j).Name, acDesign
  13.                Set frm = Forms(db.Containers(i).Documents(j).Name)
  14.                If strSetOrClear = "Set" Then
  15.                    setformtags frm
  16.                 Else
  17.                     clearformtags frm
  18.                 End If
  19.             DoCmd.Close acForm, db.Containers(i).Documents(j).Name, acSaveYes
  20.         Next
  21.     End If
  22.     If db.Containers(i).Name = "Reports" Then
  23.         For j = 0 To db.Containers(i).Documents.Count - 1
  24.             DoCmd.OpenReport db.Containers(i).Documents(j).Name, acDesign
  25.                Set rpt = Reports(db.Containers(i).Documents(j).Name)
  26.                If strSetOrClear = "Set" Then
  27.                    setrpttags rpt
  28.                 Else
  29.                    clearrpttags rpt
  30.                 End If
  31.             DoCmd.Close acReport, db.Containers(i).Documents(j).Name, acSaveYes
  32.         Next
  33.     End If
  34. Next
  35. Set db = Nothing
  36.  
  37. AccessAllForms_Exit:
  38.    Exit Sub
  39. AccessAllForms_Err:
  40.    MsgBox Err.Description & " in AccessAllForms"
  41.    Resume AccessAllForms_Exit
  42. End Sub
The above code goes through the forms and reports containers and calls the appropriate routine for each form and report.

For forms :
Expand|Select|Wrap|Line Numbers
  1. Sub setformtags(frm As Form)
  2. On Error GoTo setformtags_Err
  3. 'Mark Fisher
  4. 'This finds each control on the form that carries a caption and
  5. 'puts the caption in the trans table and fills in the tag property
  6. '
  7. 'it ignores tags already set
  8. '
  9. Dim ctl As Control, prt As Property, strKey As String
  10. Dim db As Database, strSQL As String
  11. Set db = CurrentDb
  12.  
  13. For Each ctl In frm.Controls
  14. 'go through every control
  15.     For Each prt In ctl.Properties
  16.     'find correct property
  17.         If prt.Name = "Caption" Then
  18.         'only look at controls that have a caption
  19.             If Len(prt.Value & "") > 1 Then
  20.             'only look at controls that have data in the caption
  21.                 If Len(ctl.Tag & "") = 0 Or ctl.Tag = "DetachedLabel" Then
  22.                 'only look at controls that we have not touched yet
  23.                     strKey = nextseq()
  24.                     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;"
  25.                     db.Execute (strSQL)
  26.                     ctl.Tag = strKey
  27.                 End If
  28.             End If
  29.         End If
  30.     Next
  31. Next
  32. If Len(frm.Tag & "") = 0 Then
  33. 'set the form tag
  34.     strKey = nextseq()
  35.     strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & frm.Name & ":Header' AS Expr2, " & Chr(34) & frm.Caption & Chr(34) & " AS Expr3;"
  36.     db.Execute (strSQL)
  37.     frm.Tag = strKey
  38. End If
  39.  
  40. Set db = Nothing
  41. setformtags_Exit:
  42.    Exit Sub
  43. setformtags_Err:
  44.    MsgBox Err.Description & " in setformtags"
  45.    Resume setformtags_Exit
  46. End Sub
Nextseq() is used to get the next key in order :
Expand|Select|Wrap|Line Numbers
  1. Function nextseq() As String
  2. On Error GoTo nextseq_Err
  3. 'this generates the next trans key
  4. nextseq = "TR" & Format(Val(Right(DMax("trkey", "tlkTranslate"), 9)) + 1, "000000000")
  5. nextseq_Exit:
  6.    Exit Function
  7. nextseq_Err:
  8.    MsgBox Err.Description & " in nextseq"
  9.    Resume nextseq_Exit
  10. End Function
For reports, the code is almost identical
Expand|Select|Wrap|Line Numbers
  1. Sub setrpttags(rpt As Report)
  2. On Error GoTo setrpttags_Err
  3. 'Mark Fisher
  4. 'This finds each control on the report that carries a caption and
  5. 'puts the caption in the trans table and fills in the tag property
  6. '
  7. 'it ignores tags already set
  8. Dim ctl As Control, prt As Property, strKey As String
  9. Dim db As Database, strSQL As String
  10. Set db = CurrentDb
  11.  
  12. For Each ctl In rpt.Controls
  13. 'go through every control
  14.     For Each prt In ctl.Properties
  15.     'find correct property
  16.         If prt.Name = "Caption" Then
  17.         'only look at controls that have a caption
  18.             If Len(prt.Value & "") > 0 Then
  19.             'only look at controls that have data in the caption
  20.                 If Len(ctl.Tag & "") = 0 Or ctl.Tag = "DetachedLabel" Then
  21.                 'only look at controls that we have not touched yet
  22.                     strKey = nextseq()
  23.                     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;"
  24.                     db.Execute (strSQL)
  25.                     ctl.Tag = strKey
  26.                 End If
  27.             End If
  28.         End If
  29.     Next
  30. Next
  31. If Len(rpt.Tag & "") = 0 Then
  32. 'set the report tag
  33.     strKey = nextseq()
  34.     strSQL = "INSERT INTO tlkTranslate ( trKey, context, English ) SELECT '" & strKey & "' AS Expr1, '" & rpt.Name & ":Header' AS Expr2, " & Chr(34) & rpt.Caption & Chr(34) & " AS Expr3;"
  35.     db.Execute (strSQL)
  36.     rpt.Tag = strKey
  37. End If
  38.  
  39. Set db = Nothing
  40. setrpttags_Exit:
  41.    Exit Sub
  42. setrpttags_Err:
  43.    MsgBox Err.Description & " in setrpttags"
  44.    Resume setrpttags_Exit
  45. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboLanguage_AfterUpdate()
  2. On Error GoTo cboLanguage_AfterUpdate_Err
  3. 'this will delete the existing text data and load in the new one
  4. Dim db As Database, strSQL As String
  5. Set db = CurrentDb
  6. db.Execute ("DELETE tlkText.* FROM tlkText;")
  7.  
  8. strSQL = "INSERT INTO tlkText ( trKey, trText )SELECT trKey, " & cboLanguage & " FROM tlkTranslate;"
  9. db.Execute (strSQL)
  10. db.Execute ("UPDATE tblGlobalCode SET tblGlobalCode.[Language] = '" & cboLanguage & "';")
  11. Set db = Nothing
  12. formtran Forms!fmnuMainMenu
  13. DoCmd.Close
  14. cboLanguage_AfterUpdate_Exit:
  15.    Exit Sub
  16. cboLanguage_AfterUpdate_Err:
  17.    MsgBox Err.Description & " in cboLanguage_AfterUpdate"
  18.    Resume cboLanguage_AfterUpdate_Exit
  19. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tlkMultipleBirth.option_id, tlkText.trText FROM tlkMultipleBirth INNER JOIN tlkText ON tlkMultipleBirth.trkey=tlkText.trKey;
Giving this in English :
Expand|Select|Wrap|Line Numbers
  1. option_id  trText
  2. 1          SINGLE
  3. 2          TWIN
  4. 3          TRIPLET
  5. 4          Quads
  6. 8          Don't know
  7. 9          No Data Entered
Or this in Portuguese

Expand|Select|Wrap|Line Numbers
  1. option_id  trText
  2. 1          ÚNICO
  3. 2          GÊMEOS 
  4. 3          TRIGÊMEO
  5. 4          Quadruplo
  6. 8          Não sabe
  7. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo Form_Open_Err
  3. formtran Me
  4. 'called by more than one form. If not cluster, then
  5.     'table name/clusterid/formname/tabanca id/household blah blah
Expand|Select|Wrap|Line Numbers
  1. Sub formtran(frm As Form)
  2. On Error GoTo formtran_Err
  3. 'This returns the language text for each tagged control
  4. Dim ctl As Control
  5.  
  6. For Each ctl In frm.Controls
  7.     If Len(ctl.Tag & "") > 0 Then
  8.     'only look at controls that are tagged
  9.         If Left(ctl.Tag, 2) = "TR" Then
  10.         'make sure we have the correct tag
  11.             ctl.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & ctl.Tag & "'"))
  12.         End If
  13.     End If
  14. Next
  15.  
  16. If Len(frm.Tag & "") > 0 Then
  17.     frm.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & frm.Tag & "'"))
  18. End If
  19.  
  20. formtran_Exit:
  21.    Exit Sub
  22. formtran_Err:
  23.    MsgBox Err.Description & " in formtran"
  24.    Resume formtran_Exit
  25. End Sub
Expand|Select|Wrap|Line Numbers
  1. Function nulltostring(varTXT As Variant) As String
  2. On Error GoTo nulltostring_Err
  3. 'converts null value to empty string
  4. If IsNull(varTXT) Then
  5.     nulltostring = ""
  6. Else
  7.     nulltostring = varTXT
  8. End If
  9. nulltostring_Exit:
  10.    Exit Function
  11. nulltostring_Err:
  12.    MsgBox Err.Description & " in nulltostring"
  13.    Resume nulltostring_Exit
  14. End Function
For Reports :
Expand|Select|Wrap|Line Numbers
  1. Sub reportTran(rpt As Report)
  2. On Error GoTo reportTran_Err
  3. 'This returns the language text for each tagged control
  4. Dim ctl As Control
  5.  
  6. For Each ctl In rpt.Controls
  7.     If Len(ctl.Tag & "") > 0 Then
  8.     'only look at controls that are tagged
  9.         If Left(ctl.Tag, 2) = "TR" Then
  10.         'make sure we have the correct tag
  11.             ctl.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & ctl.Tag & "'"))
  12.         End If
  13.     End If
  14. Next
  15.  
  16. If Len(rpt.Tag & "") > 0 Then
  17.     rpt.Caption = nulltostring(DLookup("trText", "tlkText", "trKey='" & rpt.Tag & "'"))
  18. End If
  19. reportTran_Exit:
  20.    Exit Sub
  21. reportTran_Err:
  22.    MsgBox Err.Description & " in reportTran"
  23.    Resume reportTran_Exit
  24. 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
File Type: zip MultiLingualSample.zip (1.79 MB, 3712 views)
Feb 18 '11 #1
Share this Article
Share on Google+
9 Comments


P: 3
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
---------------------------
Aug 13 '14 #2

P: 3
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
---------------------------
Aug 13 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,282
Mark,

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

NeoPa
Expert Mod 15k+
P: 31,485
@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.
Aug 18 '14 #5

P: 90
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.
Dec 28 '16 #6

NeoPa
Expert Mod 15k+
P: 31,485
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.
Jan 4 '17 #7

NeoPa
Expert Mod 15k+
P: 31,485
A new question (How To Implement Translation in ACCDE) was incorrectly posted in here and has now been moved to its own Question thread.
Mar 31 '18 #8

PhilOfWalton
Expert 100+
P: 1,430
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
Apr 1 '18 #9

NeoPa
Expert Mod 15k+
P: 31,485
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.
Aug 18 '19 #10