Solved

ODBC Failure - ORACLE, MS ACCESS, Windows 7

Posted on 2013-01-18
7
2,060 Views
Last Modified: 2013-01-29
Hi,

I am moving from a 32-bit XP Pro box to a 32-bit Windows 7 box and I am having trouble making ODBC connections to Oracle from within MS Access on the new Windows 7 box.

On my current system I have:
      32-bit XP Pro
      Access 2010
      The Oracle 10g client installed

My new system has:
32-bit XP Pro
      Access 2010
      The Oracle 10g client installed (configured in the exact same way)

On my new system, when I go to ODBC, select my system DNS and click test… it prompts me for my password and after entering it…IT WORKS.

When I go into an Access database, go to link table manager, select my (tested working) DNS connection… it does not prompt me for a password and I get “ODBC—Call Failed.”

Any ideas?  I did check that my oracle client BIN directory was in my system path.
0
Comment
Question by:santaspores1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 38793644
create this query

select  connect,  name, type from  msysobjects
where type=4


run it in both the old and new system and compare the connect values
0
 

Author Comment

by:santaspores1
ID: 38793807
THANKS!  They seem to be exactly the same though... and they look like the configuration parameters of the ODBC DNS entry.  The ODBC specifications are the same on each machine... and from within that interface connections do work.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 38794133
if you are getting errors from Windows 7 machine, you need to get the Oracle ODBC driver for windows 7
0
Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

 

Author Comment

by:santaspores1
ID: 38794409
Capricorn - thanks!  
I also have 11g installed.  My "oracle in OraHome" driver files are:
10g - 10.02.00.01
11g - 11.02.00.01

Oracle's web site shows a single download for all 11g clients for 32-bit windows and the most current version is 11.2.0.1 ... so it looks like I have the latest and greatest installed right now.  And, just like my 10g driver, it works fine in the ODBC connection test but not when used in MS Access.
0
 

Author Comment

by:santaspores1
ID: 38806462
Here is some more information just in case anyone can help.

I can go to a folder that has been added as a trusted location (including all subfolders) and create a new database there.  Then I can go to the External Data tab and select ODBC database.  I can then select either of my System DNS ODBC specifications (one 10g the other 11g).  MS Access will then show me a popup window that reads ODBC--Call Failed.  And it never gets to the point of prompting me for a password.
0
 

Accepted Solution

by:
santaspores1 earned 0 total points
ID: 38815389
Here seems to be the resolution:

If I reboot the system and never make any use of the 10g driver for anything prior to using the 11g driver, all is well.  If I do anything with the 10g driver, neither (even the 11g) will work in Access until I reboot and only use the 11g driver.  So I need to reboot the machine, and for every ms access database that I have, I need to use linked table manager to update the connection to the 11g driver... without touching my 10g driver at all.  And then all will be skippy.  

Just in case this proves helpful to someone else one day.
0
 

Author Closing Comment

by:santaspores1
ID: 38830320
I awarded the points elsewhere but their answers were not associated with my problem.  And my answer might be something that will help someone else one day.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum editing capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question