Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1843
  • Last Modified:

VBA MS Access Permission Denied when using Kill statement

I'm using the Kill statement to delete some files after creating them from a MS Access report and then emailing them as an attachment.  I get the Permission denied Error on the Kill statement.  What's odd is that I don't always get the error.  One time it will work fine and the next time I'll get the error.  Is there anything I can do to avoid getting this error?

I'm developing on a machine with 07 but the DB is saved in 03 format

Here is the code.
If Dir(strFilePath & "pdfReport1.pdf") <> "" Then
        Kill strFilePath & "pdfReport1.pdf"
    End If

    If Dir(strFilePath & "pdfReport2.pdf") <> "" Then
        Kill strFilePath & "pdfReport2.pdf"
    End If

Open in new window


Thanks!
0
AronMcD
Asked:
AronMcD
  • 8
  • 6
  • 4
  • +2
2 Solutions
 
Dale FyeCommented:
Chances are that someone has the file is open, or you do not have delete permissions for the folder in which the file is located.
0
 
AronMcDAuthor Commented:
I have full permissions.  I'll try another folder to test.  
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Sounds like the emailing process still have the file lock.

What VBA code are you sing to send the email?

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
try, before killing the files
in your code that sends the file as attachment,

'your emailing codes with attachment


'add this line
  doevents

'your codes to kill the files

or



try reversing the process...
* kill the file if they exists
* create the file
* email the file as attachment

0
 
AronMcDAuthor Commented:
HighTech,
Here is the code:

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strFilePath As String

    strFilePath = GetDBPath
    
    g_ItemizedPricing = True
    DoCmd.OpenReport "MFGSalesOrder", acViewPreview, , "TrxID = " & iTrxID, acHidden, iTrxID
    DoCmd.OutputTo acOutputReport, "MFGSalesOrder", acFormatPDF, "C:\pdfReport1" & ".pdf"
    DoCmd.Close acReport, "MFGSalesOrder", acSaveNo

    g_ItemizedPricing = False
    DoCmd.OpenReport "MFGSalesOrder", acViewPreview, , "TrxID = " & iTrxID, acHidden, iTrxID
    DoCmd.OutputTo acOutputReport, "MFGSalesOrder", acFormatPDF, "C:\pdfReport2" & ".pdf"
    DoCmd.Close acReport, "MFGSalesOrder", acSaveNo

    ' Create the Outlook session.

    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg

        ' Set the Subject, Body, and Importance of the message.
        If g_User_Task = "EmailOrder" Then
            .Subject = strQuoteNum & " " & "(" & strCapacity & " MFG)" & " " & strCustomer & " - ORDER " & "VIN: " & strVin
        ElseIf g_User_Task = "EmailQuote" Then
            .Subject = strQuoteNum & " " & "(" & strCapacity & " MFG)" & " " & strCustomer & " - QUOTE "
        End If
        
        .Body = "***THIS QUOTATION IS GOOD FOR 15 DAYS/SUBJECT TO A SURCHARGE***" & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance

        ' Add attachments to the message.
        Set objOutlookAttach = .Attachments.Add(strFilePath & "DealerCover" & ".doc")

        Set objOutlookAttach = .Attachments.Add("C:\pdfReport1" & ".pdf")
        Set objOutlookAttach = .Attachments.Add("C:\pdfReport2" & ".pdf")

       ' Should we display the message before sending?
           'Display the message
           .Display
           'Send immediatley
'           .Save
'           .Send
        End With
    
    Set objOutlook = Nothing
    
    'deleting files when finished
    
    'testing C: drive
    If Dir("C:\pdfReport1.pdf") <> "" Then
        Kill strFilePath & "pdfReport1.pdf"
    End If

    If Dir("C:\pdfReport2.pdf") <> "" Then
        Kill strFilePath & "pdfReport2.pdf"
    End If
    
    
    DealerEmail = True
       
Exit_DealerEmail:
    Exit Function

Err_DealerEmail:
    MsgBox Err.Description
    DealerEmail = False
   Resume Exit_DealerEmail

Open in new window


capricord,
I'll give doevents a try.

0
 
AronMcDAuthor Commented:
Correction in code I posted
The Kill statement should be:
Kill "C:\pdfReport1.pdf"

