MS Access: Oracle ODBC driver does not store password or refreshlink not working

I'm helping a friend trying to debug this issue as he's posted all over the web, so I've come to the experts for some help... here's the question below:

I have a problem converting linked tables in MS Access from Sybase to Oracle. The problem
is that with the Oracle driver the password prompt appears every single time the table
is opened (undesirable). When Sybase was used, the password prompt only was shown the first
time a table was opened (which was good).

Configuration:
- MS Access 2003
- Windows XP
- Linked tables are created without checking the "Save Password" box in each instance
- Oracle ODBC driver 10.2.0.3
- Using a File DSN

Details:

I am in a conversion of the application to move it from linking from Sybase
to instead link from Oracle.

Starting point:
I have a .mdb file that has linked tables. The tables originally were
linked to a Sybase database using Sybase's ODBC driver. When I open a table
in data view, it prompts me for the username and password. After supplying
the information, I am shown the data. Any subsequent openings of the tables
to that same Sybase database are not prompted for the username and password.
This is how I want it.

New point:
When I now have removed the tables and linked them again to an Oracle
database using Oracle's ODBC driver, the password prompt comes up EVERY time
the table is opened--even if I have previously supplied it. Furthermore, I
am prompted for the password two times (the prompt window comes up once, I
add the password, and then it appears again and I add the password
again--then the data view is displayed). This is NOT how I want it.

I cannot solve this by checking the "Save Password" box while initially linking the tables (won't work with my requirements).

What is going on here? Why is Oracle's 10.2.0.3 ODBC driver not storing the
password like the Sybase driver did?

followup:
By creating a new Access database file I determined that I could successfully link a table with the Oracle driver. Therefore, I then stepped through the custom code of the application to see if anything in there was causing the problem. I have narrowed the problem down to the following:

1. Manually re-creating the linked tables works correctly.
2. It is only when custom code refreshes the links the password prompt problem occurs.

Details:
With the Oracle ODBC drivers (Microsoft's, Oracle 9, and Oracle 10), the RefreshLink method of the TableDef object in VBA causes something to go wrong with the linked tables. Problems such as multiple password prompts or Access crashing have occurred.

Here is the chunk of code that runs in the Access application. Once the RefreshLink line of code (the last line) executes, the table is no longer usable (because multiple password prompts need to be handled for the table from that point on):



If anyone has any idea why the RefreshLink method would cause this behavior, please post.


Thank you in advance for any input.Start Free Trial

1:
2:
3:
4:
5:
6:
7:

Dim db As DAO.database
Dim tdf As DAO.TableDef
 
Set db = CurrentDb()
Set tdf = db.TableDefs("TableName")
tdf.Connect = "ODBC;FILEDSN=" & strFileDsnName & ";UID=" & strUserName & ";PWD=" & strPassword
tdf.RefreshLink