Question Deleting documents from a folder from with Acess using VB

[RosemaryJB]

Users can write & retrieve letters (WORD) from within the application I support.
My problem is that, when we remove contacts from the DB, the letters are left behind, ever increasing in number & giving us Data Protection problems. Deleting them manually is tedious & error prone.
The contact's ID is part of the document name generated by the application which is how we find them again.
I need to write some VB code which finds & deletes multiple letters automatically based on this ID.
Can anyone help?

 

[Scooterbug]

Off the top of me head, you should be able to shell out the dos command Del. As for deleting documents that dont have a record associated with them...here is how I would do it (I'm sure there are different ways). I am assuming that all the documents are stored in one folder.

First, I would get a list of the files in the directory. You can do this by opening up a command prompt, going to the directory with the files in it and typing in the following:

dir >c:\List.txt

this will generate a list of files located in that folder and dump them into a file called List.txt on your C drive. (You could narrow down the list further by using dir *.doc)

Import the file into a table. You might want to open it first and get rid of the informaiton at the top. From there, you can use that table to loop through the records and compare the ID in file name to the ID of the customers in your db.

Edit:

Here is some example code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sPath, sFileName, sCompareName, sDelCommand As String

Set db = CurrentDb
Set rs = db.OpenRecordset("List2")
sPath = "c:\apr09\"
sDelCommand = "c:\windows\system32\cmd /c del "
 
With rs
    .MoveFirst
   Do
    Do Until .EOF
    sFileName = ![Field5Name]
    sCompareName = Left(sFileName, Len(sFileName) - 7)
    sCompareName = Right(sCompareName, 2)
       If sCompareName = 20 Then
            sPath = sPath & ![Field5Name] & " /q"
            Shell (sDelCommand & sPath)
        End If
        .MoveNext
            If .EOF Then Exit Do
     Loop
    Loop Until .EOF
End With

I import addresses and billing information on a daily basis. So the list of files in the Apr09 folder are named Internet_mm-dd-yy.txt and Internet_addy_mm-dd-yy.txt. What this code does is goes through the list of file names and finds the ones who's dd is the 20th (I know, there is probably an easier way to find the 20 in the file name ). If it finds one, it executes a delete without prompt (hence the /q switch). Once you have all the documents that you want out, you can adapt the shell command to have it delete the respective files when you delete the customer record.

 

[RosemaryJB]

Very many thanks.
At the moment, I am drowning in work (I work for a charity & have just reduced my hours to 12 per week having reached my 65th birthday but the workload hasn't reduced along with my hours). When I have a quiet period I'll try out your suggeston.

 

[namliam]

Why not use the Access native Dir ??
Or use Access native Filesearch??

 

[DCrake]

I have just amended a sample demo that is posted here that allows you list files from a known path - in this demo it uses the application path - this can be changed in code to suit your needs. When you click on find files it will list all files found in that location with the filter and extension you have provided.

In your case you would enter

Filter 123*
Extension .Doc

This would only show all word documents found that begin with 123 - your client code. You can then select the documents that you want to delete by highlighting them then click on the "Delete Selected Files" button.

Give it a go and see if it works for you.

Rememer to un comment the Kill statement on the On-Click event of the command button.

David

 

[RosemaryJB]

Thank-you but I am afraid I didn't understand demo FindFiles.mdb at all.
I have tried running the macro with the correct path & *.doc but it kept refused saying I could only run it for text files. I was expecting some code? I didn't find any buttons or 'kill' statements. I don't know where the spreadsheet comes in.
Was the sample incomplete?
Office in chaos after a move so it may be a few days before I look at your reply.

 

[DCrake]

If you used the demo correctly it should be placed in a folder where the test documents are located. This can be changed in code by yourself.

Then in the filter box you could place some text that is contained in the name of the file to filter the names. This is optional.

Then in the extension textbox if you enter *.doc it will list only those types of files with that extension.

Once you have done that click the Find Files button to view in list box.

Next highlight the files you want to delete then click the Delete sected files button.

Just tested it and there are no errors. The actual Kill command has been commented out remove the comment and run.

David

 

[NigelShaw]

Hey DCrake

your demo always comes with an error for me-

"Run-time error '2455':"
"You entered an expression that has an invalid reference to the property FileSearch"

when you go into the code itself and debug, it fails on the FileSearch section.

is there a reference that is required?


regs

Nigel

 

[DCrake]

Have you checked your tools > referneces for missing objects.

Could be either MS Access Objects or ActiveX Data Objects

David

 

[NigelShaw]

Hi David,

had checked for missing references already. on re-checking, there are no ms access objects references available and the ActiveX Data Objects 2.1 is ticked.

i also have 2.0 through to 6.0 of the same reference which are currently unticked.

regs

Nigel

 

[DCrake]

My references are as follows. See attachment.

if you right click on the Application.FileSearch and select definition what happens?

David

 

[NigelShaw]

Hi David,

i think the problem is you are referencing "Microsoft Access 11.0 Object Library" where i dont have that available. i reference 12.0. other than this reference, i match yours.

when i right clicked and selected definition, it said the reference 'Office' was not referenced.

many thanks,


Nigel

 

[RosemaryJB]

Thanks for your help so far.
I am beginning to understand what is going on in the demo.
I came out of retirement to support a frighteningly sophisticated application which stretchs Access to the limits & invlolves vast amounts of code.
I hadn't programmed for donkey's years & never in VB so it has been a challenge. I am afraid adding this function, which we really need to meet Data Protection rules, is running close to the limits of my ability!
I am getting an error message - invalid use of null (94) associated with 'spath' for both import & delete..
I can't find the form the demo opens with.
I'm going to try scooterbugs code as well.
Rosemary

Private Sub CmdImport_Click()

'This code loops through all the items in the list box and uses the TransferText command
'to import them into a named table.
'If you already have an import spec then remember to change the name in the code below, if not
'then replace with ""
'The DoCmd line was generated by the macro macro 1
'and the Converted Macro module Macro1

Dim YourTextFileName As String

For x = 0 To Me.LstFoundFiles.ListCount - 1
sPath = Me.LstFoundFiles.Column(1)
YourTextFileName = sPath & Me.LstFoundFiles.ItemData(x)
Debug.Print YourTextFileName
'DoCmd.TransferText acImportDelim, "YourImportSpec", "YourTableName", YourTextFileName, False, ""

Next

End Sub

 

[DCrake]

This demo can be run totally seperate from your application and as such will not cuase any conflicts with it.

First save it in the folder where the docs are.

Next Click on the find files button.

Next click on the items in the list you want delete.

If there are no items in list then it can't find anything to delete.

The latest copy is out of reach today. If you stil have isses I will lok at it tomorrow.

David

 

[RosemaryJB]

From home - couldn't post a reply yesterday.

I hadn't realised you have to select the documents before clicking a button - silly me! - so now the demo is working as it should do.

I still can't find the form. It is not listed under Objects, not that I need it as forms are something I don't have problems with.

Using a mixture of your code & Scooterbug's, I can find & delete matched letters as we remove contacts from the DB. Just have to do the next stage which is to find the back-log of unmatched but I can see how to do that.

Thank-you both for your help. This Forum is wonderful. I have used it so much since I started this job where I am on my own if I get stuck & my manuals rarely answer my questions.

Rosemary.

 

[DCrake]

Glad to be of help and pleased you will continue to use this site.

David

 

[RosemaryJB]

I have got working code for many of the things I wanted to do (problem was more complex than I described) but am now trying to adapt the code from FindFiles.mdb to import the document list. The reason for this is to deal with contacts who have been deleted so I am trying to find unmatched letters.

FindFiles.mdb doesn't like the Irish! i.e. documents with a name like
"C12598-He-201749 AL8 7NX Mrs Sheila O'Connor.doc".

Any ideas? & thank-you for your help so far.

Rosemary

Reply from D Crake

Think you may find that this will suffice

DoCmd.RunSQL "INSERT INTO tblFoundFiles ( FilePath , FileName ) SELECT " & chr(34) & Folder & "\" & Chr(34) &" AS A, " & Chr(34) & fName & Chr(34) & " AS B;"

This can be found in the FindAllFilesInFolder() Sub

David

Thank-you. I got round it by using dos command > dir