Delete jpg file based on Access field contents

[tstew]

Hello,

What would be the best way to have access delete a bunch of jpg's?

I have two tables in mdb format, the main table has 36000 records. When I am
done with a record, I move it to a Deleted Record table (using a query and a
macro). There are about 1300 of the deleted records that have an associated
jpg file. The file size is around 100k, so I have around 130 meg of files
cluttering up my laptop. I will never look at the jpg's again. I know it's
not a lot of space, but why not have some code get rid of them?

So, should I use a button on a regularly accessed Form, or put code in the
query that moves the records to the new Table? Also, any pointers to code
would be greatly appreciated.

Thanks,
Mark

 

[Douglas J. Steele]

<picky>
You might want to check that the file actually exists before trying to
delete it.

Do Until rsPix.EOF
If Len(Dir(rsPix!PathToJPG)) > 0 Then
Kill rsPix!PathToJPG
rsPix.MoveNext
End If
Loop
</picky>

 

[ChrisO]

<picky>

Do Until rsPix.EOF
If Len(Dir(rsPix!PathToJPG)) > 0 Then
Kill rsPix!PathToJPG
rsPix.MoveNext
End If
Loop

Why: -
If Len(Dir(rsPix!PathToJPG)) > 0 Then
when: -
If Len(Dir(rsPix!PathToJPG)) Then
does the same thing.
Len(Dir(rsPix!PathToJPG))
returns 0 or a number greater than 0.
0 is equal to False and anything other than 0 is true.

or

why not simply
On Error Resume Next
since, if the file doesn’t exist at that location then there is no need to
Kill it.
and
rsPix!PathToJPG
might evaluate to Null which raises a type mismatch error on Dir(Null)

</picky>

 

[Douglas J. Steele]

There was a bug in previous versions of Access where you could run into
problems if you didn't actually have a comparison in an If statement, so
it's a coding convention I always follow. And while you're correct that

If Len(Dir(rsPix!PathToJPG)) > 0 Then

and

If Len(Dir(rsPix!PathToJPG)) Then

are syntactically the same, the first is more correct. (It also handles the
extremely unlikely possibility that VBA could someday change the fact that
it treats all non-zero values as True to only accepting -1 as True)

And while On Error Resume Next would indeed work, it would mean that other
legitimate errors would not be caught.

I'll grant that the possibility of a Null value for PathToJPG would cause an
error. I should have instantiated rsPix to only return non-Null values:

set rsPix = DbEngine(0)(0).OpenRecordset( _
"SELECT PathToJPG FROM tblDeletedRecords " & _
"WHERE PathToJPG IS NOT NULL", dbOpenForwardOnly)

(That's also a better practice, since it brings back a smaller recordset)

 

[ChrisO]

Well, we could sit here all day re-writing code but one thing I would not do
is use dbOpenForwardOnly.

dbOpenForwardOnly requires a reference to DAO so I would use: -


Option Explicit
Option Compare Text


Public Const conOpenForwardOnly As Long = 8


Sub TestIt()

With CurrentDb.OpenRecordset(" SELECT PathToJPG FROM tblDeletedRecords"
& _
" WHERE PathToJPG IS NOT NULL",
conOpenForwardOnly)
On Error Resume Next

Do Until .EOF
Kill !PathToJPG
.MoveNext
Loop

.Close
End With

End Sub

And in that loop perhaps set !PathToJPG = Null to reduce the Query hit.


But at this stage I don’t know why ‘deleted’ records are being moved rather
than just marked as being not available. Or, if ‘deleted’ is the correct word
then just delete the records.

So, until more information is available, I’m just guessing.

Regards,
Chris.

 

[Douglas J. Steele]

Well, we could sit here all day re-writing code but one thing I would not
do
is use dbOpenForwardOnly.

dbOpenForwardOnly requires a reference to DAO so I would use: -

And I would never dream of using Access without a reference to DAO.

 

[ChrisO]

And I would never dream of using Access without a reference to DAO.<<

I’m not sure what that means, Doug, so perhaps you might like to explain
that point a little further.

From my perspective, I try to remove all references (except the two that
can’t be removed) and that includes the reference to DAO. That has worked
well for me over the last 9 or 10 years so I’m a little perplexed at your
statement that you ‘would never dream about it’.

So, do you mean that you never thought about it, and therefore would not
dream about it? Or do you mean you have thought about it, decided not to do
it, and have forgotten that you dreamed about it and made the assumption that
you never dreamed about it?

From a humorous point of view, I don’t know if I’ve ever dreamt about it
but, then again, my memory of my dreams is probably flawed. ;-)

