VB and SQL

  • Thread starter Thread starter Gym
  • Start date Start date
G

Gym

Can I mix and match VB and SQL for file deletions. A
previous MVP suggested using "Kill" to delete my files,
but what I'd like to do is:

Kill table.field1 WHERE (table.field2 Like ("*"
& "delete" & "*"))
 
Gym,

No. The Kill statement pertains to a file. It is not 100% clear to me
what you are really trying to achieve here, but it looks like you need
to run an Update Query in this instance. The SQL for this query will
look something like this...
UPDATE MyTable SET Field1 = Null WHERE Field2 Like "*delete*"

Or if you are trying to remove the whole record, as against the entry in
Field1, a Delete Query, like this...
DELETE * FROM MyTable WHERE Field2 Like "*delete*"
 
Gym,

Sorry, I have no idea what you mean by that. Can you maybe give some
examples or further explanation/description?
 
table.field1 is the name of a JPG file that is linked to
my database and viewable in various forms. I would like
to be able to delete the file or a batch of files from a
form.

For example, I currently have over 5000 jpgs linked to
this database. table.field1 is merely a filename. I have
a form that views these jpg's based on the table.field1
filename.

Ultimately I'd like to delete the file corresponding to
table.field1 where the worl delete occurs in another
field of the same table.
 
I'm at a public computer with no ability to run Access, so
I can't test this, but I think it's what you're after.
Field1 should contain full path and file names.

With CurrentDb.OpenRecordSet( _
"SELECT field1 FROM table " & _
"WHERE field2 LIKE "*delete*")
WHILE NOT .EOF
KILL !Field1
.MoveNext
WEND
End With
 
Ah, ok...

The steps are:

Build the selection for the table that return the list files to
delete...

Processs this seleciton to delete the files.

The code to do this would look like:
'
dim rstRecrds as dao.RecordSet
dim strSql as string

strSql = "select field1 from tblFiles where field2 like '*delete*'"

set rstRecords = currentdb.OpenRecordSet(strSql)

do while rstRecords.EOF = false
kill rstRecords!Field1
rstRecords.MoveNext
loop
rstRecords.Close
set rstReocrds = nothing

If the field1 does ont have the whole path, then you can of couse add that
in the above loop....
 
I think that'll do it. I've tried almost everywhere to
insert the Pathname but can't figure it out. Thanks
 
Back
Top