Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Sat Jul 15 09:41:22 CDT 2006
Oops. wrote too fast - "and modifying the code." should be "modifying
the data." of course. :-[
Rocky
Rocky Smolin - Beach Access Software wrote:
> A.D:
>
> As usual - an elegant solution - and requiring no programming at all
> (just more knowledge). Thank you for that tip and the code.
>
> This will stop folks from going directly into the back end and modifying
> the code. And If I make an mde and hide the database window, that would
> stop them going through the front end.
>
> The requirement, BTW, is more of a 'feel good' thing by the owner who
> doesn't understand the technology but was told by the network guy that
> it would be possible to access the data through the Internet. So
> suddenly he wants some more security on the db. The FE and BE both
> reside on one machine which is not readily accessible physically but
> (and I haven't taken a close look so I don't know for sure) may be
> accessible from other machines in their local network although my users
> says probably not.
>
> In any event, pw protecting the BE stop 95 % of the problem.
>
> Best regards,
>
> Rocky
>
>
> A.D.TEJPAL wrote:
>
>> 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
>>
>>
>
>
--
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com