ODBC Excel Driver Login Failed

[ArviL]

Hi



OS: Windows 7 64bit; Office 2007; Used Excel file type: *.xls (must be compatible with Office2000 and with Office2007)

I have an application where some data are entered into Excel worksheet (p.e. InputSheet). On sheet InputSheet, a nondynamic name p.e. MyData=InputSheet!$A$1:$X$10000 is defined. On another sheet, p.e. ReportSheet, I use an ODBC query with named range MyData as source to display certain data from InputSheet.

I have designed applications like this before, when I had Windows XP 32bit as OS and Office2000, and they worked perfectly.

So on ReportSheet, I select from menu Data>From Other Sources>From Microsoft Query>Excel Files* ..., and create a query. It works a treat, so long as the Excel session lasts.

After I close the Excel, and then open my Excel app anew, the error message is displayed whenever I try to refresh the query:
ODBC Excel Driver Login Failed
Unrecognized database format
'Mypath\Myfile.xls'

When I open ODBC Data Source Administrator, then on User DSN tab a data source is displayed:
Excel Files, Microsoft Excel Driver (*.xls,*.xlsx, *.xlsm,*.xlsb)

When I try to configure it, at first an error message is displayed:
The Setup routines for Microsoft Excel Driver (*.xls,*.xlsx, *.xlsm,*.xlsb) ODBC driver could not be found. Please reinstall the driver.

And after tha another one:
The specified DSN contains an architecture mismatch between the Deiver and Application

But the strangest thing is, that when I now return to my app, the query works again - until I close the Excel session. Only this will hardly be an acceptable working solution :-)


Any bright ideas?
Thanks in advance!


Arvi Laanemets

[ArviL]

Btw, to get the query refreshing again, there is no need to start the ODBC Administrator at all - it will be enough to click p.e. on Windows Start button, and then to activate the excel workbook again.

But though the query will refresh, when I try to edit it (Connections_Properties>Edit Query ...), an "Unrecogized database format" error is returned anyway.

[macropod]

A couple of possibilities incl;ude the use of wrong drivers:
http://www.microsoft.com/en-us/downl....aspx?id=23734 (though there doesn't appear to be a 64-bit version fo Office 2007). If you need 64-bit support, try the 2010 version:
http://www.microsoft.com/en-us/downl....aspx?id=13255
See also:
http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

[ArviL]

Originally Posted by macropod

See also:
http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

OK, it looks like I got the reason I have problem from here.
"
If you use the 64-bit odbcad32.exe to configure or remove a DSN that connects to a 32-bit driver, for example, Driver do Microsoft Access (*.mdb), you will receive the following error message:



The specified DSN contains an architecture mismatch between the Driver and Application



To resolve this error, use the 32-bit odbcad32.exe to configure or remove the DSN."

But the DSN used for queries from Excel files isn't user-defined! It was installed by MS Office Installer! So MS Office Installer didn't check, is there 32-bit or 64-bit OS? And no update or SP didn't correct this either?

The problem isn't that the query doesn't work properly in my computer - I can cope with this. But there are somewhat 200+ MS Office users in our plants all over 4 European countries, and any of them my need to use some of workbooks I have designed. When I go to bosses, and say: "Khm...! We have to employ some guy who will manage all those ODBC datasources!", then I hardly can expect some positive response :-)