By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Oracle database password

P: 19
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.
May 11 '07 #1
Share this Question
Share on Google+
10 Replies


JConsulting
Expert 100+
P: 603
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
May 12 '07 #2

P: 1
Did you ever figure out the syntax to put into Access to get past the password window for Oracle?
May 14 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
Did you check out the link in Post #2?
May 17 '07 #4

P: 19
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.
May 21 '07 #5

P: 19
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.
May 21 '07 #6

NeoPa
Expert Mod 15k+
P: 31,347
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.
May 21 '07 #7

P: 19
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Dim ccn2 As ADODB.Connection
  2. Set ccn2 = New ADODB.Connection
  3. ccn2.Open PCMS_ORGLEADER_VW
I hope this helps explain my problem.
May 24 '07 #8

NeoPa
Expert Mod 15k+
P: 31,347
You include :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. pwd=[password];
See where that gets you for now.
May 24 '07 #9

P: 19
You include :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. pwd=[password];
See where that gets you for now.
Thanks for the tip. Here is my new code:
Expand|Select|Wrap|Line Numbers
  1. ' This will close the current form and open the form listed
  2.  
  3.     Dim stDocName1 As String
  4.     Dim stDocName2 As String
  5.     Dim stDocName3 As String
  6.     Dim stLinkCriteria As String
  7.     stDocName1 = "PCMS_COLWH_PTC"
  8.     stDocName2 = "pcmsdata"
  9.     stDocName3 = "frmInfoMsnVol"
  10.  
  11.     DoCmd.Close
  12.  
  13.     Const COLPR1 = "DSN=PCMS_ORGLEADER_VW;UID=PCMS_COLWH_PTC;pwd=pcmsdata;DBQ=COLPR1;ASY=OFF;"
  14.     Dim ccn2 As ADODB.Connection
  15.     Set ccn2 = New ADODB.Connection
  16.     ccn2.Open COLPR1, stDocName1, stDocName2
  17.  
  18.     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?
May 24 '07 #10

NeoPa
Expert Mod 15k+
P: 31,347
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.
Expand|Select|Wrap|Line Numbers
  1. ?CurrentDb.TableDefs("MyTable").Connect
Good luck.
May 24 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.