Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    File System Object - DeleteFile fails because of Permission Denied error

    The code below captures a fully qualified archived file name from a combo box and inquires of the user whether it is to be ONLY viewed or used to restore the app's primary data. In preparation to moving the archived file to the app's principle folder, the primary backend file needs to be deleted. As can be seen in the screenshot, the FSO.DeleteFile fails because of permissions issue. With the "frmRegister" having been closed and the RowSource of the combo set to a zero length string, there shouldn't be any binding issues at hand, though my understanding is that FSO isn't affected even if some binding is present.



    Code:
    Private Sub cboArchView_Click()
    Dim strArcFile As String
    Dim strViewChoice As String
    
    strArcFile = Me.cboArchView.Column(1)  'Capture whatever file name might have been selected
    
    strViewChoice = MsgBox(strArcFile & vbNewLine & _
             "archive file selected.  Do you want to " & vbNewLine & _
             "RESTORE it as primary data? ""YES""  Or just" & vbNewLine & _
             "open archive for viewing only ""NO"".", vbYesNoCancel)
                   
        strArcFile = strArchFoldr & strArcFile
        Select Case strViewChoice
            Case vbYes
                Me.cboArchView.RowSource = ""        'Drop binding on combo
                SetAttr strArcFile, vbNormal
                DoCmd.Close acForm, "frmRegister"
                
                Set FSO = CreateObject("Scripting.FileSystemObject")
    
                    Select Case strDataMode
                        Case "Personal"
                            FSO.DeleteFile strPerDB
                            FSO.MoveFile strArcFile, strPerDB
                        Case "Business"
                            FSO.DeleteFile strBusDB
                            FSO.MoveFile strArcFile, strBusDB
                    End Select
    
            Case vbNo
                Call ReLink(strArcFile, True)
                MsgBox "Press function key ""F9"" when done"
            Case Else
        End Select
    
    Set FSO = Nothing
    
    Me.cmdViewArch.SetFocus
    Me.cboArchView.Visible = False
    
    End Sub

    As one can observe in the following screenshot, where Debug shows the file name, that there must be some other issue rather than the name which I know to be valid.
    Click image for larger version. 

