ODBC Query Error

[Mike Murphy]

Hi Statalisters,

I am running Stata 13 on a 64-bit Windows machine and trying to configure an ODBC connection to pull data from MS Access 2010. I was able to successfully set up the Data Source my_source using C:\Windows\SysWOW64\odbcad32.exe and when I run

odbc list

my_source appears.

However, running:

odbc query "my_data", dialog(complete)

Returns:

The ODBC driver reported the following diagnostics
Specified driver could not be loaded due to system error 126: The specified module could not
be found. (Driver do Microsoft Access (*.mdb), C:\Windows\system32\odbcjt32.dll).
SQLSTATE=IM003

I think the issue is that the 32-bit driver is in the wrong place. Has anyone run into this issue? I'm not sure whether I should be trying to configure the 64-bit driver instead, or if I need to move a 32-bit driver into the folder specified in the error message.

Thanks,

Mike

[Joseph Coveney]

I believe that 64-bit applications, including Stata 64-bit in your case, cannot make use of 32-bit ODBC drivers. You should be using 64-bit ODBC drivers to your data sources. There might be a wrinkle in that, if you have installed 32-bit Access 2010, then you might not be able to install the 64-bit ODBC driver for Access. As I recall, they couldn't co-exist on the same machine, or something, and so I had to go completely 64-bit for everything, including the Microsoft Office 2010 applications. You might want to verify whether that's still the case.

[Mike Murphy]

Thanks Joseph- I am indeed running 64-bit Stata and 32-bit Access, so that may be at the root of the problem. I'll do some digging and see if I do indeed have to switch Office to 64 bit

[Adrian Sayers]

Martyn Sherrif gave me the solution a few days ago. very easy fix.


I do not know if it will help but I am running Stata 64 and Office 32 and I can get ODBC connectivity to Access after following this link:

http://blog.codefluententities.com/2...distributable/

The important thing is the line:

AccessDatabaseEngine_X64.exe /passive

when you are installing it.

Martyn

[Tim Wade]

I ran into this exact same problem and another workaround is to use the 32 bit version of Stata for ODBC applications when you need to interact with MS Office 32 bit. 32 and 64 bit Stata are both included with a Stata license- this is the response I received from tech support:



Dear Timothy,

Any Stata license is good for either 32-bit or 64-bit Stata. We do this because not everyone has a 64-bit machine, or sometimes they have a 64-bit machine and a 32-bit machine that they want to run Stata on. This means you can install both the 32-bit and 64-bit versions of Stata using the same license.

So for your purposes, you can install the 32-bit version of Stata on the same machine that you have the 64-bit version installed. Then, when you need to get an ODBC file, you would use the 32-bit Version of Stata to get the ODBC file, and then save that file as a Stata .dta file on your machine. Once the file has been saved as a Stata .dta file, you can load that file into your 64-bit version of Stata with the -use- command.

To install 32-bit Stata, just put in your Stata DVD and select the "Modify"
option when the DVD runs. This should give you the option of adding the 32-bit version of Stata MP. Just make sure that you install the 32-bit version of Stata in the Microsoft "C:\Program Files (x86)\Stata13" installation directory so it does not interfer with the 64-bit installation of Stata (usually in "C:\Program Files\Stata13" installation directory).


Tim

[Mike Murphy]

Thanks Martin- I was able to run the install file from the command line as you described, but then ran into the same problem after adding the ODBC source (I can add the ODBC source via the path I mentioned, but still not via the control panel). Did you have to make any other additional configuration changes?

Thank you Tim, if I can't configure the driver correctly that is something I can explore.