Solved

ODBC Failure - ORACLE, MS ACCESS, Windows 7

Posted on 2013-01-18
7
2,013 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.

773 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