So here's a fun little gotcha that I ran into today. One of my legacy apps, one of the first ones I built when I came to work at my current job, has graduated away from being in-house app to running on a third-party hosted e-commerce platform. As part of that transition, I believe the only things imported to the new platform were the products and maybe some customer accounts.
Anyway, the original application that had been running since around 2001 used a PostgreSQL database. The clients asked if it would be possible to get an export from the old database into an Access database, so they can refer back to the old data as needed. Keep in mind I work for state agency, so yes we regularly get requests about stuff that's for five years old. So in that context the request was certainly reasonable.
Now I believe I've noted here before that I am a fan of PostgreSQL, so the majority of the applications that I built in my career here use that DBMS. However, what we had a change of leadership a few years ago and formed a more formalized and team oriented group, we standardized on MySQL.
So while we still have those legacy PostgreSQL apps running, have really had to do much with the middle while other than what can be done with the piece of crap known as phpPgAdmin. So before I could fulfill this request export to Access I needed to install the ODBC drivers for PostgreSQL and set up a data source.
And that is where the gotcha came in. I'm not a newbie, so I know how to install and ODBC driver and set up a data source. And hey they had 64-bit drivers so I installed that and then open ODBC and set up my data source for my database. But other airlines so I still have the ODBC drivers I already had installed previously so cool.
So follow the usual procedure for setting up the data source, then opened Access and try to do the import table function. But it threw up in error about the data source because the data source uses 64-bit driver and I run Access 2003, which is obviously 32-bit. Had a had smacked moment, then went to edit the data source to switch it over to the 32-bit driver.
FYI even though it lets you do that it doesn't work. But then Access couldn't even see the data source. No problem, I'll just delete the data source and then reset it up from the start with the 32-bit driver. Except when I try to delete the data source it gave all kinds of weird errors about install files was not found.
Search and to hear an hour of uninstalling and reinstalling drivers, doing registry edits, and fun troubleshooting before I finally Googled the issue successfully enough to figure out what the hell is going on. (In my defense I was tired ;-P)
Basically Windows 7 64-bit, and presumably any other 64-bit version of Windows, has two ODBC administration tools: one for 64-bit ODBC connections and one for 32-bit ODBC connections. But they're both named the exact same thing: Odbcad32.exe! The only difference is where the executable is located.
- The 32-bit version is in %systemdrive%\Windows\SysWoW64
- The 64-bit version is in %systemdrive%\Windows\System32
If you just type in ODBC from the start menu, the only option that appears is the 64-bit one. Notice the extra screwed up pathing, with the 32-bit version being in a folder with 64 on it, and the 64-bit version being in the system 32 folder.
As for appearance, they look exactly alike:
Can you tell which is which? (Hint, they are in ascending order). The only I found to tell them apart is by looking at the drivers tab, which will only show 64-bit ones in that list. It will also only seemingly let you create 64-bit connections, except of course that I was able to easily mess up and make a 32-bit one that it couldn't edit. It also kept all of the older 32-bit ones I had when I upgrade from Win XP to 7 in the 64-bit area!
If you try to edit or delete those drivers from the 64-bit ODBC, you get the aforementioned error about install files not found. Is probably the most useless error ever because that's not the problem. It's purely an issue of the difference in 32-bit and 64-bit. Why Microsoft did not just make a single ODBC that could handle both, I don't know.
Once I finally got this concept in my mind, it was a simple matter of going to the start menu and typing in the full '%systemdrive%\Windows\SysWoW64\Odbcad32.exe' to access the 32-bit ODBC administrator and set up my data source there. Access was able to read that data source no problem and I was able to do the export that was acquired.
So if you find yourself needing to use a 32-bit ODBC driver for whatever reason, legacy apps, a burning hatred of all things Office 2007 and above, or whatever, you may want to make your own bookmark to the 32-bit ODBC administrator. Otherwise you'll have to remember to do that full path call every time you need to access those ODBC connections to make any changes.