Solved

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

Posted on 2011-02-28
18
784 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
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 39

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
 
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 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

19 Experts available now in Live!

Get 1:1 Help Now