I am working on migrating my back-end database from MS access to SQL server.
My access application is multi-user and multi-language based.
The front end adapts the language of the forms and reports based on a parameter set via an ini file at startup i.e. adapted to the preferences of the end-user.
In access I solved using a translation table in the front-end db and a translation class handling all the translations for forms, reports and popups.
Lookup tables (f.i. document types, contact types, account types ....) have a reference to a row in the translation table and if needed in a query I use a global function handling the call to the language class to return the requested data in the right language.
So some tables containing lookup data (not editable by users - but I want them in the back-end for data integrity) contain besides some parameters to control program flow also the type's name and description reference using to retrieve them in the language table and they sometimes appear in query results.
I am stuck implementing that last part (translation within a query) with an SQL server back-end.
Thought about 3 options:
1. create a view in SQL server, link the view as a table in access front end and then another query in access selecting the view with the extra column with the translate function.
2. same as above but instead of view a pass-through query and then a second query selecting the stored pass through query + translation.
3. Create a stored procedure or table-valued function passing the requested parameter as language and implementing the language lookup in SQL server.
A separate front-end per language is not a solution as it does not solve the lookup table's problem.
What is your advise, is there another solution ?
What I have tried:
Already converted the back-end db filled with test data and wrote and tested code for accessing it, but got stuck with that particular problem