# Miscellaneous > Ask an Expert >  MS Access:  Oracle ODBC driver does not store password

## cgSteve

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?

Thank you in advance for any input.

----------


## Island1

Lots of discussion with several options proposed.  I see you posted here and elsewhere as well.  Did you try contacting any of the other posters, etc?  Did you see the point re:  Oracle driver for the need?

http://www.tek-tips.com/viewthread.c...1451935&page=1

Let us know if any of these work, so that others might benefit from your experience.

Good Luck!

Bill

----------


## cgSteve

Thank you for the response.  I did post this question on three different sites.  But, the other options on the site you mention do not solve the problem.

I have tried the same options, but it is only when the ODBC driver is switched to Oracle that the password prompt appears.

The other options do not work as follows:

1. DSN-Less connections are not a good idea because this means the .mdb file needs to be modified every time it is deployed to a new environment.  It is best to keep connection information stored in a separate file (as I am using a FILE DSN).

2.  The "Save Password" checkbox should not be selected when linking a table because that means anyone that gains access to the .mdb file can then have access to Oracle.  This does not meet my requirements.

3.  Dynamically building the ODBC connection string is what I do in the application.  However, that doesn't help any.  If simply opening the table in data view doesn't work, then nothing else would work (is what I am assuming).  So, that is why I asked here for a way to solve the problem just from the perspective of opening the table in data view.

4.  I have tried the following drivers for Oracle:  Microsoft's driver, Oracle's 9.2 driver, and Oracles 10.2.0.3 driver.  Each driver had the same problem.

Overall, the problem on the other site was never solved.  Since I have the same problem I was hoping someone else on other sites may have an answer.

One extra point I can add is that I have tried to link to both public synonyms and actual tables in Oracle.  Each again had the same problem.  But, I'm wondering if the way Oracle manages the tables causes the problems.

Does anyone have anything else they can add or have me try?

----------


## cgSteve

Here is an update to my problem I posted above.

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):

   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

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

----------

