MS ACCESS connecting using ODBC

[Ucaihc]

Hi,
I have an Access front-end that connects to a backend that includes linked Oracle tables. The problem is that whenever I click on a function that uses one of the Oracle tables, I am promted for the Oracle login. It seems as if there is a way to store this userid and password information, since none of my other Access databases (which also contain linked Oracle tables in the backend) produce this prompt.
I thought I could store the password in the ODBC driver, but the Microsoft Oracle driver does not let me store the password. Is there some place where I can store it?
Thanks.

 

[PHV]

When you link a table you may tick a remember password checkbox.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[genomon]

Also check the default on your DSN for the ODBC connection.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[gwinn7]

There are two ways of storing this password (1) through a DSN or (2) dynamically building the ODBC connection string at run-time which you would then hard-code the password, or retrieve it from an internal table, if you want.

Gary
gwinn7

 

[Ucaihc]

Thanks, but the Mircosoft ODBC for Oracle doesn't let me store the password. And, I didn't see the remember password box. None of my other databases user code for this connection, so I think there has to be a simpler way.

 

[JoeAtWork]

How about DSN-less connection string? You can change the connection used by your linked tables by updating the corresponding TableDef.Connect property.

 

[Ucaihc]

Hi,

Thanks, but I did not find the "corresponding TableDef.Connect property".

 

[SkipVought]

Try looking for an Oracle in OraHomenn driver in your Data Sources (ODBC) to use INSTEAD of the Micorosoft Oracle driver.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[genomon]

All good solutions. We don't do Oracle here anymore, but here are some DSN-less connect strings if you need them:

For Standard Security
Conn.Open "Provider=OraOLEDB.Oracle;" & _ "Data Source=MyOracleDB;" & _ "User Id=myUsername;" & _ "Password=myPassword"

For a Trusted Connection
Conn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _ 
           "User Id=/;" & _
           "Password="

 'Or

Conn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _ 
           "OSAuthent=1" 

Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[Ucaihc]

Thanks. When I try the first, I get an error message "Provider not found -- may not be properly installed..."

Can you help? I am using a Microsoft ODBC for Oracle driver.

 

[maxhugen]

Some years ago, I had to connect to an Oracle db, and had some fun and games. Eventually, as Skip hinted at above, I obtained and installed an Oracle ODBC driver from Oracle, which then worked OK.

Max Hugen
Australia

 

[Ucaihc]

Ok, I got the provider right, but am still being prompted for the userid and password. Can you tell me why?

 

[cgSteve]

I have this same problem.

Configuration:
MS Access 2003
Windows XP

I am in a conversion of the application to move it from linking from Sybase to start linking 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.

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

 

[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.