On Windows 7 (64-bit): How to use MS Access 2007 to link to Oracle 10g tables and NOT get ora-06413 error dialog
The problem scenario: from Windows 7 (64-bit), create a link to an Oracle 10g table. Following steps 1 & 4, the dreaded "ORA-06413" dialog appears, "Test connection failed because of an error in initializing provider. ORA-06413: Connection not open" The 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. Here's what worked for me: Create a ODBC data source using the Windows Control Panel applet, "Data Source (ODBC)", which happens to be found in %windir%\syswow64\odbcad32.exe Select the System DSN tab Click the Add button Scroll to and select "Microsoft ODBC for Oracle", and hit OK Fill in the Data Source Name, I chose "DanaOracle", and hit OK, and hit OK again Here's the real trick to making this all work: 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) 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" 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 "DanaOracleDb.accdb" Select External Data -> More -> ODBC Database 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 "DanaOracle"), and hit OK Another dialog will appear, Microsoft ODBC for Oracle Connect Enter for the User Name: SYSTEM Enter for the Password: <whatever password you chose for SYS and SYSTEM when you installed Oracle 10G> Leave blank Server: Another "Link Tables" dialog will appear, choose whatever is appropriate There are probably other workarounds, like installing MS Access 2007 in a folder not containing parentheses in the path, but the above procedure worked for me -- good luck! Reference this website for more info: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f/
December 30th, 2010 6:53pm

I don't care if this is thread necro or not - after running around trying a brazillion combinations of ora clients and all sorts of freakish stuff, this worked for me. Thanks! (I'm win7 64b, office 2010 32b, ora client 10g, connecting to some gawdaful old ora server)
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2011 10:59pm

Thank you! Thank you! Thank you! I've asked to a lot of DBAs what could be the problem and nothing. Thank you... problem solved!
April 4th, 2011 4:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics