RE: st: Access 2010 32bit vs Stata 64bit

[Richard Fox <r.p.fox@bham.ac.uk>]

Many thanks for this Max. 
I'll give it a try.

Best Regards

Richard Fox
Biostatistician - CRCTU
Ext: 43410

DISCLAIMER: This e-mail is for the exclusive use of the intended recipient(s) only. If an addressing or transmission error has misdirected this e-mail and you are not the intended recipient(s), please accept our apologies. Please do not disclose, copy or distribute information in this e-mail or take any action in reliance on its contents: to do so is strictly prohibited and may be unlawful. Please inform us that this message has gone astray by replying to it before deleting it. Thank you for your co-operation. 


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Max Bo
Sent: 20 September 2012 11:36
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: Access 2010 32bit vs Stata 64bit

Dear Richard,

Microsoft has still not published a OSBC-Driver for 64bit applications that want to read data from Access Files.
I use the following workaround:
I open the Access-Database with the 32 bit Version of Stata, save it to a temporary .dta-file and the use this temp.dta-file in 64bit stata.
I copied the StataSE-32.exe into the same folder , where the 64-bit executable of stata can be found.
i use the following adofile odbc32.ado instead of the odbc.ado command

version 12
capture program drop odbc32

program define odbc32
   // syntax [anything] [if] [in] [using] [, dialog(passthru)
dsn(passthru) table(passthru) exec(passthru) load_options(passthru)
connect_options(passthru) clear]
   if `c(bit)'==32 {
   //  odbc `anything' `if' `in' `using', `dialog' `dsn' `table' `exec' 
`load_options' `connect_options' `clear'
     odbc `0'
   }
   else {
       local commandstr = substr(trim("`0'"),1,4)
       di "`commandstr'"
       local currentdir = "`c(pwd)'"
       // di "`currentdir'"
       local tempdofile = "`c(tmpdir)'z.do"
       qui di "`tempdofile'"
       capture file close tempdo
       file open tempdo using `tempdofile' , write replace
//      file write tempdo `"odbc `anything' `if' `in' `using', `dialog' 
`dsn' `table' `exec' `load_options' `connect_options' `clear'"' _n
       file write tempdo `"odbc `0'"' _n
       if "`commandstr'" == "load" {
           file write tempdo `"odbc `0'"' _n
           file write tempdo `"save `c(tmpdir)'z.dta, replace"' _n
       }
       else {
           save `c(tmpdir)'z.dta, replace
           // di `"`0'"'
           local newcommand = subinstr(`"`0'"',"create","overwrite",1)
           // di `"`newcommand'"'
           file write tempdo `"use `c(tmpdir)'z.dta, clear"' _n
           file write tempdo `"capture odbc `0'"' _n
           file write tempdo `"if "'
           file write tempdo "`"
//          file close tempdo
           file write tempdo `"r(sqlerror)"'
           file write tempdo "'"
           file write tempdo `" == -1 {"' _n
           file write tempdo `"    odbc `newcommand'"' _n
           file write tempdo `"}"' _n
       }
       file close tempdo
       qui cd "`c(tmpdir)'"
       shell "`c(sysdir_stata)'StataSE-32.exe" -e do `tempdofile'
       qui cd "`currentdir'"
       if "`commandstr'" == "load" {
           use "`c(tmpdir)'z.dta", clear
       }
     }
end

exit

Hope that helps,

Max

Am 20/09/2012 12:19, schrieb Richard Fox:
> Dear Statalist,
>
> I'm having an issue with an odbc link to access database. I know that the link is working fine as SAS is able to import the tables.
>
> I get the following error when trying to import tables or just running an 'odbc list'. The odbc link is listed when in connections but will error if clicking on particular database connection.
>
> The ODBC driver reported the following diagnostics [Microsoft][ODBC 
> Driver Manager] The specified DSN contains an architecture mismatch 
> between the Driver and Application
> SQLSTATE=IM014
>
> I believe this is a 32bit Access vs 64bit Stata issue. Any advice would be appreciated.
>
> Apologies if this issue is documented. I have searched through my 14000 Statalist emails and could not find any relevant info.
>
> I'll take this opportunity to recommend, and to thank StataCorp et al for, the UK Stata Users Group meeting - a very valuable learning experience in terms of both Statistics and Stata programming.
>
>
> Best Regards
>
> Richard Fox
> Biostatistician - CRCTU
>
>
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/