All versions of Crystal Reports up to v14.2 (2016) need to use the 32-bit ODBC Administrator. But in Windows 7 and later you are taken to the 64-bit Administrator by default.  A couple of years ago I wrote about this.  Today after helping a customer I learned a few more strange things about the ODBC Administrator that can get confusing.

The user was creating DSNs but CR could only see the DSNs if he added it as a “user” DSN. If he added a new “system” DSN he could not see it. The odd part was that one existing system DSN DID show up in CR, but any new system DSN would not show up.

So the first thing I checked was that we were loading ODBCAD32.exe to add the new system DSNs. Then we recreated, reconfigured and retested the system DSNs. I also looked at the INI Files that ODBC creates and everything. So I was stumped. As I was telling them that I was giving up I checked the 64-bit ODBC shortcut I had added to my desktop. I was surprised to find that it was also named ODBCAD32.exe.  So the 64-bit ODBC Administrator is named exactly the same as the 32-bit ODBC Administrator and even has 32 in the name.  I even checked properties within both of the EXE files and there is no property that I could find to tell me which is which. The only way to know which one is which is by the folder it is stored in.  And then it gets even more confusing because:

• The 32-bit version is in Windows\SysWoW64
• The 64-bit version is in Windows\system32

So all along we were using the 64-bit ODBC Administrator and didn’t know it. That partly explained why the system DSN changes and additions didn’t show up. What it didn’t explain is why our user DSN changes were visible in CR. That was what fooled us. If nothing had shown up then I might have suspected we were in the wrong version.  I found the answer in this Kbase article from Microsoft:

——————
SYMPTOM:
The 32-bit version of the ODBC Administrator tool displays 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs. The 64-bit version of the ODBC Administrator tool displays 64-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs. [underlines are mine]

CAUSE:
The user DSNs are stored under the following registry subkey:
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
Registry redirection is not enabled for this registry subkey. Therefore, user DSNs are visible in both the 32-bit and 64-bit versions of the ODBC Administrator tool.

RESOLUTION:
To maintain backward compatibility, no resolution for this problem is currently available.
——————
After that we loaded the right ODBC and everything worked as expected.