0

We have an Excel Macro that connects to an Oracle database. The connection string is as follows:

DRIVER=Oracle in OraClient10g_home1;ODA=F;TLO=O;FBS=60000;FWC=F;CSR=F;MDI=Me;MTS=T;DPM=F;NUM=NLS;BAM=IfAllSuccessful;BNF=F;BTD=F;RST=T;LOB=T;FDL=10;FRC=10;QTO=T;FEN=T;XSM=Default;EXC=F;APA=T;DBA=W;DBQ=<SERVER_NAME>;UID=<COMMON_USER_NAME>/<COMMON_PASSWORD>

This works in Excel 2010 for everyone in our group. But when we switch to Excel 2016 with the same file, it works for a few people, but the rest of us get this message:

Run-time error '-2147467259 (80004005)' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Does anyone know what the solution is?
Do we need to set a PATH variable for a different driver or change the connection string, or something else?

Thanks!

Community
  • 1
  • 1
Cody
  • 1
  • Did anything other than the version of Excel change? Did you get a new PC with a new version of Excel, or are you running the new version of Excel on the same machine the old version of Excel was installed on? – Bob Jarvis - Слава Україні Jul 02 '18 at 15:17
  • Only the version of Excel. It's running on the same computer, both Excel 2010 and Excel 2016 are installed. The database connection works in Excel 2010 but not Excel 2016. – Cody Jul 02 '18 at 15:31
  • Try `DRIVER={Oracle in OraClient10g_home1};` Are both Excel 32-bit? – Wernfried Domscheit Jul 02 '18 at 19:36
  • Do you have Excel 2010 and 2016 installed on the same machine? If not then most likely the Data-Source is not defied in ODBC-Adminstrator. Note, for 32-bit ODBC-Administrator your have to launch `C:\Windows\SysWOW64\odbcad32.exe` – Wernfried Domscheit Jul 02 '18 at 19:39
  • Try `Server=` instead of `DBQ=` – Wernfried Domscheit Jul 02 '18 at 19:41
  • @WernfriedDomscheit you are right, it turns out the Excel 2016 is 64 bit, while the Excel 2010 and Visual Studio are 32 bit. The ODBC 32-bit administrator has Oracle in Oraclient10g_home1 in its driver list, but the regular ODBC (presumably 64 bit) does not. Under the driver's tab for the regular ODBC administrator, it says "An ODBC driver allows ODBC-enabled programs to get information from ODBC data sources. To install new drivers, use the driver's setup program". Does this mean the original driver needs to be reinstalled in the 64-bit path? – Cody Jul 02 '18 at 19:53
  • Yes, a 64-bit application can use only 64-bit ODBC drivers. A 32-bit application requires 32-bit drivers. In case you like to install both, 32-bit and 64-bit Oracle Client (and ODBC drivers) on one machine, follow this instruction: https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 – Wernfried Domscheit Jul 03 '18 at 06:24

0 Answers0