MS Access 2016 — a bug finally fixed after 16 years
[UPDATE on February 2018 — make sure that you read Part 2 as well!]
Around ~2001/2002 we found a bug in Microsoft MS Access in a combination with Oracle ODBC.
Bug is finally fixed in MS Access 2016.
Imagine this Oracle table:
create table salary (name varchar2(10), salary number(7,2), salary2 number); insert into salary values ('King',12345.55,12345.55); 1 row created. commit; Commit complete. select * from salary; NAME SALARY SALARY2 ---------- ---------- ---------- King 12345,55 12345,55
Note that salary table has salaries stored in NUMBER(n,m) and plain NUMBER data types.
Let’s see what we get if we use MS Access 2010 with Microsoft ODBC driver for Oracle to link the salary table:
Everything is OK. Now, let’s try to link the same table, but this time with Oracle ODBC driver:
Oooppsss. MS Access converted decimal number 12345,55 to an integer 1234555. Someone would argue that this can also be a bug in Oracle ODBC. But it isn’t, because Excel (or any other ODBC app) has no problem handling decimal data over Oracle ODBC driver. This bug persisted in all recent releases MS Office 2000, 2002, 2003, 2007, 2010 and 2013. Using Oracle ODBC with any of those MS Access versions was a big NO for us.
The problem is that the only workaround was to link tables using Microsoft ODBC driver for Oracle, which is 32-bit only, deprecated by Microsoft and not enhanced in at least a decade. You can imagine my surprise after I installed 64-bit version of MS Office 2016 and trying to link to Oracle table with Oracle12c ODBC driver (also tested with older 11g driver) and realized that Microsoft finally nailed the bug after 16+ years.
Oh, and if you wonder if I submitted bug report to Microsoft and Oracle sixteen years ago? Yes, I did. Microsoft product manager simply redirected me to Oracle Support and Oracle support told me that they think that the problem are undocumented hooks in MS Access, hence, MS ODBC driver for Oracle somehow works and driver written by Oracle according to official ODBC specs doesn’t. Oracle suspicion was correct anyway, because the bug was always limited to MS Access.