Regards,
Chris.

 

[Douglas J. Steele]

Since the point of Access applications is almost always to deal with data,
and since DAO is the preferred approach when the back-end is a Jet (or ACE)
database, I would never create an Access front-end that didn't have a
reference set to DAO (or ACE).

If my back-end is in another DBMS such as SQL Server, I might also set a
reference to ADO in addition to the reference to DAO.

 

[Stuart McCall]

Since the point of Access applications is almost always to deal with data,

and since DAO is the preferred approach when the back-end is a Jet (or
ACE) database, I would never create an Access front-end that didn't have a
reference set to DAO (or ACE)

Not to mention that Access itself uses DAO recordsets internally (which is
as good a recommendation as any), so the overhead of a reference (which is
what I think Chris is concerned about) will be negligible.

I wouldn't like to do without DAO, referenced or otherwise. Preferably
referenced though, so I can use all that intellisense. Since it's included
in all OS's these days, there isn't any trouble with references when
distributing, either.

 

[ChrisO]

I’m not suggesting not using DAO what I am suggesting is not having a
reference to it.
The following link should explain it better.

http://www.utteraccess.com/forums/s...20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=

I’m also well aware of the immediate response most long-term programmers
have to it but that response tends to fade over time.

Regards,
Chris.

 

[Stuart McCall]

I'm not suggesting not using DAO what I am suggesting is not having a
reference to it.
The following link should explain it better.

http://www.utteraccess.com/forums/s...20036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=

That's fine if you're just accessing the currently loaded db, but not for
external db work.

I'm not suggesting not using DAO either, just that the presumed reference
problems don't exist for this lib so you might as well make use of those
handy drop-downs and pre-declared constants etc.

I'm also well aware of the immediate response most long-term programmers
have to it

I wonder why that would be?

but that response tends to fade over time.

Lacking hard evidence to back up that claim, yours is just another opinion.

Happy new year!

 

[ChrisO]

That's fine if you're just accessing the currently loaded db, but not for
external db work.<<

Well, as I have already said, I am aware of most of the immediate responses
posted by long-term programmers and that’s one of them.

That response amounts to; let’s see if we can break the principle somewhere
and that should prove that it shouldn’t be used anywhere. What it amounts to
is a failure when applying ‘reductio ad absurdum’ as a principle in that the
principal is extrapolated but the logic is not followed. In other words, the
test has become flawed during the extrapolation.

Here is where the extrapolation is flawed: -
I mentioned the term ‘long-term programmers’ because ‘long-term programmers’
build re-usable code libraries. Those code libraries should contain re-usable
code down to the procedure level. Each procedure should be as independent as
it can be and not rely on outside influences.

Most outside influences are handled by late binding because late binding
does not require a reference. If we late bind other references then why not
late bind DAO as well?

It’s at the procedure level that late binding occurs, not at the project
level. A late bound procedure can function equally well in a project that,
over all, might require a reference. But the late bound procedure does not
contribute to the requirement for that project to have that reference.

The application of ‘reductio ad absurdum’ was flawed in this case because
the logic broke when trying to move the argument from within the re-usable
procedure to areas where both the procedure, and the logic, does not apply.

I’ll give an example: -
If you read the link I posted then copy/paste Public Sub Case1() into a new
Access 2K database, it should fail. Now copy/paste Public Sub Case2() into
the same database, it should work.

