Random ODBC connection failure

[RobertKKoller]

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.

[MMcCarthy]

You can write the code to relink the tables on startup.

Dim tbl As DAO.TableDef
 
    ' relink the tables
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name <> "MSys*" And tbl.Name <> "~*" Then
            If Len(tbl.Connect) > 0 Then
                tbl.Connect = "odbc connection string here"
                tbl.RefreshLink
            End If
        End If
    Next
 

[wvmitchell]

Robert,

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?