How to: Localize an application

Show an Access application's own texts - messages, captions, questions - in the language the WKL components speak with the user: English, German, Slovenian, Croatian, or Italian, from one database for all languages.

Prerequisites: WKL Forms Object Library 1.65 or later; captions with keys (@orders.title) need 1.66, neutral texts, further languages and the language folder need 1.67.

• The texts live in the application's table USysWklTexts, one record per key and language. The table is part of the database itself, so an ACCDE and the Access Runtime need nothing else.
• In code, WklTexts.Text returns a text by its key, in the language the components chose for the database (Choose the language of the components), or in English when the translation is missing.
• Captions set in Design view name a key instead of holding a text: @orders.title. WKL controls and the ribbon show the text themselves; for the native controls of a form or report, one call of WklTexts.Localize in the Open event does it. One form serves every language.
• Buttons and questions every application needs (OK, Cancel, Save, Delete, …) have standard texts with fixed wkl.* keys. They're reviewed in all five languages and read the same in every WKL application.

Projects of the WKL Office SDK: keep the texts as files source/texts/<language>.json of the project instead of steps 1 and 2; the build writes the table USysWklTexts from them. A table of that name among the sources as well stops the build (documentation of the WKL Office SDK, „How to: Translate an application“). Steps 3 and 4 apply unchanged.

1. Create the table. Run the statement once, for example in the Immediate window of the VBA editor:

   CurrentDb.Execute "CREATE TABLE USysWklTexts (ID COUNTER CONSTRAINT pkUSysWklTexts PRIMARY KEY, " & _
       "TextKey TEXT(255), Language TEXT(10), TextValue MEMO)", dbFailOnError

   The USys prefix hides the table in the Navigation Pane while system objects are hidden.

2. Enter the texts. One record per key in English, and one for each other language:

   TextKey	Language	TextValue
   orders.title	en	Orders
   orders.title	de	Aufträge
   orders.saved	en	{0} orders saved.
   orders.saved	de	{0} Aufträge gespeichert.

   English is the fallback language; give every key at least an English text.

3. Use the texts in code:

   Private Sub cmdSave_Click()
       Dim texts As New WklTexts
       Dim count As Long
       On Error GoTo ErrorHandler
    
       count = SaveOrders()
       MsgBox texts.Text("orders.saved", count), vbInformation, texts.Text("wkl.information")
    
   FinalHandler:
       Exit Sub
    
   ErrorHandler:
       MsgBox Err.Description, vbExclamation, texts.Text("wkl.error")
       Resume FinalHandler
   End Sub

   New WklTexts needs the reference WKL Forms x.y Object Library (VBA editor: Tools › References); without the reference, write CreateObject("WklForms.Texts").

4. Write keys into the captions. In Design view, set the Caption of the form to @orders.title, that of the save button to @wkl.save and that of the close button to @wkl.close. Then localize the form when it opens:

   Private Sub Form_Open(Cancel As Integer)
       Dim texts As New WklTexts
       texts.Localize Me
   End Sub

   Localize replaces Caption, ControlTipText and StatusBarText of the form and of every control on it whose value is a key, and returns how many values it replaced. A report does the same in Report_Open; a subform localizes itself in its own Open event.

WKL controls take a key wherever they take a caption and show its text without any code:

Design view, the property sheets, the designers and the saved definition keep the key, so the form stays the same in every language. Ribbon screen tips and super tips are written as they are and don't take keys.

• Neutral texts. Records with the language neutral hold the text a key shows in every language without a translation of its own, before English. Write the texts in your own language there and add only the translations: with neutral in Slovenian, the Slovenian column can stay empty. The order is: the language asked for, neutral, en, [key].
• Any language. The application texts may be in any language, for example fr, pl or es; a regional code such as fr-CA counts as its language. The language of the session is the first of the application model, Office and Windows that the application has texts in. The WKL controls keep to their five languages: with French application texts they show their own texts in the language they choose otherwise.

• Language folder i18n. Files <language>.json and neutral.json in a folder i18n next to the database add texts and override records of the table with the same key and language - for a correction or a further language after delivery, without a new build. Each file is a JSON object of keys and texts in UTF-8:

  { "orders.title": "Commandes", "orders.saved": "{0} commandes enregistrées." }

  A file that cannot be read is skipped; the other texts still apply. Whoever can write to the folder changes the texts of the application: on a network share, protect it like the database itself.

• Excel and Word. A workbook or document has no table; its texts come from a language folder that the code names once, for example in Workbook_Open:

  Dim texts As New WklTexts
  texts.Folder = ThisWorkbook.Path & "\i18n"

  The language there is the user interface language of Windows when the folder has texts in it, else the language of the components; texts.Language sets another one.

On a German Office the form shows „Aufträge“ and the buttons „Speichern“ and „Schließen“; on a Slovenian Office, „Naročila“, „Shrani“ and „Zapri“. A WklGrid column with the caption @orders.customer shows „Kunde“ or „Stranka“ in the same way. When orders.title has no Slovenian translation, its neutral text appears, and without one „Orders“. A key without any text appears as [orders.title], so the gap stands out instead of leaving an empty caption.

• Placeholders: {0}, {1}, … are filled in that order from the further arguments of Text; {0:N2} shows a number with two decimals, {1:d} a short date. Numbers and dates follow the regional settings of Windows, like everything Access formats, not the language of the text.
• Keys aren't case-sensitive. Dots group them by form or topic (orders.title, orders.saved).
• Which captions are keys: @ followed by a key with at least one dot and only letters, digits, ., _ and -. @Home or @ the office stay as written. @@ at the start writes a literal @: @@orders.title shows @orders.title. A key without any text shows [key] and appears in Missing, as with Text.
• Call Localize once per opening, in form view or report view. Afterwards the properties hold the texts. In Design view Localize raises an error instead of replacing the keys, which would otherwise be saved with the form.
• Captions used as data: texts.CaptionText(value) resolves a value by the same rule as the controls, for example the caption of the navigation table that the application copies into a table or a report.
• Standard texts wkl.* can't be replaced; a record of the application with such a key is skipped. The list is in the WklTexts API reference.
• Language for a test: texts.Language = "sl" shows every text - the components' too - in Slovenian until Access ends, whatever the Office language; texts.Language = "" returns to the measured language. texts.Missing then lists each key that had no text in the language asked for, one key language per line, so a test can check that a translation is complete.
• When the texts are read: the table and the language folder once per database and Access session. If the application changes them while it runs, texts.Reload reads them again.
• IntelliSense and member names stay in English; only the texts change language.

• Choose the language of the components
• API reference: WklTexts
• Texts appear in another language than expected