What secret ways to generate an Error 70 permission problem?

[JMongi]

Here is the scenario. I got some query export to spreadsheet code working (see my other thread...lol). I ran it half a dozen times without issue. For half a second I got it into my head to turn the main code itself into a standalone sub so it can be called and used to export a variety of list queries we would be using. Long story short, I got some errors and decided the cost/benefit analysis didn't work.

So, I wasn't terribly smart and didn't save off a copy of my working code/db before trying to move it from a button call to a module. This may be irrelevant but I'm providing background just in case it is. One of the errors that occurred was an Error 70 - Permission Denied after creating an archive of the existing list (copying to a separate folder) and trying to delete the current list in preparation for generating a new list. This code worked fine pre tweaking. I throw in the towel, move my code back to the way it was. STILL get the same permission error. Look through my code, step through it. Can't see why it would be giving me a permission error. Check folder permissions, file permissions, etc, no issues stand out. I finally decide it has to be a file issue that I can't figure out. I manually delete the current list. Rerun the code and voila! No permission error. I've rerun the code again a handful of times and the delete now functions just fine.

That was a long way of asking, what gives? Any ideas on what I did or what happened that caused there to be a file that was unable to be deleted by Access?

Edit: To throw a possible start to a direction of a conversation. Did my putting some of this fso object in a PUBLIC module sub do something slightly different or necessitate a more specific closing of an instance (that's not in my code) that is handled when the code is in a PRIVATE button click sub?

 

[cheekybuddha]

Have a look in your task manager - do you have a lot of instances of Excel still running (even if you close Access completely).

You may have errors in your Excel automation code leaving hanging instances behind which are locking the file in question.

 

[JMongi]

Have a look in your task manager - do you have a lot of instances of Excel still running (even if you close Access completely).

You may have errors in your Excel automation code leaving hanging instances behind which are locking the file in question.

That was my thought as well, I just wonder why they aren't locked in general (the code runs consecutively without issue normally), and if it was locked why could I manually delete it without problem?

 

[JMongi]

I have a variety of basic subs that are called. I'm not going to post those here. Here is the "FormatList" sub:

Public Sub FormatList(Source As String, Template As String, SheetName As String)
On Error GoTo ErrHandler
DeveloperMode = True
'Coded using late binding so instances are defined as objects and excel specific types are enumerated
'Variable Definitions
Dim excelApp As Object
Dim wbSource As Object, wbTemplate As Object
Dim wsDestination As Object, wsSource As Object, wsTemplate As Object
Dim rngDestination As Object, rngTemplate As Object
Dim lastrow As Long, lastcol As Long
Dim i As Integer

'Transfer data from export source to copy of template file
Set excelApp = CreateObject("Excel.Application")
excelApp.Application.Visible = False
Set wbSource = excelApp.Workbooks.Open(Source)                                                                  'Open Source workbook
Set wbTemplate = excelApp.Workbooks.Open(Template)                                                            'Open Template copy
Set wsTemplate = wbTemplate.Worksheets(1)                                                                     'Set Template sheet
Set wsSource = wbSource.Worksheets(1)                                                                               'Set source workbook sheet
wsSource.Copy after:=wbTemplate.Worksheets(wbTemplate.Worksheets.Count)                                       'Copy source worksheet to end of template copy
wbSource.Close False
Set wsDestination = wbTemplate.Worksheets(wbTemplate.Worksheets.Count)                                        'Set New List Destination Sheet
lastrow = wsDestination.UsedRange.Rows.Count
lastcol = wsDestination.UsedRange.Columns.Count
Set rngDestination = wsDestination.Range(wsDestination.Cells(2, 1), wsDestination.Cells(lastrow, lastcol))      'Set destination data range
Set rngTemplate = wsTemplate.Range(wsTemplate.Cells(2, 1), wsTemplate.Cells(lastrow, lastcol))              'Set template data range
rngTemplate.Cells.Value = rngDestination.Cells.Value                                                           'Set template data equal to destination data
excelApp.Application.DisplayAlerts = False
wsDestination.Delete
excelApp.Application.DisplayAlerts = True

'Format alternating row shading, column borders and column widths for new range of data
With rngTemplate
    .Borders(11).LineStyle = 1
    .FormatConditions.Delete
    .FormatConditions.Add Type:=2, Formula1:="=MOD(ROW(),2)"
    With .FormatConditions(1).Interior
        .ColorIndex = 15
        .TintAndShade = 0
    End With
    .Columns.AutoFit
    For i = 1 To .Columns.Count
        .Columns(i).ColumnWidth = .Columns(i).ColumnWidth + 2
    Next i
End With

ExitHandler:
wbTemplate.Close SaveChanges:=True
excelApp.Quit
Set excelApp = Nothing
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
End Sub

And Here is the main button click event code:

Private Sub btnExport_Click()
On Error GoTo ErrHandler
'Variable Definitions
Dim qryExport As String
Dim exportFileName As String, exportFilePath As String, exportFile As String
Dim templateFileName As String, templateFilePath As String, templatefile As String
Dim listFileName As String, listFilePath As String, listFile As String, listFileLocal As String
Dim listArchivePath As String, listArchiveName As String, listArchive As String
Dim ShtName As String
Dim ProcCheck As Long
Dim fso As Object
Dim FileDate As String

ProcCheck = 0       'Each positive check increments

'Query Export Information
qryExport = "qryAvailability"
exportFileName = "qryAvailability.xlsx"
exportFilePath = "**db location**"
exportFile = exportFilePath & exportFileName

'Template Information
templateFileName = "UATemplate.xlsm"
templateFilePath = exportFilePath
templatefile = templateFilePath & templateFileName

'List Information
ShtName = "UNIT AVAILABILITY LIST"
listFileName = "UNIT AVAILABILITY LIST.xlsm"
listFilePath = "**server list location"
listFile = listFilePath & listFileName
listFileLocal = templateFilePath & listFileName

'Archive Information
listArchivePath = "**server archive location**"
FileDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
listArchiveName = Left(listFileName, Len(listFileName) - 5) & " " & FileDate & ".xlsm"
listArchive = listArchivePath & listArchiveName

If FileExists(exportFile) = False Then       'If export file already exists, query export will fail.
    ProcCheck = ProcCheck + 1
Else
    Dim answer As Boolean
    answer = MsgBox("The exported file already exists.  Do you want to delete it?" _
        , vbQuestion + vbYesNo + vbDefaultButton2 _
        , "WARNING: EXPORT FILE ALREADY EXISTS")
    If answer = vbNo Then
        MsgBox ("Ok.  Aborting Export.")
    Else
        'Delete old export file if it exists
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.DeleteFile exportFile
        Set fso = Nothing
        ProcCheck = ProcCheck + 1
    End If
End If
       
'Check If Current List Open
If IsFileOpen(listFile) = True Then
    MsgBox ("Cannot export. Current list is in use.")
Else
    ProcCheck = ProcCheck + 1
End If
       
If ProcCheck = 2 Then
    'Rename old list and move to archive
    'This step is first to minimize time between checking if this file is open and attempting to delete it
    If FileExists(listFile) Then
        Call FileCopy(listFilePath, listFileName, listArchiveName, listArchivePath)
        If FileExists(listArchive) Then
            Set fso = CreateObject("Scripting.FileSystemObject")
            fso.DeleteFile listFile
            Set fso = Nothing
        End If
    End If
   
    'Export the query to an excel spreadsheet
    Call ExportQuery(qryExport, exportFilePath)

    'Copy the template file
    Call FileCopy(templateFilePath, templateFileName, listFileName)
   
    'Transfer data from export to new copy of the template
    Call FormatList(exportFile, listFileLocal, ShtName)
   
    'Delete the generated export file
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFile exportFile
    Set fso = Nothing
       
    'Transfer New List to List Location
    If FileExists(listFileLocal) Then
        Call FileCopy(templateFilePath, listFileName, listFileName, listFilePath)
        If FileExists(listFile) Then
            Set fso = CreateObject("Scripting.FileSystemObject")
            fso.DeleteFile listFileLocal
            Set fso = Nothing
        End If
    End If
   
    'Acknowledgment Message
    MsgBox ("Export Successful!")
End If

ExitHandler:
'needed code
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
   
End Sub

 

[JMongi]

The part I'm trying to track down/understand is that the archive/delete process has worked fine even while I was troubleshooting other broken parts of the code yesterday and then when everything seemed to be working, worked multiple times in a row. Maybe it is an error trapping issue that only was highlighted when I tried the code in a module sub. Maybe an FSO object issue? Just don't know what I don't know.

 

[theDBguy]

Hi. In my limited experience and knowledge of copying and deleting files using Access and FSO, I seem to run into Error 70 when the process steps over itself.

I mean, disk access is the slowest computing process, so sometimes code process is too fast for it.

 

[JMongi]

@cheekybuddha - To answer your question directly. After all code is finished, there are no rogue instances of Excel still running in the background. This is during normal operation. To be honest, I didn't check before I just manually used File Explorer to delete the file in question.

 

[JMongi]

@theDBguy - That is an interesting thought.

 

[The_Doc_Man]

In general, anything you are doing in Access will not step on itself, permission-wise. Access can be placed into conflict with itself by doing something exclusive and leaving the connection to some table open (thus still locked) and then do something else that trips over the old lock. But that is a very different kind of lock than error 70. Very different error message. So this is not a "pure" Access internal conflict.

In your case you have an Excel Application Object which is technically NOT Access (even though Access is calling the shots). To implement this situation, Access and Windows create a new separate process/task for Excel. When that happens, you now have the potential for parallel operation. If you have a relatively modern computer, it will have multiple processor cores and thus will be physically capable of true parallel operation. All you need is two cores and you can get to the required situation.

Therefore, if you first ask Excel to do something and then ask Access to do something, they can collide. You see, typically with App objects, your request is synchronous only for the length of time it takes to trigger some action. After that action is started, the App can go about its business and leave Access to do something else. And Excel is notorious for not being kind in sharing things for writing. If you are using FSO from Access, it would theoretically be possible for Access/FSO to try to touch a file that Excel wasn't finished tweaking. I don't think that FSO involves a helper task, so the FSO action would be synchronous. But the Excel operation would in its own CPU thread in parallel with Access.

So the question is, WHEN does the Error 70 occur? More specifically, what was the sequence of code just before the error? You are looking for some complex Excel App Object activity that could take a moment to execute, followed by Access/FSO activity such that an overlap could occur.

 

[JMongi]

@The_Doc_Man - It's during this section of code:

'Check If Current List Open
If IsFileOpen(listFile) = True Then
    MsgBox ("Cannot export. Current list is in use.")
Else
    ProcCheck = ProcCheck + 1
End If

If ProcCheck = 2 Then
    'Rename old list and move to archive
    'This step is first to minimize time between checking if this file is open and attempting to delete it
    If FileExists(listFile) Then
        Call FileCopy(listFilePath, listFileName, listArchiveName, listArchivePath)
        If FileExists(listArchive) Then
            Set fso = CreateObject("Scripting.FileSystemObject")
            fso.DeleteFile listFile
            Set fso = Nothing
        End If
    End If
'...More Code That Runs....

The idea behind this block of code is to do a quick check to see if someone has the excel file in use. FYI, The IsFileOPen sub uses the DIR command and error checking for its determiantion. After getting an "all clelar" it increments the ProcCheck variable and then we get to the main code check for prerequisites (no existing export file to cause the qryExport to fail; no file in use to prevent archiving the file when the new file is created).

Everything steps through fine to this point. The next batch of code checks if the file to be archived exists, then copies it into the archive folder and then deletes the original file.
-The file exist check passes.
-The copy file into archive folder works.
-The check to make sure the newly archived file exists passes.
-The Set fso = CreateObject("Scription.FileSystemObject") does not error.
-fso.DeleteFile listFile was where I got the Error 70 - Permission Denied

It worked many times before the error. Once the error happened it always happened at that spot. It only cleared once I browsed to the folder location in file explorer and deleted the file directly. It hasn't errored since.

 

[cheekybuddha]

@cheekybuddha - To answer your question directly. After all code is finished, there are no rogue instances of Excel still running in the background.

Good to rule that one out.

DBG's suggestion of a timing issue may well be on the money.

d

 

[gemma-the-husky]

instead of filecopy, try just name filea as fileb
it will copy to a different drive, or move to the same drive. (I think)

 

[The_Doc_Man]

For the FSO action where you delete the file, change

fso.DeleteFile listFile

to

Kill listFile

That stops creation of the FSO object. I didn't think FSO was handled by a separate process, but I might have been wrong. The KILL verb is the same as an FSO.DeleteFile in effect, but doesn't involve a separate object or entity. Similarly, NAME x AS y will not use a separate FSO object. Doing that means you stay fully synchronous and no parallel actions occur. That might avoid that "collision".

 

[JMongi]

@The_Doc_Man - Does that mean I don't even need to set the FSO? Intsead of:

        If FileExists(listArchive) Then
            Set fso = CreateObject("Scripting.FileSystemObject")
            fso.DeleteFile listFile
            Set fso = Nothing
        End If

I would do:

        If FileExists(listArchive) Then
            Kill listFile
        End If

 

[JMongi]

For reference, here is the FileCopy Sub:

Public Sub FileCopy(SrcPath As String, SrcName As String, DestName As String, Optional DestPath As Variant)
Dim fso As Object
If IsMissing(DestPath) Then
    DestPath = SrcPath
End If

If FileExists(SrcPath & SrcName) = False Then
    MsgBox ("Copy Cancelled - Source File Does Not Exist")
ElseIf FileExists(DestPath & DestName) = True Then
    MsgBox ("Copy Cancelled - Destination File Already Exists")
Else
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    fso.CopyFile SrcPath & SrcName, DestPath & DestName
End If
End Sub

So instead of:

Else
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    fso.CopyFile SrcPath & SrcName, DestPath & DestName

I would do:

Else
     Name SrcPath & SrcName AS DestPath & DestName

 

[cheekybuddha]

Beware, Name renames a file[/folder], so you no longer have the original.

But if you are using FSO to copy the file and then delete the original, then Name it does it all in one pass.

 

[JMongi]

Yeah, that is the intended functionality. This is a stop gap solution to generating some output from the captured data in my database that resembles a better version of what is being used right now. So, these lists will be updated as needed by generating a new list out of Access and archiving the old list. This gets around the current limitations to our computer infrastructure that will make implementing a companywide Access db more time consuming.

 

[gemma-the-husky]

@The_Doc_Man - Does that mean I don't even need to set the FSO? Intsead of:

I would do:

        If FileExists(listArchive) Then
            Kill listFile
        End If

Note that you don't need the fileexists

simply

on error resume next  'in case there is no file
Kill listFile

** caveat. however, if the list file is open/in use than kill or delete will fail for a different reason. (however you try to delete it)

 

[cheekybuddha]

You can create a function like:

Function MoveFile(SrcFilePath As String, DestFilePath As String) As Boolean
On Error GoTo Err_MoveFile

  If Len(Dir(SrcFilePath)) > 0 And Len(Dir(DestFilePath)) > 0 Then
    Kill DestFilePath
  End If
  If Len(Dir(SrcFilePath)) > 0 Then
    Name SrcFilePath As DestFilePath
    MoveFile = Len(Dir(DestFilePath)) > 0
  End If

Exit_MoveFile:
  Exit Function

Err_MoveFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: MoveFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: " & Application.VBE.ActiveCodePane.CodeModule.Name, , _
           "Error: " & Err.Number
  End Select
  Resume Exit_MoveFile

End Function

Add/Handle any errors you encounter in the error handler.

hth,

d

 

[cheekybuddha]

Or you can make it more in line with the proc you posted in post#15 :

Function MoveFile(SrcPath As String, SrcName As String, _
                  DestName As String, Optional DestPath As Variant = Null) As Boolean
On Error GoTo Err_MoveFile

  Dim SrcFilePath As String, _
      DestFilePath As String, _
      strMsg As String

' First, check whether source path has a trailing slash, add one if not
  If Right(SrcPath, 1) <> "\" Then SrcPath = SrcPath & "\"
' Build source filepath
  SrcFilePath = SrcPath & SrcName

' if no dest path passed then use source path
  DestFilePath = Nz(DestPath, SrcPath)
' Check and add trailing slash if required
  If Right(DestFilePath, 1) <> "\" Then DestFilePath = DestFilePath & "\"
' Build dest filepath
  DestFilePath = DestFilePath & DestName

' Check source exists
  If Len(Dir(SrcFilePath)) > 0 Then
'   Check if dest already exists
    If Len(Dir(DestFilePath)) > 0 Then
'     If it exists check whether user wants to replace it
      strMsg = "Destination file already exists:" & vbNewLine & _
               DestFilePath & vbNewLine & vbNewLine & _
               "Do you want to replace it?"
      If MsgBox(strMsg, vbYesNoCancel, "Replace existing file ...") = vbYes Then
'       User wants to replace, delete existing file
        Kill DestFilePath
        DoEvents
      Else
'       User doesn't want to replace, abort
        Exit Function
      End If
    End If
'   Perform the move
    Name SrcFilePath As DestFilePath
    DoEvents
    MoveFile = Len(Dir(DestFilePath)) > 0
  Else
'   Notify user source file doesn't exist
    strMsg = "Move Cancelled - Source File Does Not Exist"
    MsgBox strMsg, vbOKOnly, "File missing ..."
  End If

Exit_MoveFile:
  Exit Function

Err_MoveFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: MoveFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: " & Application.VBE.ActiveCodePane.CodeModule.Name, , _
           "Error: " & Err.Number
  End Select
  Resume Exit_MoveFile

End Function