Why does Public Sub Case1() fail and Public Sub Case2() work?
It’s because Access 2K did not have a reference set to DAO but it does have
a hidden reference to DAO.
From the A2K help file: -
Note In Microsoft Access the CurrentDb method establishes a hidden
reference to the Microsoft DAO 3.6 Object Library in a Microsoft Access
database (.mdb).

That ‘hidden reference’ works all the way to A2K7 and it is no way a ‘slight
of hand’ because it was put there by Microsoft.

Next question is; how long will it work into the future?
For that answer you will have to ask Microsoft, but don’t expect a reply. I
think it will be there as long as DAO is there and if DAO is removed then the
whole argument becomes a moot point.

If you have an individual procedure that must use a reference to DAO, and
can’t be converted to late bound, then fine just use it. But why have a
procedure that requires a reference if that procedure can be written in a way
that does not require one? If you write re-usable code for a code library why
compromise that procedure with external references?

A good re-usable procedure should be unto itself.

 

[ChrisO]

Please excuse me, Stuart, because I failed to answer one of your questions,
namely; “I wonder why that would be?â€

That is a very difficult question indeed and one that I would prefer not to
answer in full, or at least to the best of my ability however good that may
be.

However, my answer would centre on what might be called ‘web ego’. It’s
about protecting perceived status, which largely has been earned, but removes
from the recipient of such status the ability to think in a way that may
contradict what they have said in the past to achieve such status.

I would not like your question, or my answer, to get in the way of a
technical reply so may I suggest we stick to the technical problems.

If you have a technical question, point or objection I would like to hear it
and perhaps I may be able to help or at least clarify my position.

 

[Stuart McCall]

Please excuse me, Stuart, because I failed to answer one of your
questions,
namely; "I wonder why that would be?"

That is a very difficult question indeed and one that I would prefer not
to
answer in full, or at least to the best of my ability however good that
may
be.

However, my answer would centre on what might be called 'web ego'. It's
about protecting perceived status, which largely has been earned, but
removes
from the recipient of such status the ability to think in a way that may
contradict what they have said in the past to achieve such status.

I would not like your question, or my answer, to get in the way of a
technical reply so may I suggest we stick to the technical problems.

If you have a technical question, point or objection I would like to hear
it
and perhaps I may be able to help or at least clarify my position.

Not interested enough to continue this. Take it up with someone who cares.

 

[David W. Fenton]

Iƒ Tm not suggesting not using DAO what I am suggesting is not
having a reference to it.
The following link should explain it better.

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number
=1686472&Zf=f48&Zw=&Zg=0&Zl=a&Main=1686472&Search=true&where=&Zu=20
036&Zd=l&Zn=&Zt=1f&Zs=b&Zy=#Post1686472&Zp=

Iƒ Tm also well aware of the immediate response most long-term
programmers have to it but that response tends to fade over time.

Why would you want to use DAO basically with late binding? You lose
all the data types, though you can still create DAO objects through
CurrentDB (as the example you cite shows). When you lose the data
types you also lost the Intellisense that makes them easy to work
with.

It's also the case that recordsets created that way don't have the
same scope, so you can't always do it without a variable. In that
case you'd have to use an Object variable, and this will be slower.

I think it's insane to remove the DAO reference and still use DAO.
You can do it, but why I simply cannot imagine.

 

[David W. Fenton]

That's fine if you're just accessing the currently loaded db, but
not for external db work.

Actually, you don't need the DAO reference to work with databases
other than CurrentDB, since there's a top-level Application.DBEngine
object that exposes all the functionality you need from there.

But again, I can't fathom why one would want to make one's coding so
difficult. I see no benefit to it at all.

 

[David W. Fenton]

If we late bind other references then why not
late bind DAO as well?

Your example is absurd. This code won't be running outside of
Access, so there's no point in coding around the idea that a
component won't be available in Access when that component is
fundamental to Access's operation.

