Delete multiple files from a folder using an Access Database

[dlow123]

Good Morning All,

I am not sure if I am posting this in the right place, but please bear
with me.

I am trying to run a db query to quickly populate a database with
filenames from a folder on my hard drive. Then in turn I would like to
be able to delete files from the folder, based on my deletes in the
database.

I hope that this makes sense. I am currently using the following code
in the Visual Basic editor to populate my table with the necessary
data:

Sub GetGameFiles()
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\path\to\thefile\iwantadded\tothisdb\"
strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO gamenames (gamefilename) " & _
"VALUES (" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop

End Sub

I am currently running Access 2003. I also thought about trying this
with Excel 2003

If anyone can offer assistance it would be greatly appreciated.

Thank you.

Devon L.

 

[dbahooker]

if you used an Access Data Project-- which works GREAT against a free
version of SQL Server.. then you could simply have a delete trigger
that fired the statement via XP_CMDSHELL

mdb isn't a real database

but if it comes down to Excel vs MDB then use MDB.

whatever you do; dont try this in Excel there are hella security
problems with Excel.

 

[Guest]

Hi.

Is there any other reason you are placing the filenames into a table first?
Why not just delete the files within the Do While loop?

Just replace this:
strSQL = "INSERT INTO gamenames (gamefilename) " & _
"VALUES (" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

With this:
Kill strFolder & strFile

Or, if you need the filenames in a table for some additional reason, just
insert my line of code right before this one:
strFile = Dir()

-Michael

 

[dbahooker]

what's wrong with storing them in a database?

why try to change the guys requirements?

sure there is a risk of having 'out of date' information

using a tool like SQL Server instead of a crutch like MDB would make
this a lot easier.

you could even populate the list of files / folders through a simpe
recursive DOS command.

SQL beats VB any day of the week (for complexity, performance,
stability, etc)

 

[dlow123]

WOW!

I left this post this morning and 3 responses. I will try this right
now and post the result. This started because I was trying to STORE
file names in a database for easy sorting. I run a flash games website
that uses thousands of flash and image files. You can imagine that when
it comes time to find duplicates, delete files that I don't want in
batches, etc... it becomes a daunting task. Therefore I found this to
be an easy solution. The roundabout part of this all is I create the
table in Access and then export it to Excel for manipulation. Now I am
faced with a group of about 300 files that I need to delete from a
group of 2900. Hand picking those files is simply not an option.

So hopefully this works.

 

[dlow123]

Michael,

Your response would work perfect if I already had the files that I
needed to delete in a specific folder all by themselves, but then again
I would have no need for the database, I could just delete them all.
What I am trying to do now is take what's in the database and delete
the files with another module. Hopefully this makes sense.

Thank you for your assistance so far.

 

[dlow123]

Thanks for all the help,

I actually had to code this completely differently, but I was able to
delete the 300 files from that folder. Also this only works in the
debugger, when I try to run it as regular, it throws back a syntax
error. Lastly, whenever I tried incrementing using the following, I got
an error stating that the item was not found in the collection.

i = 1
Kill (strFolder & WorkRS1.fields(i))
i = i +1

Here was the code that I was able to run by in Access, using the VBE
and holding down F8 while it manually ran through a loop:

Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim strSQL As String
Dim strFolder As String
Dim i As String

Sub DelTestFiles()

Set WorkBase = OpenDatabase("C:\database location\databasename.MDB")
strSQL = "SELECT fieldname FROM tablename"
Set WorkRS1 = WorkBase.OpenRecordset(strSQL)

strFolder = ("C:\Directory\where\the files to be\deleted were stored\")
Do Until WorkRS1.EOF

Kill (strFolder & WorkRS1!fieldname & ".*")

WorkRS1.MoveNext
Loop


WorkRS1.Close
WorkBase.Close
End Sub

If anyone can fix this and tell me where I went wrong it would still be
appreciated.

Thanks again for all the help offered here.

 

[dbahooker]

a) dont ever use DAO
b) spit on old fogies that use it 10 years after it's been retired
c) spit on the microsoft salesman who tries to tell you it's coming
back

instead of all this

Set WorkBase = OpenDatabase("C:\database location\databasename.MDB")
strSQL = "SELECT fieldname FROM tablename"
Set WorkRS1 = WorkBase.OpenRecordset(strSQL)

strFolder = ("C:\Directory\where\the files to be\deleted were stored\")
Do Until WorkRS1.EOF

you can just do this

strFolder = ("C:\Directory\where\the files to be\deleted were stored\")
Dim rst as new adodb.recordset
rst.open "select fieldname from tablename", currentProject.connection
do until rst.eof


rst.movenext
loop


I just dig ado; it seems so much easier to use than DAO.

can you even bind a report or a form to a DAO recordset?