Only recently, I realized that I was dead wrong (two years ago), claiming that Microsoft finally fixed a bug in Microsoft Access 2016 that caused wrong results whenever we use a combination of:

• linked tables via Oracle ODBC driver,
• Oracle table contains attribute(s) with data type NUMBER(n,m),
• and Windows client OS is properly using whatever (Eastern Europe) regional settings is valid for the country. In Slovenia we’re using comma as a decimal separator.

If above requirements are met, then MS Access (any version!) interprets the decimal number as integer (2342,45 becomes 234245). This can be a potential hefty salary raise for someone.

When I reported that Microsoft finally fixed a bug, I didn’t realize that I was working on Windows 10 VirtualBox VM, where I freshly installed Oracle Client 12c, but didn’t bother to check NLS_LANG in the registry. As it is usually the case, Oracle installer puts (incorrect) NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 in the registry, instead of correct one, NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250.

While preparing some new standard PC configuration for our current customer, I was surprised when I found out that an old bug is back. It was “back” because on this “matrix” machine Oracle client was properly setup with NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250. Since going back to 32-bit MS Office 16 and Microsoft ODBC driver for Oracle (only 32-bit version is available!) was not an option, we were left to figure out some workaround. And we did. We found out that MS Access interprets decimal data type in Oracle table correctly, if we change territory from Slovenia to America (without changing *any* other regional settings at OS level!).

Something like this:

-- open cmd, setup NLS_LANG, then run MS Access
cmd> SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250
cmd> "C:\Program Files\Microsoft Office\Office16\msaccess.exe"

Changing NLS_LANG variable at registry (machine) level to SLOVENIAN_AMERICA.EE8MSWIN1250 doesn’t sound as a smart move, just for the sake of fixing a bug in one faulty product, so we decided to write some simple launcher for MS Access that can fix NLS_LANG variable behind a scene, leaving NLS_LANG in registry intact.

Below is a simple Go code that’ll run MS Access after properly setting up territory part of NLS_LANG variable. Note that we deliberately used absolute paths to MS Access binary that works for our Windows 10 standard configuration, something that you might want to change if you decide to use it. You can change language and character set part of NLS_LANG variable as well.

To build executable from the code follow these steps:

• download and install Go
• save code in some file, such as G:\SRC\runAccess.go
• compile code, open cmd and execute: cd G:\SRC & go build
• ** or, you can compile the code with: go build -ldflags -H=windowsgui

** The alternative build with the -ldflags -H=windowsgui is optional but recommended. It will cause console window to close after it launches MS Access.

// runAccess.go -- MS Access launcher
package main
 
 import (
	"fmt"
	"os"
	"os/exec"
	"flag"
 )
 
func main() {

	var version *bool
	var help *bool
	
	version = flag.Bool("version",false,"18.02")
	help = flag.Bool("help",false,"MS Access 2016 Launcher") 
	
	flag.Parse()
	
	if *version {
		fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
		os.Exit(0)
	}
	
	if *help {
		fmt.Println("I'm here to silently run this code for a user:")
		fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe")
		os.Exit(0)
	}
	
    if (os.Args != nil && len(os.Args) > 1) {
		cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe", os.Args[1])
		if err := cmd.Run(); err != nil {
			fmt.Println("Error: ", err)
		}
	} else {
		cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe")
		if err := cmd.Run(); err != nil {
			fmt.Println("Error: ", err)
		}
	}		
}

You can do two things with runAccess.exe. You can run it as a standalone program. It’ll open MS Access with territory part of NLS_LANG set to AMERICA. Or, you can associate *.accdb or *.mdb file extension with this launcher and it’ll correctly start MS Access if user double clicks on MS Access database file.

If you’re wondering what is perhaps a negative consequence of tweaking territory part of NLS_LANG variable, then you can refer to Oracle official NLS lang FAQ, a quote:

What does the TERRITORY component of the NLS_LANG parameter control?

The territory component of the NLS_LANG parameter controls the operation of a subset of globalization support features. It specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA , FRANCE , or CANADA . If the territory is not specified, then the value is derived from the language value.

We can certainly leave with “wrong” monetary value and having american “default date” is easy to fix with a proper NLS_DATE_FORMAT.

UPDATE: April 11, 2018

I added a small troubleshooting feature to my Golang code that allows end users to easily check if they run instance of MS Access spawned from my runAccess app. I added /cmd at the end of the command line with text NLS_TERRITORY=AMERICA. This MS Access related “command line switch” is usually read by VBA function Command(), but can also be viewed from:

File -> Options -> Client Settings -> Advanced -> Command-line arguments NLS_TERRITORY=AMERICA

package main
 
 import (
	"fmt"
	"os"
	"os/exec"
	"flag"
 )
 
func main() {

	var version *bool
	var help *bool
	
	version = flag.Bool("version",false,"18.03")
	help = flag.Bool("help",false,"MS Access 2016 Launcher") 
	
	flag.Parse()
	
	if *version {
		fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
		os.Exit(0)
	}
	
	if *help {
		fmt.Println("I'm here to silently run this code for a user:")
		fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe /cmd NLS_TERRITORY=AMERICA")
		os.Exit(0)
	}
	
    if (os.Args != nil && len(os.Args) > 1) {
		cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe " + os.Args[1] + " /cmd NLS_TERRITORY=AMERICA")
		if err := cmd.Run(); err != nil {
			fmt.Println("Error: ", err)
		}
	} else {
		cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe /cmd NLS_TERRITORY=AMERICA")
		if err := cmd.Run(); err != nil {
			fmt.Println("Error: ", err)
		}
	}