[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Set regional settings in Access/VBA for current session

Posted on 2010-03-30
8
Medium Priority
?
4,160 Views
1 Endorsement
Last Modified: 2012-05-09
Hi!

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 ?

Thanks!
1
Comment
Question by:Francois_Tremblay
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 29114315
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.
0
 

Author Comment

by:Francois_Tremblay
ID: 29115543
Is there not a way that would look like this :

If(...)
{
Application.SetLocale("FR-CA"); // ( french canada )
}
else
{
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
or
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 29148136
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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 22

Accepted Solution

by:
danaseaman earned 2000 total points
ID: 29159467
Attached is code for FormatDateByLCID (short or long) in:

English - Canada LCID 4105
French  - Canada LCID 3084

Other LCIDs available at this link:
http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx

Option Explicit

Private Type SYSTEMTIME
   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)
   Else
      nul = InStr(StrIn, vbNullChar)
      If (nul) Then
         StripNull = Left$(StrIn, nul - 1)
      Else
         StripNull = Trim$(StrIn)
      End If
   End If
End Function

Open in new window

0
 

Author Comment

by:Francois_Tremblay
ID: 29218856
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.
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 29219235
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
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 29220605
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.




0
 
LVL 44

Expert Comment

by:GRayL
ID: 29230403
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.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question