Solved

Access 2007 - force deleting files in use by other applications.

Posted on 2011-02-28
18
787 Views
Last Modified: 2012-05-11
I've got a situation in an Access 2007 application where users can import files as attachments to different business objects.  When a user chooses to they can open that object and view it, which actually opens the file in whatever application is associated with it.  If the user then tries to delete that file through the Access application interface then they receive an error stating that the file cannot be deleted because it is already in use by another application.

Is there any way to force delete files through Access VBA regardless of the fact that they may be locked by other applications?

thanks
0
Comment
Question by:JosephEricDavis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 84 total points
ID: 34999924
You can use freeware utilities from command line:
unlocker (http://download.cnet.com/Unlocker/3000-2248_4-10493998.html)
or FileAssasin (http://www.malwarebytes.org/fileassassin.php)

unlocker FileName /D /S
Fileassassine /delete /silent /FileName

0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35000078
Not sure if that will be a workable option.  This is a product for a business client.  I'm not sure I can deliver them the access application and tell them it is dependent on them installing FileAssasin on their computer in order to work correctly.
0
 
LVL 75
ID: 35000187
I can vouch for Unlocker in most cases.  Easy to install/use ... works in most instances.
mx
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35000253
Try trapping the error and simply ask the user, via a message box, to close image in the associated program.
(Or at a more extreme level, also close the Associated Program directly...)

Remember the File Locking error exists for a reason.  If you use "Brute Force" to delete an open (Locked) file, the system may be left in an unstable state.

;-)

JeffCoachman
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35000310
Ok, so I guess what I'm mainly looking for is logical ways to sidestep issues like this.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 83 total points
ID: 35000355
The is also the Kill function:

from Help

"Kill pathname

The required pathname argument is a string expression that specifies one or more file names to be deleted. The pathname may include the directory or folder, and the drive."
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35000363
Kill does not allow deletion of locked files.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35000574
I don't know the specifics of your interface, but if you are using the standard Access "Manage Attachments" dialog box, you may be out of luck avoiding this situation. (as there is no specific error number to trap)

Remember, the message should be clear enough that most users will understand what needs to be done.

Just FYI, just for fun, see here:
http://www.mvps.org/access/api/api0025.htm
(But again, you would somehow have to trap the message...)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35000635
<Remember, the message should be clear enough that most users will understand what needs to be done.>

Meaning: ...the default message that is presented when you have the image opened from the "Manage Attachments" dialog box,... and you try to click the "Remove" button...

This message is generally clear enough so that most users should know what the issue is, and know what to do.

Screenshot attached

JeffCoachman
untitled.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35000671
Just so we are clear...

Are you trying to "Remove" the attachment, or "Delete" the file from a folder?
This is why in the Manage Attachments' dialog box, the wording is "Remove" (not delete)
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35000786
That is a solution that I've been toying with.  When I add an attachment I not only copy the file into a common attachments repository (directory structure) But I also insert data into my database to tell it the path to the attachment in the repository and what business objects it is linked to.  So what I've been pondering about is just removing the records from the database that tie the file to the business object.

However, I don't want the attachment repository directory to get bloated with files that no longer have anything to do with the application.  So I would like to delete them at some point.

So my data structure looks like this... I have a document table that has a documentID and a path to the file attachment.  Then I have a contracts table which holds the contracts business objects.  Then I have ContractDocument table that holds records having a ContractID and a DocumentID so that a contract can have multiple attachments.  Incidentally there are other business objects that would also have attachments which will have a relational table (IE ExpenditureDocument, to tie attachments from the document table to expenditure items).

So on delete, if I were simple to delete the record from the relational table then my application would no longer associate the attachment with the business object.  There would still however be a record in the document table.  Then I could at some point, like when the application first starts, run a sql statement to delete all the records in the document table that are not associated with any business object.

How does this sound?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35000905
<When I add an attachment I not only copy the file into a common attachments repository (directory structure) But I also insert data into my database to tell it the path to the attachment in the repository and what business objects it is linked to.>
??
So this is NOT a standard Access 2007-2010 "Attachment" object (Datatype)?

This is your own "Custom" system.

This was not clear from your original post...

Why "open" it image at all?
Why not just view the image in a form?
    me.YourImageControl.Picture= "C:\Images\2011\SomeImage.jpg"
This way you should be able to delete the image at will.

...or am I missing something?

Jeff
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35001520
The user can attach an image, but can also attach any other file type.  .pdf, .doc, .xls, whatever.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35003902
You did not really answer my question...
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35007683
Sorry...
No, the attachments are not being stored inside the Access data file.  They are being stored in a directory that exists along side the access file.  The path to the file is being stored in the database, but not the file itself.

Is that what you were wondering?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35009505
Thanks for the clarifications.

Then the same rules are in effect.

   In a nutshell, You cannot delete a file if it is opened by another app.

You can play around with the code in the link to close the external app.
And, yes, ...there are utilities that will "Nuke" the file, but again, this may leave your system in an unstable state.
(besides, who wants to install one more app?)
;-)

Jeff
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 35017246
So in the end what I did was created a process that would attempt to delete the file.  If it failed I would catch the error and hide it from the user and make a note that the file still had need to be deleted.  I also created a process when the application starts that it will attempt to clean up all the files that should have already been deleted.  This is a good solution for me.

Thanks for all those who participated in this question.  I'll distribute the points among you as best I can.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35018202
You could have also given yourself some credit as well, being that you posted the actual fix you used.
;-)

You can click the "Request Attention" link and ask for help if you want to also designate your post above as a solution as well.

;-)

Jeff
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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