Click here to Skip to main content
15,790,435 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Gerry Schmitz 30-Apr-23 14:57pm    
"Translate" is a very broad subject. Substituting one picklist for another is different from translating free form text. Can't tell what patterns you're using.
Member 8101499 1-May-23 8:16am    
So sorry my naming was confusing.
My "translating" is in fact just looking up a table containing a unique ID and one column per supported language. The "Translate" function replaces the passed ID with the content of the current language column for that row when called from a query. As you mentioned in your reply it is just a substitution based on a reference pointing to a row in a table and a parameter loaded dynamically when starting the front-end.
Gerry Schmitz 2-May-23 9:29am    
Translation is done for the benefit of a user (i.e. what do they "see"; what do they "want") So, talking about back ends is the cart before the horse.
CHill60 2-May-23 5:35am    
It might help if you shared some sample data and what you would expect from that data. I'm struggling to see what the problem is.
I set up some simple tables
declare @languages table(langID int identity(1,1), langName nvarchar(50));
insert into @languages(langName) values

declare @formtext table(itemID int, formtext nvarchar(50), langID int)
insert into @formtext (itemID, formtext, langID) values
(1, 'forename', 1),
(1, 'prénom',2),
(1, 'vorname',3),
(2, 'surname', 1),
(2, 'nom de famille',2),
(2, 'nachname',3);
And then a couple of parameters - these would either be parameters to your query, or write a stored procedure to return the data
declare @userLang nvarchar(50) = 'french';
declare @phraseRequired nvarchar(50) = 'forename'
Then returning the correct word or phrase simply becomes
select @phraseRequired, t1.formtext
from @formtext t1
join @languages t2 on t1.langID = t2.langID and t2.langName = @userLang
join @formtext t3 on t1.itemID = t3.itemID and t3.formtext = @phraseRequired
If I'm completely wide of the mark then you will need to supply more information on your model
Member 8101499 3-May-23 9:51am    
Hi Chill60,
thanks for your answer but the problem is not at the SQL query level, the problem lies in the fact that per original setup the language is dynamically set when starting the Access frontend, so the Access frontend code has a language class property with the current language.
The language table has as columns: ID, Object, Field, E, F, N etc... No problem to write the query but to have the language available in the backend SQL server for each opened connection - it's a multi-user application.
Actually as I don't know how to pass that data for every connection separately I solved it by creating 2 queries in Access a pass-through query to pull the needed data from SQL server and another calling the first with a function looking up the language table that I leave in the frontend for now this way:
1. pass-through: Select Name,Amount,LanguageID from dbo.SQLServerTable WHERE ....
2. local: Select Name, Amount, languageData(LanguageID) FROM pass-through;
LanguageData() is a public Function (in fact I named it Translate()) in Access frontend that retrieves the data for the current language.
I wondered if there wasn't a better way.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900