Access VBA宏执行Kill/FileCopy触发运行时错误70（权限被拒）求助

Let's tackle that frustrating Run-time Error 70 (Permission Denied) you're hitting in your Access VBA macro. I've seen this exact issue pop up even when users swear no files are open and permissions are set correctly—here are the most likely culprits and fixes:

1. Transient File Locks from Background Processes #

Even if you didn't open the target file or template, background services like Windows Search indexing, antivirus scanners, or leftover Excel temporary file handlers might be holding a lock on the files. These locks are often short-lived, but they can interfere with your Kill/FileCopy operations.

Fixes: #

• Add a short delay before attempting the file operations to give background processes time to release locks. You'll need to declare the Sleep API first:

  #If VBA7 Then
      Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
  #Else
      Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  #End If
  
  ' Insert this right before your Kill/FileCopy lines
  Sleep 1000 ' Wait 1 full second (adjust as needed)
  

• Use a retry loop for the Kill operation to handle transient locks gracefully:

  Dim retryCount As Integer
  retryCount = 0
  
  Do While retryCount < 3
      On Error Resume Next
      Kill sOutputFile
      If Err.Number = 0 Then Exit Do ' Success, exit loop
      On Error GoTo 0
      
      Sleep 500 ' Wait half a second before retrying
      retryCount = retryCount + 1
  Loop
  
  If retryCount = 3 Then
      MsgBox "Failed to delete file after 3 attempts: " & sOutputFile, vbCritical
      Exit Sub
  End If
  

2. Invalid or Missing Directories #

Your code assumes the \Output Files\Recon\ and \Temp Files\ directories exist—but if they don't, FileCopy will fail with a permission error (even though the real issue is a missing folder). Also, hidden trailing spaces in sDate or sClient can create invalid paths that the OS treats as inaccessible.

Fixes: #

• Add debug prints to verify your paths are resolving correctly:

  Debug.Print "Output File Path: " & sOutputFile
  Debug.Print "Template File Path: " & sTemplateFile
  

  Check the Immediate Window (Ctrl+G in the VBA editor) to ensure there are no weird characters or typos.
• Add code to create directories if they're missing:

  ' Create output directory if it doesn't exist
  Dim outputDir As String
  outputDir = CurrentProject.Path & "\Output Files\Recon\"
  If Dir(outputDir, vbDirectory) = "" Then MkDir outputDir
  
  ' Create temp directory if it doesn't exist
  Dim tempDir As String
  tempDir = CurrentProject.Path & "\Temp Files\"
  If Dir(tempDir, vbDirectory) = "" Then MkDir tempDir
  

3. Unclosed File Handles from Previous Operations #

You mentioned you "cleared the two files" earlier—if you used Open statements or Excel automation to modify them, make sure those file handles were properly closed. Lingering Excel instances (even hidden ones) can hold locks on files without you realizing it.

Fixes: #

• If you're using Excel automation, explicitly quit the app and release all objects:

  Dim xlApp As Object
  Set xlApp = CreateObject("Excel.Application")
  
  ' ... your Excel operations here ...
  
  xlApp.Quit
  Set xlApp = Nothing ' Critical to release the file handle
  

• Audit your code to ensure any Open statements are paired with Close statements:

  Open sOutputFile For Output As #1
  ' ... write to file ...
  Close #1 ' Don't forget this!
  

4. Read-Only Template File #

Even if you have directory access, the template file itself might be marked as read-only, which can cause issues during FileCopy.

Fix: #

Add code to remove the read-only attribute before copying:

If GetAttr(sTemplateFile) And vbReadOnly Then
    SetAttr sTemplateFile, vbNormal
End If

Additional Debugging Tips #

• Enable detailed error trapping to get more context:

  Sub YourMacroName()
      On Error GoTo ErrorHandler
      
      ' ... your existing code ...
      
      Exit Sub
      
  ErrorHandler:
      MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & "Line number: " & Erl
  End Sub
  

• Try running your Access database as Administrator—UAC (User Account Control) can sometimes block file operations in protected directories like Program Files, even if you have full permissions.

内容的提问来源于stack exchange，提问作者heaton124