Rather than:
 Kill strFilePath & "pdfReport1.pdf"
0
 
Jeffrey CoachmanMIS LiasonCommented:
Depending on your system setting, "Kill" may not be allowed in VBA.

You may have to load the Scripting Runtime library in your VBE, and do it this way:

   Dim fso As FileSystemObject
   Set fso = CreateObject("Scripting.FileSystemObject")
   fso.DeleteFile ("C:\YourFolder\YourFile.xxx")

JeffCoachman


0
 
AronMcDAuthor Commented:
I just ran it 5 times without making any changes and I didn't get the error.  This morning I got the error about 4 times.  I didn't do anything so I'm not really sure what's going on.  Things are tough to correct when there is no consistency.  Frustrating.  
0
 
AronMcDAuthor Commented:
Jeff,
I can an error "User-defined type not defined" on Dim fso As FileSystemObject when I compile.
0
 
Dale FyeCommented:
You might want to consider implementing an error handler in that routine, trapping that specific error attempting the delete another time within the error handler.  Sometimes, I've experienced situations where when I step through the code, the Kill action succeeds but when I run it without breakpoints it fails.  This is usually due to the fact that the file is still locked for some reason.  If you put something in your error handler and add a brief pause and then try the deletion again, that might take care of it.

If you do this, it would be best to use a string to define the file name you want to kill, that way you can refer to that string in your error handler, accomodating both of the file names you have used.
0
 
Rey Obrero (Capricorn1)Commented:

the "Kill" will generate an error if
* the file does not exists  >> np with this since you have a code to test

* the file is locked ..

did you try my suggestions ?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I can an error "User-defined type not defined" on Dim fso As FileSystemObject when I compile.>
Did you load the Microsoft Scripting Runtime library in your VBE as a reference?, ...as I stated?
0
 
Dale FyeCommented:
you don't have anything directing your code to actually implement the error handler.

ON ERROR GOTO Err_DealerEmail
0
 
AronMcDAuthor Commented:
Capricorn,
Yes I put the doevents in and it ran fine...but I haven't been able to get the darned thing to error since I posted this question (without making any changes).  This morning it was erroring pretty consistently.  So I'm not sure what's going on.  

 Set objOutlook = Nothing
    
    'testing capricorn
    DoEvents
    
    'deleting files when finished
    'testing C: drive
    If Dir("C:\pdfReport1.pdf") <> "" Then
        Kill "C:\pdfReport1.pdf"
    End If

    If Dir("C:\pdfReport2.pdf") <> "" Then
        Kill "C:\pdfReport2.pdf"
    End If

Open in new window

0
 
AronMcDAuthor Commented:
I'm not sure what to do.  Should I leave this question open for a bit in case it errors again?  
0
 
Jeffrey CoachmanMIS LiasonCommented:
Did you load the MS Scripting Library, and try my code?
Yes or No?
0
 
Jeffrey CoachmanMIS LiasonCommented:
This works fine for me...

FWIW
0
 
Rey Obrero (Capricorn1)Commented:


AronMcD,

the other option is reverse the order of deleting the file as i have posted in
http:#a36488892

this process will surely eliminate  the possibility of the file being "locked" by the process of sending the file as attachment

0
 
AronMcDAuthor Commented:
I'm using doevents as capricorn suggested and also Jeff's MS Scripint Library code and it's working fine.  What's confusing to me is why I wasn't able to get the error with the same code I was using when I originally got the error.  Anyway, I'm going to assume that with the suggestions from cap and Jeff I won't have to worry about getting the error again, so I'll split the pts between you two.  Hopefully that's ok.  

Thanks to everyone for their help.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
My guess is that because this issue was "inconsistent" it had more to do with adding DoEvents than using another technique to delete the file.

I only posted the alternate delete technique, because I thought the the file was not ever being deleted.
0
 
Rey Obrero (Capricorn1)Commented:
<because I thought the the file was not ever being deleted.>

it was stated twice in the original post.  
<What's odd is that I don't always get the error.  One time it will work fine and the next time I'll get the error. >
0
 
Jeffrey CoachmanMIS LiasonCommented:
Sadly, I only looked at the question title...
:-(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now