• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2165
  • Last Modified:

ODBC Failure - ORACLE, MS ACCESS, Windows 7

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
santaspores1
Asked:
santaspores1
  • 5
  • 2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
santaspores1Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
if you are getting errors from Windows 7 machine, you need to get the Oracle ODBC driver for windows 7
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
santaspores1Author Commented:
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
 
santaspores1Author Commented:
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
 
santaspores1Author Commented:
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
 
santaspores1Author Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now