The DBA of our backend database is required to create users with a password that is expired. This means that the Application users logging in from MS-Access will need to change their Oracle password before being allowed to use the application. Most of my user base does not know SQL, or the use of SQLPlus to change their passwords.
I've been able to detect within Access when the expired password error message is generated and created a dialog box for them to provide a new password as input to the ALTER USER <username> IDENTIFIED BY <newpassword> command string. I still need to be able to connect to the database and it seems that ODBC won't let me. I don't have too many ideas on how to connect and change the password. Has anyone tried this before? If so, what might I try?
Following is a piece of code that should do it. I'm not at a place where I can connect to an Oracle database right now (i.e. I'm home), but I'm pretty sure it works. You need to change the connect-string (strConnect) to your situation.
Dim wspODBC As Workspace, conODBC As Connection, qdfN As QueryDef
Dim strConnect As String
'database is the SID of the Oracle-database, DSN is the name of the ODBC datasource
strConnect = "ODBC;DATABASE=database;UID=username;PWD=password; DSN=oradb;"
Set wspODBC = DBEngine.CreateWorkspace("ORCL", "Admin", "", dbUseODBC)
Set conODBC = wspODBC.OpenConnection("conODBC", , False, strConnect)
Set qdfN = conODBC.CreateQueryDef
qdfN.ReturnsRecords = False
qdfN.SQL = "ALTER USER JOHN IDENTIFIED BY NEWPASSWORD"
Set qdfN = Nothing
Set conODBC = Nothing
Set wspODBC = Nothing
I tried doing something very similar using ADO instead of DAO. My problem is that Oracle sends back an error code at the time the connection is opened and the ALTER command fails. I was using the execute method on the connection object to accomplish nearly the same end. My guess is that DAO will produce the same result. Remember, the user has a password the DBA has set as expired. Oracle sends back an error and won't allow the connection via ODBC.
SQLPlus has the capability to get around this by opening a dialog box prompting for the old password, a new password, and passing the typing sobriety test of re-entering the same new password. I'm trying to emulate that behavior in my Access application, but so far, I haven't seen the topic covered anywhere. I don't have Oracle at home either, but I'll see if DAO helps me out tomorrow.
It takes a topic already addressed in 2003, in order to integrate.
I have succesfully used the method proposed by Ad Dieleman, whom I thank for the change of the password to connect to Oracle dB.
This method allows you to run many Oracle SQL commands and is very interesting, but unfortunately the 2007 version of Access is no longer possible to use it as ODBDirect not supported and therefore must use ADO instead of DAO (Run-Time Error 3847)!
Unfortunately I do not have the ability to rewrite the code and then, wonder if someone has already solved the problem !?
I hope in your welcome aid.
Function CHANGEPASS(USR As String, PSW As String, PSWNEW As String, DNS As String, DBB As String) As Boolean
' USR = User [ODBC]
' PSW = Old Password [ODBC]
' PSWNEW = New Password [ODBC]
' DNS = DNS name [ODBC]
' DBB = Database path [ODBC]
On Error GoTo Err
Dim db As Database
Dim LSProc As QueryDef
Dim STConnect As String
Dim STSql As String
Set db = CurrentDb()
Set LSProc = db.CreateQueryDef("")