alternative to manually refreshing linked tables?

[WPW07]

Hello,

Not sure where to begin with this. I developed a simple front end
application which is linked to a dozen oracle tables. Everything
worked fine until today. The user received this message: "Microsoft
Office Access" ODBC - Connector to "xxx" failed. I remembered the
password for Oracle expires every 90 days. So I went in and refreshed
the links with the new password.

Is there a way through autoexec and/or vba code to handle this in the
future?

Thank you.

 

[Stefan Hoffmann]

hi,

Is there a way through autoexec and/or vba code to handle this in the
future?

Let the user enter the password. Or ask the DBA for an account with a
not expiring password.

Otherwise us something like this:

--
Public Sub Reconnect()

Const MATCH As String = "OraHome" ' Depends on the use driver.
Dim tdf As DAO.TableDef

For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.Name; tdf.Connect
If InStr(tdf.Connect, MATCH) > 0 Then
tdf.Connect = SetPassword(tdf.Connect, NewPassword)
End If
Next tdf

End Sub

Private Function SetPassword(AConnect As String, _
APassword As Srting _
) As String
' replace the password...
End Function