[AccessD] [SPAM SUSPECT] Opening password protected database

Rocky,

    If linked tables are imported AFTER setting the password for BE, you will be prompted once for the BE password and the links will get established. 

    Thereafter, so long as the BE path remains undisturbed, you do can keep referring to CurrentDb as usual. There will be no error and you do not have to bother about the BE.

    However, if the BE password is set or changed after table links have already been established, it will be necessary to modify the connect strings for linked tables accordingly. Once that has been done, you can carry on smoothly as per previous para.

    Sample subroutine named P_RefreshLinks(), as given below facilitates setting up the revised links even if the BE has a changed or newly set up password. It takes the full path of BE as its argument.

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

====================================
Sub P_RefreshLinks(ByVal BePath As Variant)
' References - DAO 3.6 and MS Scripting RunTime.
' ReLinks all tables pertaining to BackEnd db
' identified by BePath.
' BePath is the full path of BE db
' (complete with file extn)
On Error Resume Next
    Dim Cnt As Long, CheckPwd As String
    Dim Lnk1 As String, Lnk2 As String, Lnk3 As String
    Dim Lnk As String, Dbn As String, Cns As String
    Dim db As DAO.Database, tdf As TableDef
    Dim fso As FileSystemObject
    
    ' Check for existence of BE db
    Set fso = New FileSystemObject
    If fso.FileExists(Nz(BePath, "")) = False Then
        MsgBox "Db Path Does Not Exist" & _
                    vbCrLf & "(" & BePath & ")"
        GoTo ExitPoint
    End If
    ' Get name of BE db
    Dbn = Mid(BePath, InStrRev(BePath, "\") + 1)
    
    CheckPwd = "N"          ' Default
    Set db = CurrentDb
    Cnt = 0
    For Each tdf In db.TableDefs
        Cns = tdf.Connect
        ' Skip the loop if it is not a linked table, or
        ' if the link does not pertain to the
        ' specified BackEnd
        If Len(Cns) = 0 Or _
                            InStr(Cns, Dbn) = 0 Then
            GoTo Skip
        End If
    
        ' Build the Link string
        Lnk1 = "MS Access"
        Lnk3 = "DATABASE=" & BePath
        ' This block ensures that the connection link
        ' need not be re-built in every cycle of the loop
        If CheckPwd <> "Y" Then
            If InStr(Cns, "PWD=") > 0 Then
                Lnk2 = Mid(Cns, InStr(Cns, "PWD="))
                If InStr(Cns, ";") > 0 Then
                    Lnk2 = Left(Lnk2, InStr(Lnk2, ";") - 1)
                End If
                Lnk = Lnk1 & ";" & Lnk2 & ";" & Lnk3
            Else
                Lnk = Lnk1 & ";" & Lnk3
            End If   ' InStr
        End If   ' CheckPwd
    
        tdf.Connect = Lnk
        tdf.RefreshLink
        ' There will be error if BackEnd password has changed
        ' (If so, prompt for the new password)
        If Err.Number <> 0 Then
            Lnk2 = Trim(InputBox("Enter Password for " & Dbn))
            ' Statement below covers the situation even if password
            ' for back-end db has been chnged from something to nothing.
            Lnk2 = IIf(Len(Lnk2) > 0, "PWD=" & Lnk2, "")
            Lnk = Lnk1 & ";" & _
                        IIf(Len(Lnk2) > 0, Lnk2 & ";", "") & Lnk3
            CheckPwd = "Y"
            tdf.Connect = Lnk
            Err.Clear
            tdf.RefreshLink
            ' Error at this stage means wrong password
            ' was entered. Exit procedure
            If Err.Number <> 0 Then
                MsgBox "Wrong Password"
                GoTo ExitPoint
            End If
        End If
        CheckPwd = "Y"
        Cnt = Cnt + 1
        
Skip:
    Next
    MsgBox Cnt & " Tables Linked Successfully" & _
                vbCrLf & "(To  " & Dbn & ")"
    
ExitPoint:
    Set tdf = Nothing
    Set db = Nothing
    On Error GoTo 0
    
    ' Note - It is observed that if existing password for
    '            back-end db is removed altogether, the
    '            linked tables continue to function properly,
    '            without any need to refresh links.
    '            (even though connect string continues to
    '            have the old password).
End Sub
====================================

  ----- Original Message ----- 
  From: Rocky Smolin - Beach Access Software 
  To: Access Developers discussion and problem solving 
  Sent: Friday, July 14, 2006 23:32
  Subject: Re: [AccessD] [SPAM SUSPECT] Opening password protected database


  Bobby:

  I tried using set db = Currentdb after putting the password on the back 
  end and got an 'invalid password' error when opening the first recordset 
  to a linked table.

  Rocky


  Bobby Heid wrote:
  > Did not read all of the email.
  >
  > If the tables in the BE are linked, then you can use CurrentDB.  You only
  > have to use the Opendatabse method if you are opening the BE directly.
  >
  > Now, you will have to use the password when linking the tables.
  >
  > Bobby