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.

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.





(E-Mail Removed) wrote:
> 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.

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



"(E-Mail Removed)" wrote:

> 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.
>
>

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)





Michael H wrote:
> 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
>
>
>
> "(E-Mail Removed)" wrote:
>
> > 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.
> >
> >

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.


(E-Mail Removed) wrote:
> 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)
>
>
>
>
>
> Michael H wrote:
> > 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
> >
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > 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.
> > >
> > >

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 wrote:
> 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.
>
>
> (E-Mail Removed) wrote:
> > 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)
> >
> >
> >
> >
> >
> > Michael H wrote:
> > > 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
> > >
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > 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.
> > > >
> > > >

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.


dlow123 wrote:
> 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 wrote:
> > 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.
> >
> >
> > (E-Mail Removed) wrote:
> > > 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)
> > >
> > >
> > >
> > >
> > >
> > > Michael H wrote:
> > > > 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
> > > >
> > > >
> > > >
> > > > "(E-Mail Removed)" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > >

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?






dlow123 wrote:
> 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.
>
>
> dlow123 wrote:
> > 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 wrote:
> > > 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.
> > >
> > >
> > > (E-Mail Removed) wrote:
> > > > 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)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Michael H wrote:
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > > "(E-Mail Removed)" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > >