Solved

ODBC Failure - ORACLE, MS ACCESS, Windows 7

Posted on 2013-01-18
7
1,997 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now