Excel 2013 64bit 에서 VBA 오라클 데이터베이스 접속 법 RRS feed

  • 질문

  • 안녕하세요. OS는 64비트 운영체제입니다.

    기존에는 엑셀 2010 32비트 버전을 사용하였습니다.

    이번에 2013 64비트로 업그레이드 하였더니 엑셀 매크로를 사용하는데 데이터베이스 접속이 되지 않습니다.

    데이터베이스는 ORACLE 11g입니다.

    기존에는 PROVIDER = MSDAORA.1 을 사용했는데 접속이 되지 않네요.

    Microsoft.ACE.OLEDB.15.0 도 사용해보고 여러가지 시도해보았지만 접속이 되지 않고

    공급자를 찾을수 없다는 에러가 뜨거나 이 응용프로그램을 시작할수 없다는 에러가 뜹니다.

    답변 부탁드립니다.

    • 편집됨 탱탱4 2015년 10월 15일 목요일 오전 2:10
    2015년 10월 15일 목요일 오전 2:09


  • 동일한 문의가 stackoverflow에 문의되어 있고 답변되어 있습니다.  x64 Oracle client를 이용하여야 합니다. 다음 두 웹 링크 답변들 참조하십시오.
    Connecting to 32 bit Oracle client with 64 bit Excel
    BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

    One solution is to install both x86 (32-bit) and x64 Oracle Clients on your machine, then it does not matter on which architecture your application is running.

    Here an instruction to install x86 and x64 Oracle client on one machine:

    Assumptions: Oracle Home is called OraClient11g_home1, Client Version is 11gR2

    Optionally remove any installed Oracle client

    Download and install Oracle x86 Client, for example into C:\Oracle\11.2\Client_x86

    Download and install Oracle x64 Client into different folder, for example to C:\Oracle\11.2\Client_x64

    Open command line tool, go to folder %WINDIR%\System32, typically C:\Windows\System32 and create a symbolic link ora112 to folder C:\Oracle\11.2\Client_x64 (see below)

    Change to folder %WINDIR%\SysWOW64, typically C:\Windows\SysWOW64 and create a symbolic link ora112 to folder C:\Oracle\11.2\Client_x86, (see below)

    Modify the PATH environment variable, replace all entries like C:\Oracle\11.2\Client_x86 and C:\Oracle\11.2\Client_x64 by C:\Windows\System32\ora112, respective their \bin subfolder. Note: C:\Windows\SysWOW64\ora112 is not needed in PATH environment.

    If needed set yor ORACLE_HOME environment variable to C:\Windows\System32\ora112

    Open your Registry Editor. Set Registry value HKLM\Software\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME to C:\Windows\System32\ora112

    Set Registry value HKLM\Software\Wow6432Node\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME to C:\Windows\System32\ora112 (not C:\Windows\SysWOW64\ora112)

    You are done! Now you can use x86 and x64 Oracle client seamless together, i.e. an x86 application will load the x86 libraries, an x64 application loads the x64 libraries without any further modification on your system.

    Probably it is a wise option to set your TNS_ADMIN environment variable (resp. TNS_ADMIN entries in Registry) to a common location, for example TNS_ADMIN=C:\Oracle\Common\network.

    Commands to create symbolic links:

    cd C:\Windows\System32
    mklink /d ora112 C:\Oracle\11.2\Client_x64
    cd C:\Windows\SysWOW64
    mklink /d ora112 C:\Oracle\11.2\Client_x86


    Both symbolic links must have the same name, e.g. ora112.

    Despite of their names folder C:\Windows\System32 contains the x64 libraries, whereas C:\Windows\SysWOW64 contains the x86 (32-bit) libraries. Don't be confused.

    2015년 10월 15일 목요일 오전 4:25