ODBC between 32bit and 64bit programs

[Davegod]

I'd doing a bit of freebie work for a charity that's strayed into more of an IT problem than my own speciality.

I suspect the issue is that 64bit Excel cannot work with a 32bit ODBC driver, but my time and accessibility with the charity is very limited (also, it doesn't help that I have to get their IT contractor to put in the admin password all the time) so I'm hoping someone either knows a solution or can confirm my analysis.

For the full story:

Previously, they had everything 32bit under XP and I set up Excel to pull in data from another (32bit) program, using that program's ODBC driver. This fully automated a significant task for a senior manager, enabling her to do in a click what used to be a week-long task (and avoiding lots of scope for human error to boot).

They upgraded the data source software to a new version and after a bit of fiddling I was able to simply point it to the updated ODBC driver and all was well.

Then they upgraded to W7 64bit and MS Office 64bit. I tried the same trick but to no avail. At first I couldn't figure out why, after installing the ODBC driver, it wouldn't even show in the ODBC admin tool. After lots of head-scratching, it turns out there's two different versions of the ODBC admin tool. So I find the 32bit one, which shows the driver and I successfully configure it.

Now back into Excel... It still can't see the driver. I can't see it at all if I try to set up a new pivot table. I managed to get out of the Excel wizard and into VBscript itself, which can see the driver but then it tells me "architecture conflict" and refuses.

If reinstalling Excel with the 32bit version will work, they would definitely do this, but I don't want to suggest this unless I know it will work (not least because it means getting the IT contractor out). I'm wary that the architecture is really the problem since presumably there would be some wrapper or whatever to get around it, hooking up database data seems to me to be the kind of thing MS wouldn't want to break. Actually I'm surprised I'm not finding any Google hits for the exact software I'm using (Sage Line 50) since I'm sure lots of folks do exactly the same thing.


btw, apologies but I'm unlikely to be able to just try things out, as getting access to the organisation is extremely limited.

 

[Dahak]

You might as uninstall office 64bit and re-install it as 32-bit. there is not going to be an easy way to get a workaround

Even Microsoft recommends running 32bit unless you specifically need the advantages of 64 bit excel

Also from here

Databases that have had their source code removed (such as .mde, .ade, and .accde files) cannot be moved between 32-bit and 64-bit editions of Office 2010. Such databases that are created by using 32-bit Office (any version) can be used only with 32-bit Office, and a database that is created on 64-bit Office can be used only on 64-bit Office.

 

[Nothinman]

Yea, installing 64-bit Office is a fail right now.

 

[alent1234]

ODBC is a HUGE PITA

at work we had a SQL server that was kind of a router. we put a bunch of third party drivers on it and all it does is hit other db servers and transfers data to other SQL servers.

original x64 was a PITA to work with so when we put it on vmware i set it up as x86 and it was a lot easier to configure

go to start > run and I think the command is odbc32

 

[Nothinman]

ODBC is a HUGE PITA

at work we had a SQL server that was kind of a router. we put a bunch of third party drivers on it and all it does is hit other db servers and transfers data to other SQL servers.

original x64 was a PITA to work with so when we put it on vmware i set it up as x86 and it was a lot easier to configure

go to start > run and I think the command is odbc32

That's a PITA because you need 64-bit ODBC drivers for 64-bit apps and 32-bit ones for 32-bit apps, one can't use the other. And, just like hardware, I'm sure 3rd parties are slow to release 64-bit ODBC drivers because most Windows users are still using 32-bit apps.