Solved

VBA MS Access Permission Denied when using Kill statement

Posted on 2011-09-06
22
1,178 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

13 Experts available now in Live!

Get 1:1 Help Now