Oracle database password

[Reedsp]

Version: MSAccess 2003 SP2

Explination: I have a database that connects to an oracle database. I have setup an ODBC connection under the System DSN tab in the ODBC Source Administrator. The first time I go to a form that has fields from the oracle table, the "Oracle ODBC Driver Connect" window appears, as it should. Once I type in the password and continue with the database, that window never appears again. If I enter the password in the registry the "Oracle ODBC Driver Connect" window never appears.

Properties of System DSN setup:
Data Source Name: PCMS_ORGLEADER_VW
TNS Service Name: COLPR1
User ID: PCMS_COLWH_PTC

Driver: Oracle in OraHome92 (Oracle 9.2)

Question: How can you pass to the oracle database the password without manually typing it in the "Oracle ODBC Driver Connect" window or putting the password in the registry? What code do you use? I don't want the user to remember a password and I don't want to modify the registry on all the computers that use this database.

[JConsulting]

Version: MSAccess 2003 SP2

Explination: I have a database that connects to an oracle database. I have setup an ODBC connection under the System DSN tab in the ODBC Source Administrator. The first time I go to a form that has fields from the oracle table, the "Oracle ODBC Driver Connect" window appears, as it should. Once I type in the password and continue with the database, that window never appears again. If I enter the password in the registry the "Oracle ODBC Driver Connect" window never appears.

Properties of System DSN setup:
Data Source Name: PCMS_ORGLEADER_VW
TNS Service Name: COLPR1
User ID: PCMS_COLWH_PTC

Driver: Oracle in OraHome92 (Oracle 9.2)

Question: How can you pass to the oracle database the password without manually typing it in the "Oracle ODBC Driver Connect" window or putting the password in the registry? What code do you use? I don't want the user to remember a password and I don't want to modify the registry on all the computers that use this database.

You might check here for an excellent source for most all references.
http://www.connectionstrings.com/?carrier=oracle
J

[emoen]

Did you ever figure out the syntax to put into Access to get past the password window for Oracle?

[NeoPa]

Did you check out the link in Post #2?

[Reedsp]

Did you check out the link in Post #2?

I did check out the link. I don't know where to put the code. Do I use it as a CONST (Constant) at the top of the Form that views the data. I have 2 areas I connect to Oracle. One is through a look up combo box, the other is viewing a form that shows data from the oracle table. If you know where or how I might use this code let me know. If you need a better description let me know also.

Thanks.

[Reedsp]

Did you ever figure out the syntax to put into Access to get past the password window for Oracle?

I did figure out the syntax. Its' where to put the syntax that is the 2nd part of the problem. If you have and Idea where to put it that would be a big help.

Thanks.

[NeoPa]

I did check out the link. I don't know where to put the code. Do I use it as a CONST (Constant) at the top of the Form that views the data. I have 2 areas I connect to Oracle. One is through a look up combo box, the other is viewing a form that shows data from the oracle table. If you know where or how I might use this code let me know. If you need a better description let me know also.

Thanks.

I'm afraid I can't answer your question as I'm unfamiliar with Oracle and I don't have the code you're talking about in front of me. If JC doesn't come back with some helpful tips and you want to post what you've got so far, I can try to make an educated guess with/for you. I know Access reasonably well.
I've also used ODBC connections in various ways, though rarely set up in the code.

[Reedsp]

I'm afraid I can't answer your question as I'm unfamiliar with Oracle and I don't have the code you're talking about in front of me. If JC doesn't come back with some helpful tips and you want to post what you've got so far, I can try to make an educated guess with/for you. I know Access reasonably well.
I've also used ODBC connections in various ways, though rarely set up in the code.

Thanks for your willingness to help. I will try and explain the issue as best as possible.

From my original message, I included the DSN variables I have set up. I have 2 areas the use or access oracle data. The first has information that I look up through a combo box (based on a query to the oracle table) information that in then displayed on the window. As you go to each record you see this information. So when I first open the Form that I will call Address Info, because information that is displayed is related to the oracle table the Oracle sign on window pops up asking for the password. If you type the incorrect password the information from the oracle table is not displayed on the form. All the other fields can be seen. If the password is correct then the oracle information is displayed and I am not asked for the password for any other time I access the oracle information.

Here is the code I am using.

Const PCMS_ORGLEADER_VW = "Provider=MSDASQL;USER ID=PCMS_COLWH_PTC;pwd=pcmsdata;Extended Properties=DSN=PCMS_ORGLEADER_VW;UID=PCMS_COLWH_PTC;pwd=[password];DBQ=COLPR1;ASY=OFF;"

I am using this as a constant. Is their another way. Do I need to open a recordset? I have tried the following code hoping to open the ODBC connection and pass through the password but it hasn't worked.

Dim ccn2 As ADODB.Connection
Set ccn2 = New ADODB.Connection
ccn2.Open PCMS_ORGLEADER_VW

I hope this helps explain my problem.

[NeoPa]

You include :

Extended Properties=DSN=PCMS_ORGLEADER_VW;

I'm not sure but I think you don't want embedded spaces and you don't want A=B=C.
You also repeat pwd=. The second is just wrong I think.

pwd=[password];

See where that gets you for now.

[Reedsp]

You include :

Extended Properties=DSN=PCMS_ORGLEADER_VW;

I'm not sure but I think you don't want embedded spaces and you don't want A=B=C.
You also repeat pwd=. The second is just wrong I think.

pwd=[password];

See where that gets you for now.

Thanks for the tip. Here is my new code:

' This will close the current form and open the form listed
 
    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim stDocName3 As String
    Dim stLinkCriteria As String
    stDocName1 = "PCMS_COLWH_PTC"
    stDocName2 = "pcmsdata"
    stDocName3 = "frmInfoMsnVol"
 
    DoCmd.Close
 
    Const COLPR1 = "DSN=PCMS_ORGLEADER_VW;UID=PCMS_COLWH_PTC;pwd=pcmsdata;DBQ=COLPR1;ASY=OFF;"
    Dim ccn2 As ADODB.Connection
    Set ccn2 = New ADODB.Connection
    ccn2.Open COLPR1, stDocName1, stDocName2
 
    DoCmd.OpenForm stDocName3, , , stLinkCriteria

It still opens the Oracle window wanting the password. I know I can modify the password settings in the registry and set the password and I never get the Oracle window but I am trying to avoid that step. Any more ideas?

[NeoPa]

My first, and most important idea, is to remember to use code tags in your posts. That leaves me more time to answer them ;)

Otherwise I'm afraid I'm not a great deal of help here in this area as some of the settings are ORACLE specific and not related to ODBC generally.
What I do to get the basic outline of a connect string though, is to create a linked table in the database itself, then print off the connection string of that table. I then examine this, and determine what is required and what format looks like it may work. It usually involves a bit of trial and error, but it's worked for me in the past.
Enter the following code (modified to use your table name), in the Immediate pane of the debugger.

?CurrentDb.TableDefs("MyTable").Connect

Good luck.