[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

How do I get rid of "ODBC --call failed" error in MS Access 2007 Linked Table Manager?

Hello,

I'm running Access 2007, linking to Oracle datasources (Oracle 10g).  This error happens in the Llinked Table Manager.  Usually when I select a linked table, and also select "Always prompt for new location".... then I get "ODBC -- call failed."  I'm on Office 2007 SP2.  This seems to have been a problem with Access 2007 from the get-go, though I've been using it for a while now.  I'm using an ACCDB database.  Any ideas on how to fix this?  Thanks,

Glenn
0
Glenn Ford
Asked:
Glenn Ford
  • 7
  • 4
  • 3
2 Solutions
 
ppangoCommented:
Are you using a file or system DSN?
0
 
ppangoCommented:
And are you using XP, Vista or another OS?
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
Thanks ppango.  I'm using a system DSN, and XP Service Pack 3.

Since posting this question, I have noticed a pattern.  The linked table manager refresh seems to work fine after a re-boot..... that is, it works for a few refreshes, and then the problem rears it's head (and does not go away until I do another re-boot).

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
ppangoCommented:
Are you using the Oracle ODBC driver from the Oracle client install or the
Microsoft ODBC driver for Oracle?

In case you're using the one provided by microsoft, i would recommend you try this one: http://www.easysoft.com/products/data_access/odbc_oracle_driver/download.html

Ppango
0
 
ppangoCommented:
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
I'm using the Oracle 10g ODBC driver from the client install.  I have tried the Microsoft driver for Oracle (in the past), but this driver seems to lack the robustness of the Oracle drivers.  I'm ruling that out for production use.

Thanks for the reference to the EasySoft products, also.  I don't think that our organization would be up to spending that kind of money on a new driver, however,

Any thoughts on whether this is an Oracle issue, or an Access issue?  Sounds like you are saying that it is an Oracle issue.

Thanks.

0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
Thanks for these posts.  We're a vanilla outfit when it comes to ODBC drivers, so I will not be trying to install any 3rd party products.  I really just want to get the current software to work properly.  Perhaps that is not possible.  Thanks for all the help, though.
0
 
Jim P.Commented:
With WinXP and below, there really wasn't an ODBC driver for Oracle. It was dependent on loading the Oracle driver, and then was a front-end on it.

My best suggestion is to have a reg read setup that checks that the Oracle client is loaded, then from there use an ODBC load to add it.
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
Hello jimpen,

Thanks for your response.  You'll have to explain your second paragraph to me.  I think you're talking over my head.... if you could.

Thanks
0
 
Jim P.Commented:
You can use Access to read the registry to see if the Oracle Drivers are loaded. It would probaly be under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI that the it would show your Oracle Drivers.

How to read the registry: http://accesstips.wordpress.com/2009/07/16/microsoft-access-vba-uses-wmi-to-read-the-registry/

Note that the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle key is just a front end for the actual Oracle drivers.

Then if the Oracle driver is loaded, there are several ways to create an ODBC call -- but the basic idea is in this article: http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Creating-DSNs-for-SQL-Server-In-depth-on-how-to-make-one.html
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
"My best suggestion is to have a reg read setup that checks that the Oracle client is loaded, then from there use an ODBC load to add it."

Thanks for the elaboration on this topic.  Now, if I use the ODBC load, how does this process substitute for using Linked Table Manager to change all data sources from PROD to TEST, for example?
0
 
Jim P.Commented:
You can build a table and then use that with a stored to relink the tables on the click of the button.

It is not a massive problem to do. I may have the code lying around to do it for SQL. You would have to mod it to change it to Oracle.
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
Thanks jimpen,

I'm really looking to see whether there is a way to get the Linked Table Manager to work as it should.  Your information is quite valuable, but vba code is not the direction I want to go.  I just want to get the MS Access Linked Table manager to work as advertised.  Is there an anwer?
0
 
Glenn FordSr Applications ProgrammerAuthor Commented:
Thanks to you both.  I appreciate your input.  I will not be implementing either solution, but both solutions are very informative, and certainly worth the points.  For now (until Microsoft fixes it), I'll just limp along with the Linked Table Manager, because it does seem to work a little bit after a re-boot... and, I don't have to re-link that often.
Best regards....
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now