Internationalisation in Access

Working with Joomla, I'm used to messing around with language files for the user interface.
Each language file supplies text that should be displayed (as part of the interface) to the user depending on their language.
So today, I decided it was high time I built something similar in Access. Below is the (surprisingly easy) result, using Kernel32 to identify the current language of the user...

First of all you'll need a table to store your text. Access text fields store text as unicode so you can even include text from right-to-left languages like Arabic or Hebrew.

The table will need 3 fields

1. swmVar to store the variable name
   Type: Text
   Size: 25
   Required: Yes
   Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
2. lwmLanguageID to store the ID of the Language the text is for
   Type: Long (Long Integer)
   Required: Yes
   Default Val: 0
3. swoData to store the text itself
    Type: Text
   Size: 255

I can't think of a reason why table would need a primary key but it does need to make sure that you don't store duplicate values of swmVar within the same Language ID so for ease I've set swmVar + lwmLanguageID as a composite primary key. However, you could create a separate primary key and just have these to fields as a composite unique index.

NB: I'm using my standard naming convention here where the first letter of the field represents the field type, the 2nd notes if the field is editable (w=write, r=read-only) and the 3rd shows if the field is optional (o) or mandatory (m).

Next you'll need the module
Module: ModLanguage

Option Compare Database
' Module to assist internationalisation of database interface
' written by Marc Ozin
' version: rc2012.02.02
' Website: http://marcozin.blogspot.com


' this module requires a lanaguage table like this:

' Table Name: tblLanguage
' Fields:
'   swmVar
'       Type:        Text
'       Size:        25
'       Required:    Yes
'       Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
'   lwmLanguageID
'       Type:        Long Integer
'       Required:    Yes
'       Default Val: 0
'   swoData
'       Type:        Text
'       Size:        255
'
' Primary Key: Composite of swmVar & lwmLanguageID
'
' this table will store language varables & text for each lanaguage code
' plus language varables & text to default to if language code not found
' signifed using lwmLanguageID of zero (0)

' Notes:
' Table of Locale ID's can be found on these pages:
' http://msdn.microsoft.com/en-us/goglobal/bb895996
' http://msdn.microsoft.com/en-us/goglobal/bb964664

' Examples:
' to set the caption for the button called ButtonCancel in a form when it opens
' edit the OnLoad event for the form and insert the following line:
'   ButtonCancel.Caption = GetLangTxt("CANCEL")
' Then in the language table create a row with these values:
'   swmVar:         CANCEL
'   lwmLanguageID:  0
'   swoData:        &Cancel
' "&Cancel" will be the default text when a particular user's language cannot be found
' to have different text for German, add the following row:
'   swmVar:         CANCEL
'   lwmLanguageID:  1031
'   swoData:        &Kündigen




'Get the language ID for the current user's locale from kernel32
'returns Long number representing the Language ID
Public Declare Function GetUserDefaultLCID% Lib "kernel32" ()


Public Function GetLangTxt(sLangVar As String, Optional ByVal lLanguageID As Long = -1) As String
' returns language text from specified variable stored in the Language Table
' function will try and return text for specified varable for current language (or language specified by lLanguageID)
' if no text found, then it will try and return text for varable with a language id of zero
' if still no text found, then it will return the name of the varable incased in square brackets

Const cLanguageTable = "tblLanguage"
Const cLanguageDataField = "[swoData]"
Const cLanguageVarableNameField = "[swmVar]"
Const cLanguageIDField = "[lwmLanguageID]"

Dim vTemp As Variant
    
    If lLanguageID = -1 Then
        lLanguageID = GetUserDefaultLCID()
    End If

    'try for a specific language id
    vTemp = DLookup(cLanguageDataField, cLanguageTable _
    , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=" & CStr(lLanguageID))
    
    ' if nothing found
    If IsNull(vTemp) Then
        'try for language zero
        vTemp = DLookup(cLanguageDataField, cLanguageTable _
        , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=0")
    End If
    GetLangTxt = Nz(vTemp, "[" & sLangVar & "]")
End Function

Here's an example of how to set the caption for the button called ButtonCancel in a form when it opens.

1. edit the OnLoad event for the form and insert the following line:

   ButtonCancel.Caption = GetLangTxt("CANCEL")

2. Then to set "&Cancel" as the default text when a particular user's language cannot be found, in the language table create a row with these values:
   
   • swmVar: CANCEL
   • lwmLanguageID: 0
   • swoData: &Cancel
3. to have different text for German, add the following row:
   
   • swmVar: CANCEL
   • lwmLanguageID: 1031
   • swoData: &Kündigen