Secondly, the A2K example is ludicrous -- that was a remarkably
stupid design error on Microsoft's part and they changed it.

Third, code portability is not a valid goal except for the code that
is intended to be portable. The DAO reference is hardly a block to
portability significant enough to abandon al the benefits in terms
of ease-of-use and performance that the DAO reference provides.

 

[ChrisO]

David.

I think I answered you points in the post 5 up from yours but I’m really not
sure.

So let’s have another go at it and let’s also try to remove emotive words.

That might be correct under some certain circumstances; however the
direction of logic flow might be wrong. As with other forms of late binding
it can be written as early binding and then move to late binding when done.
This would mean we still use DAO and then remove the DAO reference which is
the reverse of what is quoted.

The ‘why’ of doing so I think I have already posted. We late bind in order
to try and preserve portability. DAO was not referenced in Access 2000 so any
code that used variables declared as DAO.SomethingOrOther would fail because
of the lack of a DAO reference in Tool>References. Yes it’s easy to fix, but
why have to fix something that needed not to be broken in the first place?

So where possible at the procedure level, we can write it in such a way that
it works no matter where it’s used. This to means the code is better because
it’s more portable but there is more to it than just that.

From the link I provided: -

Public Sub Case2()

With CurrentDb.OpenRecordset("tblMyTable", 2)
Do Until .EOF
MsgBox .Fields("SomeText")
.MoveNext
Loop
.Close
End With

End Sub

That should run in any version of Access from 97 and up.
So the question becomes; why compromise that portability by declaring a
variable that requires as DAO reference?

The other point, and really the reason I started writing it that way a lot
of years ago, is that we circumvent the old hackney argument about having to
set variables to nothing when finished with them. In the above case the
argument is circumvented because there is no variable set to something and,
therefore, no requirement to set it to nothing.

So, if I’m allowed to be a little humours here, I’m not being argumentative,
I’m being non- argumentative. ;-)

-----------------

In any case, this but a small part of something much larger.
The larger thing is about writing code in such a way that tries to work
under all circumstances, and that‘s not an easy task.


We, apart from some people who happen to live in a country which runs the
default regional settings, need to watch for date settings in SQL. And yes,
it’s been done to death (and appropriately so by people like Allen Browne and
others) yet it is still often ignored. To me it seems the most often asked
question by Access people outside the US but all they really have to do is
throw Allen’s function into the database and use it. But it also happens to
be important for people inside the US because the code they write might work,
if no one has changed their regional settings, but their code could break if
moved to another country. But here again the procedure is the same. Try not
to allow a code procedure to be dependant on outside influences.


Most of us use a period as the decimal separator and it generally works by
default. But again it’s based on regional settings and a VBA SQL string build
could fail under German (Germany) regional settings where the default is a
comma. The code could fail because the procedure has become dependant on
outside influences.


Some people use the word Detail when referring to a section on a Form or
Report. But Detail is just the default name in the English version of Access
and it can be changed. In the Norwegian version of Access I don’t think by
keyboard would allow me to even type the default name of the detail section
and it could change. The word Detail could fail, almost impossible to test,
but the procedure has become dependant on outside influences.


If a procedure receives a Boolean argument set to -1 then the procedure will
insert that Boolean argument as True in a VBA SQL string build. That works by
default in the English version of Access. In the Dutch version of Access the
word Waar is inserted and the SQL string blows. If all you are using is an
English version of Access you can’t see that happening and you can’t de-bug
it. The procedure has become dependant on outside influences.


Some charts will work fine if we pay attention to Dates including most
regional settings. Those same charts can fail with Afrikaans. I don’t know
why and I couldn’t fix it using Date/Time fields. The fix was to stop using
Date/Time fields and use straight doubles. Somehow the procedures had become
dependant on an outside influence.



The point being, try not to write a procedure that is dependant on an
outside influence and not even make the assumption that the reference to DAO
will be present.

 

