[AccessD] Invalid Password

Rocky,

    That too can be handled as part of the same automated process. Let the form for changing BE password be available only in the master FE handled by an authorized person. This FE can have a table T_FePaths holding the full paths of all other front ends in its field named FePath.

    Additional subroutine named P_ExportLinkedTablesToAllFrontEnds() in VBA module of master FE as given below, would export the freshly linked tables to all other front ends. Revised code in click event of the command button would be as follows: 

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

    Note:
    (a) Using True as second argument in OpenDatabase method in subroutine named P_ChangeBePwdAndRelink() as given in my earlier post ensures that the back end db is handled in exclusive mode while changing the password.

    (b) TransferDatabase method has the advantage that while implementing export action, if an object with the given name already exists in the target db, it gets replaced by the exported one silently, without any warning message.

    (c) Moreover, TransferDatabase action is remarkably faster than the combination of actions represented by OpenDatabase > DeleteTable > CopyTable methods.

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

' Code in general or form's module
'==============================
Sub P_ExportLinkedTablesToAllFrontEnds()
On Error GoTo ErrTrap
    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim Qst As String, Tnm As String
    
    Set db = DBEngine(0)(0)
    
    ' Paths of all front ends other than the master FE
    ' (Table T_FePaths has paths of all FE's in field FePath)
    Qst = "SELECT FePath FROM T_FePaths;"
    Set rst1 = db.OpenRecordset(Qst)
    
    ' Names of all linked tables in master FE
    Qst = "SELECT Name FROM MSysObjects " & _
            " WHERE Type = 6;"
    Set rst2 = db.OpenRecordset(Qst)
    
    ' Copy all linked tables from master FE to
    ' all other FE's
    Do Until rst1.EOF
        rst2.MoveFirst
        Do Until rst2.EOF
            ' Copy linked table from master FE
            ' to other FE
            Tnm = rst2.Fields(0)
            DoCmd.TransferDatabase acExport, _
                    "Microsoft Access", rst1.Fields(0), _
                    acTable, Tnm, Tnm
            
            rst2.MoveNext
        Loop
        rst1.MoveNext
    Loop

    ' Note:
    '   TransferDatabase method has the advantage
    '   that while implementing export action, if an
    '   object with given name, already exists in the
    '   target db, it gets replaced by the exported one,
    '   without any warning message.
    '   Moreover, TransferDatabase action is remarkably
    '   faster than the combination of actions represented by
    '   OpenDatabase > DeleteTable > CopyTable methods.
    
ExitPoint:
    On Error Resume Next
    rst1.Close
    rst2.Close
    Set rst1 = Nothing
    Set rst2 = 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: Tuesday, October 06, 2009 22:24
  Subject: Re: [AccessD] Invalid Password


  Well, there's a potential procedural problem.  Each user has their own copy
  of the FE.  So the head guy should change the password and then, after
  relinking HIS FE, distribute that FE to the other users.  I think.

  R 

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
  Sent: Tuesday, October 06, 2009 8:57 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Invalid Password

  Just saw it.  Looks like a good solution.  Just as an FYI, if multiple
  people go into this Front end, when the password changes, it will lock the
  users out when one person goes in.  (I'm about 90% certain, because this
  process will probably require exclusive access to the front
  end....)

  Drew

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
  Sent: Tuesday, October 06, 2009 10:23 AM
  To: 'Access Developers discussion and problem solving'
  Subject: Re: [AccessD] Invalid Password

  Drew:

  Thanks for the reply.  Did you see A.D.'s solution.  I think that will work.
  They're using 2003 and actually the primary machine is still on A2K which
  causes reference problems.  He's on the IT list for an upgrade.  Has been
  for about 2 years.  

  R