Why is my 32bit ODBC connection not showing up in odbcad32.exe?

Question

I have a Windows 7 64bit system that must run a 32bit application that accesses a MSSQL Server 2005 express via an ODBC connection. After installation everything was working fine. Now a few weeks after installation I get a report that the application is no longer connecting to the database. I checked the C:\Windows\SysWow64\odbcad32.exe to see if there is a problem with the ODBC settings, and the ODBC connection was no longer visible. So I checked the registry to see if it was completely removed. The registry key is HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC When I opened that up the values were there, they just weren't accessible to the odbcad32 or my own application.

I was attempting to run this as a computer administrator so permissions shouldn't be an issue, and besides I checked to make sure that they weren't changed, but I still can't get it to work. Does anyone have any ideas on how to get the values back into the odbcad32 applet? That will most likely fix the software problem as well.

Solution 1

I think this article on my employer's site, about using 32-bit ODBC on 64-bit Windows, may help you out...

Nothing you've said, in your comment below nor in your original problem statement, clearly indicates that you're using the 32-bit ODBC Administrator. Note that both the 32-bit and 64-bit program are named the same, odbcad32.exe. The difference is in their counterintuitive directory location -- System32 (for 64-bit components) and SysWow64 (for 32-bit components).

You may be hitting an old but recurring issue with minor corruption in the Windows Registry. The corruption takes the form of entries containing this 4-character string --

@=""  

These entries aren't visible anywhere except Registry export files -- but they can lead to a number of undesired behaviors.

NOTE: On your 64-bit Windows machine, there are naturally some complications tied to the 32-bit Registry. This Microsoft KB article may be sufficient to get you through these.

I suggest that you use the 64-bit Registry Editor to export the following branches (where these entries tend to be found) --

HKEY_LOCAL_MACHINE\Software\ODBC
HKEY_LOCAL_MACHINE\Software\WOW6432Node\ODBC
HKEY_CURRENT_USER\Software\ODBC
HKEY_CURRENT_USER\Software\WOW6432Node\ODBC

Edit these files in any text editor (Notepad.exe or Wordpad.exe are generally fine), and delete all lines which consist of that 4-character string, above. Then, delete the Registry tree segment(s) you exported, and import from the edited files -- thereby restoring the tree segment(s), minus the corruption.

Solution 2

I had this same issue and here's how I fixed it for my System DSN.

For reference, here are the two paths in the registry for whether the ODBC is 64-bit or 32-bit on a Windows OS.

64-bit:

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

32-bit:

\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI

In the ODBC.INI folder there was a sub-folder with the name of the DSN with all its related keys, and another sub-folder called ODBC Data Sources that only had the (Default): (value not set) key.

In the ODBC Data Sources folder there should also be a key with the Name of the DSN entry, Type of REG_SZ and a Data value of the ODBC Provider/Driver name (in my case it was iSeries Access ODBC Driver). I set up another test to an MS SQL server, and the Data value was SQL Server.

How I discovered this was I completely deleted all DSN folders and the ODBC Data Sources folder and recreated my DSN from scratch. It created both folders and related keys, and now my DSNs are showing up and are usable.

Without the key with the provider name in the ODBC Data Sources, the DSN would not show up in odbcad32.exe. I could also recreate the issue by deleting the ODBC Data Sources entry.

Hopefully this helps someone if the other solution doesn't fix them. I tried exporting my registry keys and did not see the @="" as described in the other solution. Please let me know if these instructions aren't clear enough and I'll try to post screenshots. Thanks!

Comments

[Karan]

Have you tried creating a new connection, or perhaps deleting the old one from the registry and recreating it via the applet?

[JRSofty]

@Karan it seems it was a two fold problem. First the database file itself was corrupted which required a complete database rebuild. Then I did what you suggested, by removing the old DSN entry and re-created it and now it is working fine. What I still would like to know is "why" did this happen?

[week]

Have you tried running 32bit odbcad32 from c:\windows\SysWOW64\ instead of just running from default location c:\windows\system32\ .

[JRSofty]

Yes, I should have mentioned it. For this software I always run it from c:\windows\SysWOW64

[JRSofty]

While the information you give is correct that is not my problem. The application's installer does create a 32bit ODBC system DSN, and the application is installed in the `C:\Program Files (x86)` folder. The problem is that it wasn't shown in the 32bit ODBC administrator applet even though it was in the registry under the correct location.

[TallTed]

I've edited my answer. You may find what I was saying about the 32-bit vs 64-bit administrator clearer now ... and I've added some more information about another possibility.

[JRSofty]

Thanks, you are absolutely right, I should have mentioned that I was using the odbcad32.exe from the SysWow64 folder. I'm not sure what you mean by the 64bit Registry Editor. Normally I just enter regedit in the Start menu's search bar and open that. That may or may not be the 64 bit version. In any case I never saw a @="" entry there. Must I export the branches to see that entry?

[TallTed]

Yes, you have to export the branches to text files, to see the @="" entries. They are not visible in the RegEdit GUI. And, as the MS KB article I linked to says, Start -> regedit will get you the 64-bit Editor on 64-bit Windows. You have to specify the SysWow64 path to get the 32-bit Editor.

[JamesCW]

The ODBC Data Sources key was definitely my problem.

[Ramhound]

This should have been submitted as a comment to "answer 7". Since you do not have enough reputation to submit a comment, it means, you simply should not submit comments, until you have the appropriate privilege I have no idea which answer is "answer 7", we also have no idea what command you used, which is the reason I am treating this as a comment submitted as an answer

[JRSofty]

I think he was referring to the one with 7 votes above. However you are correct this should be a comment.