Solved

ODBC Failure - ORACLE, MS ACCESS, Windows 7

Posted on 2013-01-18
7
2,078 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

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.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

624 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