Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Question Unanswered: Change password in Oracle using ODBC

    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?
    Regards,
    Terry

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Change password in Oracle using ODBC

    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.


    Sub Example()
    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"
    qdfN.Execute
    qdfN.Close
    Set qdfN = Nothing
    Set conODBC = Nothing
    Set wspODBC = Nothing
    End Sub

    Best of luck.

    Ad Dieleman.

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    I'll try it but ...

    Thanks so much for replying.

    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.
    Regards,
    Terry

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    I had problems with Microsoft's drivers for ORACLE, try original ORACLE drivers when you change the password....

    jiri

  5. #5
    Join Date
    Aug 2015
    Posts
    2

    Change Password in Oracle by ODBC VBA ACCESS

    Good morning
    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.

    THANK YOU

    Giulio

  6. #6
    Join Date
    Aug 2015
    Posts
    2

    Change password & SQl code

    I have resolved with this code:

    ================================================== =======================
    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

    'Declarations
    Dim db As Database
    Dim LSProc As QueryDef
    Dim STConnect As String
    Dim STSql As String

    'Set
    Set db = CurrentDb()
    Set LSProc = db.CreateQueryDef("")

    CHANGEPASS = False
    STConnect = "ODBC;DATABASE=" & DBB & ";UID=" & USR & ";PWD=" & PSW & "; DSN=" & DNS & ";"
    STSql = "ALTER USER " & USR & " IDENTIFIED BY " & PSWNEW & ""

    'Use {Microsoft ODBC for Oracle} ODBC connection
    LSProc.Connect = STConnect
    LSProc.sql = STSql
    LSProc.ReturnsRecords = False
    LSProc.ODBCTimeout = 0

    LSProc.Execute

    Set LSProc = Nothing
    db.Close

    CHANGEPASS = True

    MsgBox "PASSWORD regularly changed!", vbOKOnly, "Password"

    Exi:
    Exit Function
    Err:
    MsgBox "Procedure not completed correctly! Check.", vbCritical, "Error"
    Resume Exi

    End Function
    ================================================== ============

    Good luck

    Giulio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •