Access 2007 (32-Bit) Link to Oracle Database Using Microsoft ODBC for Oracle Driver on Windows 7 (64-Bit)

When we want to create link to table oracle using access 2007 x86 on Win 7 x64. We must setting several properties:

1. Setting Data Source (ODBC) to Target in sysWOW64:

  • Open Control Panel > Administrative Tools > Right Click Data Source (ODBC) > Properties > Change the Target and Start In to %windir%\syswow64\odbcad32.exe like following pictures:

Image

Then another problem is that MS Access 2007 on Windows 7 (64-bit version) is by default installed in the following folder:  C:\Program Files (x86)\Microsoft Office\Office12, and the Microsoft ODBC for Oracle driver doesn’t work properly with the parentheses in the path.  The solution is create a symlink to the folder Access is installed in, and then launching Access from that location.

2. Set up a symlink to the c:\Program Files (x86) folder without the parentheses:

  • Select the Windows button:  Start -> All Programs -> Accessories
  • Scroll down to Command Prompt, and right-click and select “Run as administrator”
  • At the command prompt, enter the following:  mklink /D “C:\Program Files x86” “C:\Program Files (x86)”
  • You should get back:  symbolic link created for C:\Program Files x86 <<===>> C:\Program Files (x86)

Image

3. For convenience, create a desktop shortcut to the new no-parentheses path to Access

  • Launch Windows Explorer (quick launch is Windows key, and press E), and navigate to C:\Program Files x86\Microsoft Office\Office12
  • Scroll to MSACCESS, right-click, and select “Create Shortcut”
  • A dialog will appear, saying it “…cannot create a shortcut here, and ask if you would like one placed on the desktop instead?”  Click Yes.
  • Go to the desktop (quick launch is Windows key, and press D), and rename the shortcut so you can better remember why it’s there
  • Select the shortcut
  • Right-click, and select rename
  • Rename to “MSAccess for Oracle”

4. Finally, create a MS Access database with a link to an Oracle table

  • Double-click the “MSAccess for Oracle” desktop shortcut
  • Select new blank database, and give it a name, I chose “OracleDb.accdb”
  • Select External Data -> More -> ODBC Database

Image

  • Select “Link to the data source by creating a linked table.”
  • In the “Select Data Source” dialog, select the “Machine Data Source” tab, and select the data source you just created (mine was “Oracle”), and hit OK
  • Another dialog will appear, Microsoft ODBC for Oracle Connect
  • Enter for the User Name:  <Username>
  • Enter for the Password:  <whatever password you chose for SYS and SYSTEM when you installed Oracle 10G>
  • Leave blank Server: <server name>
  • Another “Link Tables” dialog will appear, choose whatever is appropriate

Image

That’s finish the tutorial, Good Luck

Reference : http://social.msdn.microsoft.com/Forums/office/en-US/fee3f1db-7d4e-4e36-918d-5f7d3e6821c7/on-windows-7-64bit-how-to-use-ms-access-2007-to-link-to-oracle-10g-tables-and-not-get-ora06413?forum=accessdev

About evocruzader

I am a honest, energetic, cool, n fun... gather near me and u will live with full of joy n fun. ^^
This entry was posted in DBA, Information, IT and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s