Making an Access application bilingual

[Eniac]

Hello there,

I'm currently developing an MS Access 2002 application that I'd like to be bilingual.

I'm used to making bilingual stuff but for websites and I find it quite different to do it efficiently when dealing with forms.

Currently, I have tried to use a web technique and convert it for my forms.

Basically, I have a table that holds all my labels, classified by categories and owners, then when I load form, I do some VBA scripting to replace the content of my captions.

and it works...but because I always have to mess with a recordset, it gives me a feeling that its crappy and sluggish.

I have tried saving the recordset in XML and using the DOM to do XPath queries on the document (like I do for a website) but I'm not really used to dealing with the DOM object directly and its beginning to turn in a nightmare to run a XPath query with all those undeclared namespaces and all...

well anyway...all I meant to ask was what *you* do to make it efficiently bilingual ?

Thank you

 

[Mile-O]

If I were to make something biliingual I'd try to emulate the Resource File in Visual Basic.

I'd make a table with three (or more) fields, for example: TextID, English, German

The TextID field is an Autonumber, and the other two are text into which I place both sides of the translation. For each caption, message box, etc. I simply assign the TextID number and write a function to get the appropriate text based on what language is selected.

Consider the attached example, it was a half-finished database that, to be honest, doesn't do much, but lets you switch language. You may need to hold down Shift to open it - I can't remember.

 

[Eniac]

Hey thanks SJ, that was precisely what I was searching for, it implements exactly what I did my my "labels" table.

I really like the way you've used numbers and a loop to indicate what fields and labels are for processing.

I'm still a little concerned about the performance when I'll I separate the forms from the database. From what I understand, DLookup makes a quick and simple query on a table to get a value, this means that if the tables aren't in the same mdb as the forms, we'll highly depend on traffic network to get a fast app.

I think I'll still work toward reading all the labels in one shot then prolly use a self-made variant of DLookup.

You've put me on the right way man, thanks a lot!!

 

[FoFa]

helped a friend do something like this awhile (long while) ago. basically the same theroy, except he used delimited labels in a single column for each form (or in one case each tab on a form). used a single Dlookup per form (or Tab) to pull the data in, parsed it to an array and loaded the labels from the array. He had 4 languages he was doing this with so had 4 colulmns for each form depending on the language. This worked pretty good doing a single Dlookup for a form rather than one for each lable.

 

[Eniac]

I like to share the results of my work when I got helped by people and when i think it can be useful others.

So here it is. It's a mix of what SJ attached to his first post and what I already had done before posting.

Basically, its a generic function that accepts a Group name and a form name. it'll read all the labels for that group in the form, loop thru the form and replace the various labels (or any fields ....) using XPath query on a modified ADO XML Recordset

It allows to me to do everything in a single and also seems pretty efficient. Its not bulletproof, I'm probably gonna add some error handling later but I think it covers the basics that needs to be done, all the rest is a matter of customization.

Thank you so much guys for your useful insight!

Public Sub Load_Labels(ByVal prmstrGroupName As String, _
                       ByVal prmstrDestinationForm As String)

Const cRECORD_GROUP As Byte = 0
Const cRECORD_CODE As Byte = 1
Const cRECORD_LABEL As Byte = 2

Dim objXML As MSXML2.DOMDocument40
Dim objNodes As MSXML2.IXMLDOMNodeList

Dim objStream As ADODB.Stream
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String

Dim strXMLDoc As String
Dim ctl As Control
Dim frm As Form


Set objConn = CurrentProject.Connection
Set objRS = New ADODB.Recordset

strSQL = "SELECT Group, Code, Label From CD_Labels L, App_Constants C " & _
         "Where L.CD_Lang__ID = C.User_Language " & _
           "AND L.Group = '" & prmstrGroupName & "'"

Set objRS.ActiveConnection = objConn
objRS.Open strSQL, , adOpenForwardOnly, adLockReadOnly

Set objStream = New ADODB.Stream
objRS.Save objStream, adPersistXML
objRS.Close
Set objRS = Nothing
Set objConn = Nothing

strXMLDoc = objStream.ReadText()
strXMLDoc = Replace(strXMLDoc, "rs:data", "recordset") 'For some reason we can't xpath on those so im replacing them
strXMLDoc = Replace(strXMLDoc, "z:row", "record")

objStream.Position = 0
Call objStream.WriteText(strXMLDoc)
objStream.Position = 0

Set objXML = New MSXML2.DOMDocument40
Call objXML.loadXML(objStream.ReadText())
Set objStream = Nothing

Set frm = Forms.Item(prmstrDestinationForm)
'We base ourselves on the label name to identify which label to use in XML doc
On Error Resume Next
For Each ctl In frm 'Depending on type of field, they may or may not have all the attributes we're affecting
  Set objNodes = objXML.selectNodes("//recordset//record[@Code='" & ctl.Name & "']")
  If objNodes.length = 1 Then 'Check if matches only 1 (as it should....) - add error handling on that
    ctl.Caption = objNodes.Item(0).Attributes(cRECORD_LABEL).Text
    ctl.ControlTipText = objNodes.Item(0).Attributes(cRECORD_LABEL).Text
    ctl.StatusBarText = objNodes.Item(0).Attributes(cRECORD_LABEL).Text
  End If
Next
On Error GoTo 0

Set ctl = Nothing
Set frm = Nothing
Set objNodes = Nothing
Set objXML = Nothing

End Sub

 

[Pat Hartman]

Is it really necessary to change all the labels each time a form is loaded? Why not use a one time procedure that "initializes" the db to the requested language? This routine can update the label captions and save them.