[David W. Fenton]

[quoting me:]

That might be correct under some certain circumstances; however
the direction of logic flow might be wrong. As with other forms of
late binding it can be written as early binding and then move to
late binding when done. This would mean we still use DAO and then
remove the DAO reference which is the reverse of what is quoted.

???

Not "use" as in "writing code" but "use" as in "at runtime the code
executes using DAO."

Late binding is slower than early binder, both in initializing and
in traversing properties/methods. Now, it may not be enough slower
to matter, but the lack of any justification for late binding DAO in
the first place makes this performance sacrifice useless. And it
*could* make a difference in particular types of scenarios.

The ‘why’ of doing so I think I have already posted. We late
bind in order to try and preserve portability.

And there's absolutely no portability issue in Access.

DAO was not referenced in Access 2000 so any
code that used variables declared as DAO.SomethingOrOther would
fail because of the lack of a DAO reference in Tool>References.
Yes it’s easy to fix, but why have to fix something that needed
not to be broken in the first place?

It should be fixed because any A2K app that lacks the DAO reference
is written by someone who doesn't know what they are doing. Either
they didn't use DAO in code before that point or they are using ADO.
In either case, their app is wrongly-designed for what they are
trying to do, precisely because they didn't know they need to
rectify Microsoft's design error in not setting the default DAO
reference.

So where possible at the procedure level, we can write it in such
a way that it works no matter where it’s used. This to means the
code is better because it’s more portable but there is more to
it than just that.

I disagree that this is a priority that should drive the design of
code.

[]

That should run in any version of Access from 97 and up.
So the question becomes; why compromise that portability by
declaring a variable that requires as DAO reference?

Because it's better design. It will be more understandable by more
programmers. It will execute faster. It will compile more reliably.
It will be more easily maintained. It will use memory more
efficiently.

The other point, and really the reason I started writing it that
way a lot of years ago, is that we circumvent the old hackney
argument about having to set variables to nothing when finished
with them. In the above case the argument is circumvented because
there is no variable set to something and, therefore, no
requirement to set it to nothing.

But your example works in only one type of circumstance. There are
numerous cases where it does *not* work. I'm all for using With
blocks where they make sense, but as your method of avoiding DAO
early binding, it seems misguided.

Indeed, if I were going with your approach, I'd still cache a
reference to CurrentDB as I do with normal early binding of DAO,
rather than needing to set up the With block all the time.

Also, to me, the chief loss of late binding is strongly typed
variables for your workspaces and databases and recordsets and
fields. This makes the code harder to debug and less efficient when
it runs. Yes, of course, you could do your programming with early
binding and switch to late binding for production, but why bother?
There is no gain whatsoever to doing so except for the silly
portability argument you're making.

So, if I’m allowed to be a little humours here, I’m not being
argumentative, I’m being non- argumentative. ;-)

I think your argument is insanely unrealistic and misguided and will
say so at length in order to try to prevent your leading someone
down the garden path.

In any case, this but a small part of something much larger.
The larger thing is about writing code in such a way that tries to
work under all circumstances, and that‘s not an easy task.

I think this is an overblown artifical requirement that you're
applying to a coding environment that is very stable and entirely
reliable with early binding (and the one case in which it is not is
fixable in 1 second -- anyone who doesn't know how to do that
shouldn't be copying VBA code from elsewhere into their current
app).

[completely irrelevant examples of SQL compatibility deleted]

The point being, try not to write a procedure that is dependant on
an outside influence and not even make the assumption that the
reference to DAO will be present.

You're comparing a 747 to a gnat.

There is absolutely no significant issue with portability for code
writting using early binding in DAO. The only minor issue you've
adduced (the A2000 issue) is so easily rectified that I just don't
understand why you're recommending that someone spend so much more
time coding with late binding (or wasting the time of switching from
early to late binding for production) just to save the time of
adding the DAO reference in any A2000-created MDB that lacks it.

Your priorities are just completely upside-down.