How can I connect to a database using the 32 Bit ODBC on a Windows 7 (64 Bit) machine?

9

2

I am having difficulties using a DSN that was defined in the 32 Bit version of the ODBC-manager? (there are two separate managers for 32/64 Bit on a 64 Bit machine)

Using the DatabaseLink package will give an error as the DSN is not found and unfortunately I cannot find how to make it use the 32 Bit ODBC in the documentation.

gwr

Posted 2012-04-18T10:28:23.413

Reputation: 11 083

What error are you getting? – Verbeia – 2012-04-18T10:29:42.837

1This may become messy. We had a related problem and only solved it by switching to a freshly setup 64bit machine. Can you delete the 32bit ODBC DSN and try with a new 64bit one? – Yves Klett – 2012-04-18T10:58:00.847

In[160]:= conn = OpenSQLConnection[JDBC["odbc", "accessDatabase"]] (* where "accessDatabase" is a 32bit DSN *)

During evaluation of In[160]:= JDBC::error: [Microsoft][ODBC Driver Manager] Der angegebene DSN weist eine nicht übereinstimmende Architektur von Treiber und Anwendung auf.>> (Translation: DSN does not have a compatible architecture with regard to the driver.)

This is to reproduce the error; for further comments please refer to the Answer 1 where I have commented also. Thanks. – gwr – 2012-04-18T14:31:46.657

Answers

8

You might be facing the problem documented in Microsoft's knowledge base as KB942976. In a nutshell, the system call that enumerates DSNs on a 64-bit system will also list 32-bit DSNs -- even though those DSNs cannot be accessed from a 64-bit application. The knowledge base article states that there is no current resolution to this problem in the interest of backward compatibility (?).

It seems that you have only two choices:

  1. Run the 32-bit version of Mathematica to continue using the 32-bit DSN, or
  2. Create a new 64-bit DSN for use with 64-bit Mathematica.

WReach

Posted 2012-04-18T10:28:23.413

Reputation: 62 787

Thank you (all) for helping. My own research has revealed something similar, so the 32 Bit DSN cannot be used by Mathematica 64 Bit. Unfortunately while Office 32 Bit is installed Windows will not let me install the Access ODBC Driver for 64 Bit ;-( Guess that means running on 32 Bit in Mathematica (how?).

With DLL there is the option of Forcing 32 Bit with the Reinstall of the NETLink - would something similar work with the JLink? (After all it is Jlink that works underneath the Mathematica in DatabaseLink?)

Is there some other 64 Bit ODBC Driver that connects to Access (.accdb)? – gwr – 2012-04-18T14:26:05.460

2+1 for "in the interest of backward compatibility" :) – Dr. belisarius – 2012-04-18T16:03:48.367

4It is not Mathematica that needs to run in 32-bit mode, but rather the Java runtime used by J/Link. To force the use of a 32-bit JVM, you can do as follows. You probably already have a 32-bit Java installed at the following location, and if not, you can install one from java.com. Needs["JLink`"]; ReinstallJava[CommandLine->"C:\\Program Files (x86)\\Java\\jre7\\bin\\javaw"]. This is the J/Link equivalent of the Force32Bit option that is in .NET/Link. – Todd Gayley – 2013-07-31T15:53:15.167