The Problem
If ever you get into developing applications, especially Access databases, that are deployed on various countries and/or where users change the default Regional settings you will quickly ask yourself why, oh why, did Microsoft make things so complex.
For example creating a value list through VBA, some languages use a comma (,), others the semicolon (;) and so on, and so on it goes for decimal separators, date separators, …
So hard coding the separator is a no-no unless you are in a very controlled environment!
Let’s look at a simple example, such as getting a listing of all the tables within a database to be used as a Value list of a combo box. Normally, our code would look something like:
'---------------------------------------------------------------------------------------
' Procedure : listTables
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Generate a separated listing of all the database tables to be used
' as a value list for instance
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bShowSys : True/False -> Include system tables or not
'
' Usage:
' ~~~~~~
' sTbls = listTables(True)
' sTbls = listTables(False)
' Me.cboName.RowSource = listTables(True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-06-10 Initial Release
'---------------------------------------------------------------------------------------
Function listTables(bShowSys As Boolean) As String
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim td As DAO.TableDefs
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo NextTable
listTables = listTables & t.Name & ","
NextTable:
Next
'Truncate the trailing comma on the last entry
If Len(listTables) > 0 Then listTables = Left(listTables, Len(listTables) - 1)
Error_Handler_Exit:
On Error Resume Next
If Not td Is Nothing Then Set td = Nothing
If Not db Is Nothing Then Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: listTables" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
The issue with the above is the comma in the following line of code because that may not be the list separator defined in the user’s computer settings.
listTables = listTables & t.Name & ","
Don’t get me wrong, the VBA code will run just fine. It is when you go to assign the string to a RowSource (…) that you will face problems.
So what are we to do? What’s the Solution
In Excel, they have a very simple command to determine the values of such separators
Application.International(xlListSeparator)
but there is nothing like that in Access! (banging my head against the wall. Microsoft’s standardization between applications is mind blowing!!!!)
Well, the fact is there are a couple approaches we could take: API, Registry, …
API
Declare Function GetLocaleInfo Lib "kernel32" Alias _
"GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String, ByVal cchData As Long) As Long
Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Public Const LOCALE_SLIST = &HC
Public Function GetListSeparator() As String
On Error GoTo Error_Handler
Dim ListSeparator As String
Dim iRetVal1 As Long
Dim iRetVal2 As Long
Dim lpLCDataVar As String
Dim Position As Integer
Dim Locale As Long
Locale = GetUserDefaultLCID()
iRetVal1 = GetLocaleInfo(Locale, LOCALE_SLIST, lpLCDataVar, 0)
ListSeparator = String$(iRetVal1, 0)
iRetVal2 = GetLocaleInfo(Locale, LOCALE_SLIST, ListSeparator, iRetVal1)
Position = InStr(ListSeparator, Chr$(0))
If Position > 0 Then
ListSeparator = Left$(ListSeparator, Position - 1)
GetListSeparator = ListSeparator
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetListSeparator" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Registry
All of these ‘mysterious’ settings are actually neatly stored in the registry and are easily accessible in the HKEY_CURRENT_USER\Control Panel\International key.
And with the following single line of code we can easily read any one of them by simply changing the key to read
CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Control Panel\International\sList")
As always though, we should turn into a properly reusable function like
'---------------------------------------------------------------------------------------
' Procedure : GetInternationalSetting
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the system's regional setting values for internaltional chrs
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sKey : The registry key to read (sList, sDecimal, sDate, sThousand, ...)
'
' Usage:
' ~~~~~~
' GetInternationalSetting("sDecimal")
' GetInternationalSetting("sList")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2017-09-01 Initial Release
'---------------------------------------------------------------------------------------
Function GetInternationalSetting(ByVal sKey As String) As String
On Error GoTo Error_Handler
GetInternationalSetting = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Control Panel\International\" & sKey)
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetInternationalSetting" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
With this new information in hand, we can then adapt our original table function so it become flexible for all PC’s and regions of the world by doing
Using the API
'---------------------------------------------------------------------------------------
' Procedure : listTables
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Generate a separated listing of all the database tables to be used
' as a value list for instance
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bShowSys : True/False -> Include system tables or not
'
' Usage:
' ~~~~~~
' sTbls = listTables(True)
' sTbls = listTables(False)
' Me.cboName.RowSource = listTables(True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-06-10 Initial Release
' 2 2017-09-01 Internationalized version
'---------------------------------------------------------------------------------------
Function listTables(bShowSys As Boolean) As String
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim sDelim As String
sDelim = GetListSeparator
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo NextTable
listTables = listTables & t.Name & sDelim
NextTable:
Next
'Truncate the trailing comma on the last entry
If Len(listTables) > 0 Then listTables = Left(listTables, Len(listTables) - Len(sDelim))
Error_Handler_Exit:
On Error Resume Next
If Not td Is Nothing Then Set td = Nothing
If Not db Is Nothing Then Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: listTables" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Using the function
'---------------------------------------------------------------------------------------
' Procedure : listTables
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Generate a separated listing of all the database tables to be used
' as a value list for instance
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bShowSys : True/False -> Include system tables or not
'
' Usage:
' ~~~~~~
' sTbls = listTables(True)
' sTbls = listTables(False)
' Me.cboName.RowSource = listTables(True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-06-10 Initial Release
' 2 2017-09-01 Internationalized version
'---------------------------------------------------------------------------------------
Function listTables(bShowSys As Boolean) As String
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim sDelim As String
sDelim = GetInternationalSetting("sList")
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo NextTable
listTables = listTables & t.Name & sDelim
NextTable:
Next
'Truncate the trailing comma on the last entry
If Len(listTables) > 0 Then listTables = Left(listTables, Len(listTables) - Len(sDelim))
Error_Handler_Exit:
On Error Resume Next
If Not td Is Nothing Then Set td = Nothing
If Not db Is Nothing Then Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: listTables" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Of course, then we’d need to perform the same operation through our code that builds lists, equations, … everywhere regional setting characters are used! This is when defining these regional settings as TempVars at the startup of your database makes a lot of sense and modifying function to accept the sDelim as an input variable can greatly simplify your coding life.
Another Approach
Instead of embedding the internationalization code within each function, another approach would be to leave your tried and tested functions alone, and simply use the Replace function when calling them. So you’d take something like
Me.cboName.RowSource = Replace(listTables(True), ",", GetInternationalSetting("sList"))
This way you don’t need to change any of your existing procedures. On the other hand, you need to always remember to add the Replace function to every call. There can also be issues with this approach should the string you are performing a replace on include any comma beyond those used as a list separator.
The choice is yours depending on your needs.
What’s also great with the above API or Function is that it will work in every VBA application! No more need to know in Excel you use this command, … now with one solution you can rule them all!
Update From a Comment
In a recent comment, Joe, stated
So I created a bit of code to do exactly that in case it could possibly help someone out there.
'---------------------------------------------------------------------------------------
' Procedure : Regisrty_ExportInternationalSettings
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Enumerate all the International Setting Values and create a csv file on
' the user's desktop
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' Dependencies: Txt_Append -> https://www.devhut.net/vba-append-text-to-a-text-file/
'
' Usage:
' ~~~~~~
' Call Regisrty_ExportInternationalSettings
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2022-01-27 Blog Comment Request/Feedback
'---------------------------------------------------------------------------------------
Sub Regisrty_ExportInternationalSettings()
On Error GoTo Error_Handler
Dim oReg As Object
Dim aNames As Variant
Dim sName As Variant
Dim aTypes As Variant
Dim sValue As String
Dim sOutput As String
Const HKEY_CURRENT_USER = &H80000001
Const sParentKey = "Control Panel\International"
Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
oReg.EnumValues HKEY_CURRENT_USER, sParentKey, aNames, aTypes 'Get all the Values within the sParentKey
For Each sName In aNames 'Iterate through each Value
oReg.GetStringValue HKEY_CURRENT_USER, sParentKey, sName, sValue
sOutput = sOutput & Chr(34) & sName & Chr(34) & "," & Chr(34) & sValue & Chr(34) & vbCrLf
Next
If sOutput <> "" Then sOutput = Left(sOutput, Len(sOutput) - 2)
Txt_Append Environ("USERPROFILE") & "\Desktop\InternationalSettings.csv", sOutput
Error_Handler_Exit:
On Error Resume Next
If Not oReg Is Nothing Then Set oReg = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Regisrty_EnumerateKeyValues2" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
This procedure also requires a copy of my Txt_Append() function, so be sure to grab a copy of that as well as the code above for it to work properly.
Thanks Daniel for that code. A point of clarification:
If my combo box rowsource is defined with semicolon separators in my .accdb (my default LCID is 1033), and the db is distributed as .accde, will the combo box work “as is” for all locales?
Or, is it necessary to define the rowsource in VBA, using your approach, for the combo box to work for all locales as a single .accde?
I’m assuming that if the rowsource was defined in VBA and compiled locally, the combo box would work fine for that locale. But I don’t really want to distribute the .accdb.
I’m OK (for now) with having English-only list items: the target users are English speakers/readers, though potentially in different locales.
A more general question:
What would it involve to internationalize my application, at least to the point of code functionality without translation?
I saw this scary list of Locale-Sensitive Visual Basic Methods and Properties:
https://www.globalyzer.com/gzserver/help/reference/localeSensitiveMethods/vbunsafe.html#vbWin32
I assume that what applies to VB will also apply to VBA
Based on this do I need to make changes to all of these items listed, or will the fact that the .accde was compiled in LCID 1033 make them all OK, though dates will be shown in US format?
For example, “DateValue” is on the list, and I use “DateValue” to get a UTC timestamp:
Public Function GetUTCTimeDate(DateOnly As Boolean) As Date
‘from https://stackoverflow.com/questions/48371398/get-date-from-internet-and-compare-to-system-clock-on-workbook-open
‘UTC time is Universal Time, used to be GMT
Dim UTCDateTime As String
Dim arrDT() As String
Dim http As Object
Dim UTCDate As String
Dim UTCTime As String
Const NetTime As String = “https://www.time.gov/”
On Error Resume Next
Set http = CreateObject(“Microsoft.XMLHTTP”)
On Error GoTo UTCTimeDateError
http.Open “GET”, NetTime & Now(), False, “”, “”
http.Send
UTCDateTime = http.GetResponseHeader(“Date”)
UTCDate = Mid(UTCDateTime, InStr(UTCDateTime, “,”) + 2)
UTCDate = Left(UTCDate, InStrRev(UTCDate, ” “) – 1)
UTCTime = Mid(UTCDate, InStrRev(UTCDate, ” “) + 1)
UTCDate = Left(UTCDate, InStrRev(UTCDate, ” “) – 1)
If DateOnly Then
GetUTCTimeDate = DateValue(UTCDate)
Else
GetUTCTimeDate = DateValue(UTCDate) + TimeValue(UTCTime)
End If
Exit Function
UTCTimeDateError:
GetUTCTimeDate = DateValue(“14 Jun 1947”)
End Function
PS: When I said “my combo box rowsource is defined with semicolon separators in my .accdb”, what I meant to indicate was that the rowsource is entered directly into the Row Source field on the Property Sheet.
Hi Daniel
Thank you for your post.
I am from Oman, we use Arabic, it is Unicode and RTL.
I am a moderator of an Arabic MS Access Forum, and our members besides having localized Windows in English, Arabic, some use French too, and we get a lot of the following error:
The expression On Load you entered as event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.
The solution to the above issue is with the Regional Unicode setting, use either an Arabic Country, or France for the programs done in Algeria or Morocco.
Recently I came across Better Access Charts post, which was done on a German Localized PC I assume
https://github.com/team-moeller/better-access-charts/releases/tag/2.19.06
and once I downloaded it, I was hit with the same error above (when opening Any Form), I was able to get the program to work by making my own Forms elements, then copy/paste the code from the original Forms.
I then used SaveAsText both the Forms, but couldn’t see any reference or LCID for the Original Form which was not working !! I was hoping to change a setting and getting it to work, but !!
Besides the issues you mentioned in your article, please add this this issue too !!
jaffer
Hi
Is there a way to identify which language/Region the program was done with, and how to get to that reference/setting in the DB.
thanking you
jaffer