[AccessD] Invalid Password

A.D.:

Thank you for the code.  I should send an invoice to the client in your
favor. 

Best,

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Monday, October 05, 2009 5:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Invalid Password

Rocky,

    Subroutine P_ChangeBePwdAndRelink() as given below, will do the needful.
Place two text boxes named TxtPwdNew and TxtPwdNewConfirm (with password as
the input mask) on a form in the FE. The code in click event of command
button on this form would be:

' Code in form's module
'==========================
Private Sub CmdSetNewPwd_Click()
    If Len(Me.TxtPwdNew) > 0 And _
                    Me.TxtPwdNewConfirm = _
                    Me.TxtPwdNew Then
        P_ChangeBePwdAndRelink Me.TxtPwdNew
    Else
        MsgBox "Discrepancy in entries"
    End If
End Sub
'==========================

    With the above arrangement, the user simply to enters the new password
in the text boxes and clicks the command button for automated change of
password in BE, accompanied by freshly linked tables in FE.

Best wishes,
A.D. Tejpal
------------

' Code in general or form's module
'====================================
Sub P_ChangeBePwdAndRelink(NewPwd As String) On Error GoTo ErrTrap
    ' Changes password of back end db, and
    ' replaces all existing linked tables by fresh
    ' ones, with revised connection string as per
    ' new password.
    Dim db As DAO.Database
    Dim dbBE As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim BePath As String, ConnString As String
    Dim OldPwd As String, Qst As String
    Dim Cnt As Long, Rtv As Variant
    Dim Tnm As String
    
    Const BePrefix As String = "DATABASE="
    Const PwdPrefix As String = "PWD="
    Const TempTableName As String = "T_ZZZTemp"
    
    Set db = DBEngine(0)(0)
    
    ' Delete TempTable (T_ZZZTemp) - if existing.
    On Error Resume Next
    DoCmd.DeleteObject acTable, TempTableName
    On Error GoTo ErrTrap
    
    ' Make fresh temp table so as to reflect latest
    ' status of linked tables.
    Qst = "SELECT Connect, Database, " & _
            " Name INTO " & TempTableName & _
            " FROM MSysObjects " & _
            " WHERE Type = 6;"
    db.Execute Qst, dbFailOnError
    
    Set rst = db.OpenRecordset(TempTableName)
    If rst.RecordCount > 0 Then
        ' Get existing BE path and connect string
        BePath = rst.Fields("Database")
        ConnString = rst.Fields("Connect") & BePath
        
        ' Extract existing password from conn string
        Rtv = Split(ConnString, ";")
        OldPwd = ""
        For Cnt = 0 To UBound(Rtv)
            If Rtv(Cnt) Like PwdPrefix & "*" Then
                OldPwd = Mid(Rtv(Cnt), _
                                    Len(PwdPrefix) + 1)
                Exit For
            End If
        Next
        If NewPwd <> OldPwd Then
            ' Change BE password to new one.
            Set dbBE = OpenDatabase(BePath, True, _
                    False, ";" & PwdPrefix & OldPwd & ";")
            dbBE.NewPassword OldPwd, NewPwd
            
            ' Modify the connect string as per new password
            ConnString = Replace(ConnString, _
                                    PwdPrefix & OldPwd, _
                                    PwdPrefix & NewPwd)
            
            ' Delete existing linked tables and recreate them
            ' afresh as per new connect string.
            Do Until rst.EOF
                Tnm = rst.Fields("Name")
                DoCmd.DeleteObject acTable, Tnm
    
                DoCmd.TransferDatabase acLink, _
                        "Microsoft Access", BePath, _
                        acTable, Tnm, Tnm
                
                rst.MoveNext
            Loop
            
            db.TableDefs.Refresh
            
            MsgBox "New password as specified, has " & _
                        "been set for the BE" & vbCrLf & _
                        "Connect strings for all linked tables " & _
                        "have also been revised accordingly"
        Else
            MsgBox "Newly supplied password is " & _
                        "same as existing one"
        End If
    Else
        MsgBox "No Linked Tables Found"
    End If
    
    ' Note:
    '   If the back end db is password protected,
    '   assignment of a new connect string by using a
    '   statement like tdf.Connect = ConnString becomes
    '   ineffective The existing connect string continues
    '   to hold good). Also, statement like tdf.RefreshLink
    '   gives error.
    
ExitPoint:
    rst.Close
    Set rst = Nothing
    Set dbBE = Nothing
    Set db = Nothing
    On Error GoTo 0
    Exit Sub
        
ErrTrap:
    MsgBox Err.Number & " - " & Err.DESCRIPTION
    Resume ExitPoint
End Sub
'=====================================

  ----- Original Message -----
  From: Rocky Smolin
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, October 05, 2009 09:26
  Subject: Re: [AccessD] Invalid Password


  A.D.:

  I understand and that looks like a good solution - I prefer functions that
  work through the front end with a button click - keeps the user out of the
  OS.

  But to change the password on the back end I believe you need to open the
BE
  exclusive.  So how can you change the BE password through the front end?

  Thanks and regards,

  Rocky Smolin
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com