By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,240 Members | 791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,240 IT Pros & Developers. It's quick & easy.

Random ODBC connection failure

P: 1
I have an Access frontend that connects to an Oracle9i database backend that is set up with a DSN using the Oracle 8 ODBC Driver. In general, everything works perfectly, but randomly (I say random since I can't find any cause) when I open the Access application and open a form or table that connects to Oracle, I get the error message "ODBC - Connection to 'DSN Name' failed". Then to fix this, all I have to do is use the Linked Table Manager to reselect the DSN and then everything works fine until the error happens again. In case it matters, this Access application exists on a share folder and is used by three people with separate Oracle accounts. While resetting the DSN with the Linked Table Manager is quite easy, I don't want to have to tell my users that they might need to do this every once in a while.
Nov 19 '07 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 14,534
You can write the code to relink the tables on startup.

Expand|Select|Wrap|Line Numbers
  1. Dim tbl As DAO.TableDef
  3.     ' relink the tables
  4.     For Each tbl In CurrentDb.TableDefs
  5.         If tbl.Name <> "MSys*" And tbl.Name <> "~*" Then
  6.             If Len(tbl.Connect) > 0 Then
  7.                 tbl.Connect = "odbc connection string here"
  8.                 tbl.RefreshLink
  9.             End If
  10.         End If
  11.     Next
Nov 27 '07 #2

P: 42

Is it possible that the Oracle table is being changed? When you create an ODBC connection in Access, it remembers the table structure but does not automatically update if the underlying table changes. So for example, if you add a column to the table, you need to relink, otherwise you get error 3146 ODBC call failed.

Another thought, I'm not an Oracle person, but is it possible that the Oracle 8 driver is not 100% compatible with the Oracle 9 database?
Nov 27 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.