Set regional settings in Access/VBA for current session


I have an Access 2003 application.   Lots of people are using it from notebooks, desktop and thin clients.  However, the users don't have access to their regional settings in the current corporate thin clients architecture.  Sometimes, the IT people do things with the roaming profiles and for some reasons their regional settings are changed.

I would like to get my app shielded from these modifications.

I could format each field everywhere but that would be a significant time investment for a legacy app and I don't want to change the user regional settings from my application (bad idea)

So I'm asking...

Is there a way to change the regional settings for only the current access session?  

I would like to use the default regional settings for english canada or french canada depending on a variable I control and not use the profile regional setting and not have to change it.

Is there a way to do this from Access 2003 / VBA ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It's up to you and system administrators to agree a course of action.
If the user does not have the permissions to do that then you can't do it from within code either, unless you simulate an adminstrator login in your code - and of course to do that you will have to be supplied with suitable username and password.
Francois_TremblayAuthor Commented:
Is there not a way that would look like this :

Application.SetLocale("FR-CA"); // ( french canada )
Application.SetLocale("EN-CA"); // (english canada )

Then the line that look like :
aTextVariable = Format(aDate,"dddd dd mmm yyyy")

could either return
sunday 14 april 2010
dimanche 14 avril 2010

independantly of their windows regional settings ?

Right now all the date format are directly linked to their regional settings and I would like to have a kind of "override" in my apps.

I don't want to change the regional settings of the user, just how my format works for date.  Number aren't that different so it's not a problem for the users.
A date is stored in Access as nnnnn.ddddddd   The user, via Regional Settings, sees Sunday, 14 April, 2010 or dimanche, 14 avril, 2010.  Why do you want to make the English user 'see' french settings or vice versa?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Attached is code for FormatDateByLCID (short or long) in:

English - Canada LCID 4105
French  - Canada LCID 3084

Other LCIDs available at this link:

Option Explicit

   wYear                As Integer
   wMonth               As Integer
   wDayOfWeek           As Integer
   wDay                 As Integer
   wHour                As Integer
   wMinute              As Integer
   wSecond              As Integer
   wMilliseconds        As Integer
End Type

Private Declare Function GetDateFormatA Lib "kernel32" (ByVal locale As Long, ByVal dwFlags As Long, lpDate As SYSTEMTIME, ByVal lpFormat As String, ByVal lpDateStr As String, ByVal cchDate As Long) As Long
Private Declare Function GetTimeFormatA Lib "kernel32" (ByVal locale As Long, ByVal dwFlags As Long, lpTime As SYSTEMTIME, ByVal lpFormat As String, ByVal lpTimeStr As String, ByVal cchTime As Long) As Long
Private Declare Function VariantTimeToSystemTime Lib "oleaut32" (ByVal vtime As Date, lpSystemTime As SYSTEMTIME) As Long

Private Const DATE_LONGDATE As Long = &H2
Private Const DATE_SHORTDATE As Long = &H1

Private Sub Form_Load()
   Dim bTime      As Boolean
   Dim sMsg       As String
   Dim sNow       As Date
   bTime = False ' True
   sNow = Now
   sMsg = "English - Canada: " & vbTab & FormatDateByLCID(sNow, 4105) & " " & FormatTimeByLCID(sNow, 4105) & vbNewLine & _
      "French - Canada: " & vbTab & FormatDateByLCID(sNow, 3084) & " " & FormatTimeByLCID(sNow, 3084)
   Debug.Print sMsg
   MsgBox sMsg
End Sub

'Purpose: Returns internationally correct formatted short or long date.
Public Function FormatDateByLCID(ByVal DateVar As Date, _
   ByVal LCID As Long, Optional ByVal bShortDate As Boolean) As String

   Dim st               As SYSTEMTIME
   Dim Buffer           As String * 255
   Dim ret              As Long

   VariantTimeToSystemTime DateVar, st
   GetDateFormatA LCID, IIf(bShortDate, DATE_SHORTDATE, DATE_LONGDATE), st, vbNullString, Buffer, 255
   FormatDateByLCID = StripNull(Buffer)
End Function

'Purpose: Returns internationally correct formatted short or long date.
Public Function FormatTimeByLCID(ByVal DateVar As Date, ByVal LCID As Long) As String

   Dim st               As SYSTEMTIME
   Dim Buffer           As String * 255
   Dim strTemp          As String
   Dim ret              As Long

   VariantTimeToSystemTime DateVar, st
   ret = GetTimeFormatA(LCID, 0&, st, vbNullString, Buffer, 255)
   strTemp = StripNull(Buffer)
   FormatTimeByLCID = strTemp
End Function
Public Function StripNull(StrIn As String) As String
   Dim nul              As Long
   nul = InStr(StrIn, vbNullChar & vbNullChar)
   If (nul) Then
      StripNull = Left$(StrIn, nul - 1)
      nul = InStr(StrIn, vbNullChar)
      If (nul) Then
         StripNull = Left$(StrIn, nul - 1)
         StripNull = Trim$(StrIn)
      End If
   End If
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Francois_TremblayAuthor Commented:
Hi !  

I want to be able to do so because, for some reasons, sometimes the roaming profiles of people are reset to the other languages.

So french user see english,or the other way around.

When this happens, they need to call the help desk, wait a few minutes, have their profiles configured, restart their thin client session, etc.  It brings a lot of frustration to the end user.

I would like to be able to "hardcode"  the user language directly in my app and not use the regional settings.  This way, english user will always see that application in english for dates and same for french users.
Code in Post 29159467 is independent of Regional Settings:

Debug.Print of code:

English - Canada:    March-31-10 12:13:04 PM
French - Canada:  31 mars 2010 12:13:04
There are 2 ways to get the LCID:

Private Declare Function GetUserDefaultLCID Lib "kernel32.dll" () As Long
Private Declare Function GetSystemDefaultLCID Lib "kernel32.dll" () As Long

If you think that the user may be changing the LCID just use GetSystemDefaultLCID and supply this Value to the code I supplied earlier.

danaseaman:  When referring to a post in this thread just type:  http:#a and copy and past the post ID after the 'a' as in http:#a29159467

Now you just click on the string to go to that specific post.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.