Solved

VBA MS Access Permission Denied when using Kill statement

Posted on 2011-09-06
22
1,204 Views
Last Modified: 2012-05-12
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
Comment
Question by:AronMcD
  • 8
  • 6
  • 4
  • +2
22 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36488834
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
 

Author Comment

by:AronMcD
ID: 36488869
I have full permissions.  I'll try another folder to test.  
0
 
LVL 21
ID: 36488891
Sounds like the emailing process still have the file lock.

What VBA code are you sing to send the email?

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 36488892
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
 

Author Comment

by:AronMcD
ID: 36488998
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
 

Author Comment

by:AronMcD
ID: 36489028
Correction in code I posted
The Kill statement should be:
Kill "C:\pdfReport1.pdf"

Rather than:
 Kill strFilePath & "pdfReport1.pdf"
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 36489068
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
 

Author Comment

by:AronMcD
ID: 36489070
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
 

Author Comment

by:AronMcD
ID: 36489100
Jeff,
I can an error "User-defined type not defined" on Dim fso As FileSystemObject when I compile.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36489126
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36489133

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489199
<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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36489201
you don't have anything directing your code to actually implement the error handler.

ON ERROR GOTO Err_DealerEmail
0
 

Author Comment

by:AronMcD
ID: 36489202
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
 

Author Comment

by:AronMcD
ID: 36489209
I'm not sure what to do.  Should I leave this question open for a bit in case it errors again?  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489239
Did you load the MS Scripting Library, and try my code?
Yes or No?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489242
This works fine for me...

FWIW
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36489312


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
 

Author Comment

by:AronMcD
ID: 36489313
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36492893
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36492903
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36493048
Sadly, I only looked at the question title...
:-(
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now