Name:	000.jpg 
Views:	19 
Size:	81.3 KB 
ID:	37214


    What seems to be the problem with the FSO.DeleteFile?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can't move a file if it has been deleted.
    You can't delete a file that is in use.

    First check you have permissions to edit files in the two folders.
    Check the file isn't in use.
    Then do fso.copyfile followed by kill to delete the original
    Or just do fso.movefile

    Hope that works or you. It early here. I'll go and have some coffee now then go and play in the overnight snow.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Colin,
    Below is the segment of code of interest: (Added: Call ReLink(strEmtyDB, False) 'Free-up our primary for the moment)
    Code:
     Case vbYes
                Me.cboArchView.RowSource = ""        'Drop binding on combo
                SetAttr strArcFile, vbNormal
                DoCmd.Close acForm, "frmSettings"
                DoCmd.Close acForm, "frmRegister"
                
                
                Set FSO = CreateObject("Scripting.FileSystemObject")
                Call ReLink(strEmtyDB, False)   'Free-up our primary for the moment
                
                    Select Case strDataMode
                        Case "Personal"
                            FSO.DeleteFile strPerDB
                            FSO.MoveFile strArcFile, strPerDB   'Restore archive to primary
                            Call ReLink(strPerDB, True)         'Link us back up and re-open the app
    I added the call to ReLink the app's primary DB, strPerDB. With that and what was already coded there are NO ELEMENTS BOUND to the app's primary backend. The intent, if not apparent, is to delete the primary strPerDB backend so that I can MOVE the archive file into its place while essentially renaming the archive in the process.

    The current user, me, has "Full Control" permission on file strPerDB. If, after the ReLink, I leave the app open I CANNOT delete strPerDB from Windows Explorer because Windows says Access has it in use.
    In WHAT state it's in use is a complete mystery to me, so I remain stumped.
    Bill

    Early for me too, I'm going back to bed.................

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK I'll try that again
    If you want to overwrite the existing strPerDB (or strBusDB) with strArcFile, do one of the following:

    Code:
    Select Case strDataMode                    
    Case "Personal"
    Kill strPerDB FSO.MoveFile strArcFile, strPerDB
    Case "Business" Kill strBusDB FSO.MoveFile strArcFile, strBusDB End Select
    or if you want to keep strArcFile as well

    Code:
    Select Case strDataMode
    Case "Personal"
    Kill strPerDB
    FSO.CopyFile strArcFile, strPerDB
    Case "Business"
    Kill strBusDB
    FSO.CopyFile strArcFile, strBusDB
    End Select
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Hi Colin,
    I coded "Kill" to begin with and that also raises the Err 70 so then switched to FSO. After eliminating bonding and link issues is when I decided to post.

    I moved the offending code to a general module (below) to see if it made any difference if the only form still open got closed first. Frustrating, as Err 70 is persisting.

    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub Restore(ArcFile As String)
    Dim FSO As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Call ReLink(strEmtyDB, False)   'Free-up our primary for the moment
    
        Select Case strDataMode
        
            Case "Personal"
                FSO.DeleteFile strPerDB    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Err 70 here
                FSO.MoveFile ArcFile, strPerDB   'Restore Personal archive to primary
                Call ReLink(strPerDB, True)         'Link us back up and re-open the app
                
            Case "Business"
                Kill strBusDB
                FSO.MoveFile ArcFile, strBusDB   'Restore Personal archive to primary
                Call ReLink(strBusDB, True)         'Link us back up and re-open the app
                
        End Select
    
    Set FSO = Nothing
    End Sub

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not that it will solve this issue, but I really recommend you add proper error handling to each of your procedures

    If it isn't a permissions issue (have you checked), then the file you are trying to delete MUST still be in use.
    Are you SURE you don't any open forms or a persistent connection to the BE file?

    BTW You now have a mixture of Kill and FSO.DeleteFILE
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I really recommend you add proper error handling to each of your procedures
    Just hadn't added it yet......... just shot-gunning trying to get either Kill or DeleteFile to not fail.

    BTW You now have a mixture of Kill and FSO.DeleteFILE
    I know, just trying to get one of the methods to work.

    Are you SURE you don't any open forms or a persistent connection to the BE file?
    There are no forms or reports open and I've ReLinked all tables to a different backend. Where else might there be a "snake-in-the-grass" (connection)? If there were a DOA Recordset left Set somewhere, which I seriously doubt, the ReLink'ing would have severed that connection...... right?

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    The app "eBud" is started by the user via a typical Open. Under the covers is a "standard" macro "Autoexec" that runs a general module I refer to as eBud's "Front Gate". That code simply addresses whether the user is opening eBud for personal, business or potentially both. In any case, at that juncture there are NO CONNECTIONS other than the FrontEnd->BackEnd links present at the time of the last execution of eBud. With all of that being said, I added as the first statement of the "Front Gate" to Kill strPerDB. Where strPerDB is a global constant that defines the app's principle names. That statement executed without error.

    I will persist in my search for the "snake-in-the-grass".

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See if my AvailableConnections utility can find your snake curled up somewhere warm and out of the way

    Surely error handling is most useful of all in exactly this sort of dilemna rather than adding it later!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I see one form and one report in your utility. How is the utility employed?

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry - I explained it in an AWF thread but it needed more explanation on the website
    I've just added that now. Let me know if it makes sense
    The info button on the form may also be helpful

    Anyway, import both items into your database
    Run the form just after starting your database when you want to do a snake in the grass type check.
    You should have 255 connections unless you already have something in use
    Leave the form open but put it in an out of the way position.
    Run other tasks and click requery on the form. The number of connections will drop each time something new is opened
    They should recover as items are closed ...if everything is being closed / set to nothing

    Close everything ready to do your restore. Requery the form. Does it list anything still open?
    Have the connections recovered?

    My strong suspicion is you will find something is still using connections although you believe everything has been closed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Okay, the screenshot below is the Re-queried Availability form at the point where the Kill/DeleteFile would be attempted. If I'm using your utility correctly, I don't see any "snakes"?
    Click image for larger version. 

Name:	000.jpg 
Views:	13 
Size:	61.7 KB 
ID:	37229

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Interesting that frmRegister used 13 connections but I agree it all looks clear
    Can't remember whether I've already suggested opening the FE using the shift bypass to prevent code running at startup
    Then run your code without opening anything at all
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Then run your code without opening anything at all
    Yes, I did that earlier this AM presumably having the same thought as you have now. The Kill/DeleteFile ran without error when I did that.

    Interesting that frmRegister used 13 connections
    frmRegister has a bunch of bound text boxes so no surprise there.

  15. #15
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Well, this is interesting........... I think

    Referring back to the code posted in #5, I changed the file name to the "Business" backend to see if I could delete that while having opened the app for "Personal" use. The "Business" mdb isn't involved in any way when the app is opened for "Personal" use. Yet, DeleteFile fails with the Err 70 Permission Denied error with that file, as the next screenshots show:
    Click image for larger version. 

Name:	001.jpg 
Views:	9 
Size:	25.0 KB 
ID:	37240Click image for larger version. 

Name:	004.jpg 
Views:	9 
Size:	19.8 KB 
ID:	37241

    I did make one pass through the DeleteFile code specifying a totally unrelated mdb file to test FSO DeleteFile and the file deleted okay so the problem seems to lay elsewhere.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-10-2017, 11:56 PM
  2. Replies: 3
    Last Post: 07-12-2017, 04:29 PM
  3. Replies: 3
    Last Post: 01-21-2017, 04:09 AM
  4. Getting Permission Denied error when moving file
    By ricksil in forum Programming
    Replies: 2
    Last Post: 01-20-2016, 11:21 AM
  5. Replies: 3
    Last Post: 09-01-2010, 08:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums