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

[Max Bo <boh_max@web.de>]

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/