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.
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 :
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
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
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!
The above code goes through the forms and reports containers and calls the appropriate routine for each form and report.
For forms :
Nextseq() is used to get the next key in order :
For reports, the code is almost identical
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.
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 :
Giving this in English :
Or this in Portuguese
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.
For Reports :
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.
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
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
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
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")
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
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
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
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
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
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
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;
Code:
option_id trText 1 SINGLE 2 TWIN 3 TRIPLET 4 Quads 8 Don't know 9 No Data Entered
Code:
option_id trText 1 ÚNICO 2 GÊMEOS 3 TRIGÊMEO 4 Quadruplo 8 Não sabe 9 Sem dado preenchido
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
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
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.
Comment