Solved

VBA MS Access Permission Denied when using Kill statement

Posted on 2011-09-06
22
1,290 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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