I recently contributed to a forum thread, Multi Language Support, regarding multi-lingual databases and thought I’d elaborate a little on the subject based on my experience.

The Obvious

When you start thinking of trying to develop a multi-lingual application you look at your forms and reports and quickly realize that you need to create the means to provide translation for things like:

• Buttons
• Labels

The Less Obvious

Quite often, people overlook a great many other aspects of the database that need translating like:

• Form/Report Captions
• Combobox/Listbox Row Sources
• Control Default Values
• Control Tip
• Tags if you use them in certain ways (generate pop-ups, …)
• Error Messages, Dialogs, Prompts, …
• Date/Time values as different languages often use different formats
• Documentation

Translation Approach

Over the years, I’ve developed numerous multi-lingual Access databases. You basically have 3 main translations approaches you can employ:

• Real-time dynamic translation
• Real-time static table based translation
• Create separate FE for each language

Let us look at each of these briefly.

Real-time dynamic translation

This approach involved using an online translation service (think Bing, Google, …) to perform the translation in real-time. This requires permanent internet connectivity.

In my experience, I tried using real-time Google Translations, but abandoned this approach because the translations are not always good giving a very poor user experience. There can also be a performance hit depending on your Internet connectivity and quantity of translations to be performed.

Real-time static table based translation

This approach is based on building a table that hold all your database translations and then using the table to drive the real-time translation of your database objects.

In my mind, Table driven translation is definitely the way to go (you can use google here to initially perform the translation of all your terms, but be sure to carefully go through them afterwards to ensure they are correct!). The issue here is the fact that for simple forms it is almost instantaneous, but for very complex forms (with multiple subforms) is can noticeably slow down the opening process.

Create separate FE for each language

I have seen some developer create and maintain independent front-ends, one per language. The benefit is that there is no real-time processing going on, so you have optimal performance.

That said, this is the worse solution and I highly advise against it as it doubles, triples, … your workload as a developer as you now have to replicate code, test, … in multiple separate applications. It is a nightmare to manage.

I’ve also seen cases where the developer created multiple versions of objects for each language within the same database. Then they call the appropriate version depending on the user’s language. This is to causes major duplication of work, increase the size of the database and I advise you avoid.

A Better Solution?!

When I first started creating multi-lingual databases I used the Real-time static table based translation approach and, in a general sense, it works very well.

For some of my bigger databases, what I’ve actually done is use the best of both worlds. I use a table to manage all the translations, but as part of my release process, I have a function that translates all the objects and generates a static database for each language. Thus, I have a table driven translation, but, for the end users, there is no real-time translation occurring impacting performance. Better yet for me, I only have one database to maintain!

Some Gotchas To Watch Out For

There are a several special headaches that you may need to deal with when developing a multi-lingual interface which might include:

• Depending on the languages involved, you may also need to dynamically change the font size of certain controls to make the translated content fit properly in the existing control dimensions.
• Separators and other delimiters – when translating row sources and the likes you will need to check the user’s regional settings to ascertain and use the proper separators for there setup. A semi colon may work for you, but on another PC may need to be a comma. This is why I wrote posts like Special Characters and Internationalization of an Application

Conclusion

I hope this discussion helps some of you and avoids you falling into a few of the pitfalls I originally did when I first set out developing my first few multi-lingual applications.