Moving a windows folder via VBA causes security permissions problems

[jmfenn]

I'm having an issue with security permissions. We have a folder "S:\Work Flow" with a folder for each employee. The front end and admin staff can see all the folders, but the employees only see their own folders. When a file is assigned to an employee by the front end staff, the code creates a folder with the file number in the relevant employee's workflow folder. So, for example, when file 154567 is assigned to JFenn in our access order sheet, it creates a folder "S:\Work Flow\JFenn\154567". This folder has the same permissions as its parent folder, which is to say that it is only visible to the front end, admin, and JFenn.

Sometimes, though, we need to change the employee to whom the file was assigned. So if the file is taken away from JFenn and assigned to JSmith, it would move (cut and paste, because there may be files in that folder already) the folder from "S:\Work Flow\JFenn\154567" to "S:\Work Flow\JSmith\154567". When I do this action manually using ctrl-x, the security permissions of the moved folder are changed so that JFenn is no longer allowed access, and JSmith is allowed access to this folder. When I make the change with VBA from Access, however, JFenn is the only employee with access to that folder still, only he can't see JSmith's folder. JSmith can't see the moved folder at all: it looks to him as though nothing has happened. I really don't want to have to do this change manually... can anyone help me? Here is the relevant bit of code.

Private Sub AppraiserCB_AfterUpdate()
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String


    ToPath = "S:\Work Flow\" & FolderName(Me.AppraiserCB.Value) & "\" & Me.FileNumber
    FromPath = "S:\Work Flow\" & FolderName(Me.AppraiserCB.OldValue) & "\" & Me.FileNumber
    Set FSO = CreateObject("scripting.filesystemobject")
    If Me.AppraiserCB.OldValue <> 77 And FSO.folderexists(FromPath) = True Then
            If FSO.folderexists(ToPath) = True Then
                MsgBox "The system is trying to move all the data from the folder " & FromPath & " to the folder " & ToPath & ", but both folders already exist. You need to manually decide which folder to keep."
                Exit Sub
            End If
            FSO.movefolder Source:=FromPath, Destination:=ToPath
        Else
            If Dir(ToPath, vbDirectory) = "" Then MkDir ToPath
    End If
    DoCmd.RunCommand acCmdSaveRecord
End Sub

[ranman256]

You shouldn't need to move folders.

[Micron]

I would put it another way: this is ill-advised. When the transition needs to be made, is it so important that you restrict the former "owner" from seeing the file? If there is some sort of legal privacy law that you're trying to comply with, it wouldn't take much effort to circumnavigate your 'security' if you're using Access to gain access to these files. There might be other ways of transferring ownership other than getting down to network permissions.

[jmfenn]

It's not about security, it's about stopping people from accidentally deleting each other's work. I have to code to the lowest common denominator here. It's way easier to spend a day coding than an unsuccessful month trying to change user behavior...

[Micron]

perhaps a table with userID as a FK from your user table and a path to their documents and present only the ones associated with their name? You could easily alter the user ID based on changed responsibility.