More elegant code

  • Thread starter Thread starter bhammer
  • Start date Start date
B

bhammer

There must be a better way. This works, but seems too much. Anybody have any
elegant ideas?

tblBigListOfImages is to be appended to periodically with the records in
tblNewImages (a make-table resulting from cmdBrowse). But first, I need to
make sure that there are no duplicate FileNames.

To do this I have to use three queries and two new tables (messy).

So I have cmdCheckForDuplicates that:
1. Runs a make table query, qmtCopyBigListOfImages, then
2. Runs qappCombineOldAndNew to append all the records from tblNewImages to
the new tblOldAndNewImages, then
3. Runs qmtDuplicates to make another table that contains the common
Filenames in the tblCombineOldAndNew.
Whew!

Thoughts?
 
You've described "how" you are trying to do something, but "what" is not
clear.

What will you be able to do as a result of all of this (whatever "this"
turns out to be)? Please describe a bit more about the business need you
are trying to meet.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Where do the file names come from?
It would seem that this is the point to make sure there are no duplicates,
but you would need code to make things neat for the end user.
Why bother with two tables?
If you need to keep "old" and "new" for some reason a check box named
"NewImage" with a defeault of True works fine and all you have to do is run
an update query as needed to make them "old"
 
Jeff,

Thanks for the interest. It's an image database. One database per
(unrelated) Project. The user needs to be able to copy new image files (as
they periodically become available) to a destination folder containing the
images previously cataloged for this particular project. The copying is done
via a wizard I am creating. BrowseForSourceFolder creates the New Images
list. A Destination folder is browsed to to find the collection folder for
the project. The new images files will be added to it. A CopyImages function
does the file copy work. Another button will catalog the filenames into the
database Images table.

The problem to solve is this: It is possible that sometimes the image
filenames will be identical to those already in the database (and the
collection folder), so I need to have a button the check for duplicates prior
to copying the files and prior to appending the Images table. The result of
the check is simply a display of the duplicate filenames (or none, and emplty
list) to let the user have feedback. If duplicates exist, then the user is
directed to rename the offending files.

I have this wizard working already, but it seems clunky to me. It feels like
I'm missing a more elegant solution to the whole thing.
 
Mike,

See also my elaboration to Jeff's reply.

To your question, the filenames come from two places, the tblBigListOfImages
is already existing in the database and it lists all previously cataloged
images. The new filenames are in a make table that results from calling the
BrowseFolder function and CopyImage function. That's the only way I know how
to get the list of new files in the Source Folder. Then those new filenames
need to be compared the the existing old list. The only way I know how to do
this is to append the new filenames to a copy of the old filenames and check
for duplicates. The user simply needs to know whether or not there are
duplicate filenames to that he can continue with the copy new files button.
Can I put the results of the check dupes query directly into the listbox on
the wizard, without first making a table? That would eliminate one table. . .

-Brad
 
Not sure if it's applicable, but another approach might be to use a combobox
to list all (current) filenames. That way, the user will know right off if
the "new image file" name is already being used.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ooo, nice thought but the list of "old" images may be a few thousand long. .
..not practical. Typically the "new" list will be about 300 images files,
the"old" list will be a few thousand. . .Brad
 
Brad,

Are you checking for entire file names (including path)?

What is the table structure of tblBigListOfImages (include a sample record
or two).
What is the structure of the table that stores the results of BrowseFolder?

How large are these tables?

You one table contains full path, and the other doesn't, you could create a
function that strips the filename off of the fullname. Then join the two
tables, something like:

SELECT tblMakeTable.*,
IIF(isnull(BLOI.fieldname), "No", "Yes") as Duplicate
FROM tblMakeTable
LEFT JOIN tblBigListOfImages as BLOI
ON fnFileName(tblMakeTable.Filename) = fnFileName(BLOI.Filename)

This would give you all the fields in tblMakeTable and an additional field
(Duplicate)
which indicates whether the filename already exits in tblBigListOfImages

But I'm still not sure how you would use this.

Sometimes, I go by the old addage: If it works, don't fix it!
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Hi bhammer:

Why not append a Now() value to the end of each new image as you copy it
into the existing folder? Then you can skip all the checking for duplicates.

CW
 
You didn't give the table details, but maybe something like this works.

Insert tblBigListOfImages as B
(
Select *
From tblNewImages as N
Where Not Exists (
Select *
From tblBigListOfImages as B2
Where B2.fileName = N.fileName
)
)

The where clause will limit the selection to filenames in tblNewImages that
don't already exist in tblBigListOfImages.
 
Dale Fye said:
Brad,

Are you checking for entire file names (including path)?

What is the table structure of tblBigListOfImages (include a sample record
or two).
What is the structure of the table that stores the results of BrowseFolder?

How large are these tables?

You one table contains full path, and the other doesn't, you could create a
function that strips the filename off of the fullname. Then join the two
tables, something like:

SELECT tblMakeTable.*,
IIF(isnull(BLOI.fieldname), "No", "Yes") as Duplicate
FROM tblMakeTable
LEFT JOIN tblBigListOfImages as BLOI
ON fnFileName(tblMakeTable.Filename) = fnFileName(BLOI.Filename)

This would give you all the fields in tblMakeTable and an additional field
(Duplicate)
which indicates whether the filename already exits in tblBigListOfImages

But I'm still not sure how you would use this.

Sometimes, I go by the old addage: If it works, don't fix it!
Click to expand...

Dale,
Yes, it aint broke. . .but it aint pretty neither! The table has thousands
of records. Keep in mind I'm trying to both catalog the filename into the big
table AND filecopy the image file from its source folder into the destination
folder, which has the thousands of actual image files that are cataloged into
the database. They are all at the same path now, so where they came from (old
path) doesn't matter.

I may try something like Cheeze_Wiz suggests, append something to the
filename--even let the user type in a suffix or prefix . . .

-Brad
 
Cheese_whiz said:
Hi bhammer:

Why not append a Now() value to the end of each new image as you copy it
into the existing folder? Then you can skip all the checking for duplicates.

CW
Click to expand...

CW,

I like this idea, even put a textbox for the user to enter their own suffix
or prefix, but the danger of duplication would still lurk. With the date idea
it would be unique, but the filename should not get too out-of-hand since it
will have to be referenced on reports and such. But you have me thinking. . .

-Brad
 
I used Dir to do something similar.
I just didn't move duplicates but it would be easy to add a -something after
the file name to show it is a duplicate.
I used code rather than queries because I wanted to add the name of the file
to both a field that referenced it and a field that contained a name for the
picture.
C:\someFolder\FredSmith.jpg was stored as is and used to point to the
picture.
FredSmith (more likely 12234566) was added as the picture name.
The user could then leave it or make it meaningful.
 
Brad,

Why would you want to copy the file? You will just end up with multiple
copies of the same file all over your network. Why not just store a pointer
to the file. It would be significantly easier to just check to see whether
the table already contains a file name, and if not add it. I'd even go so
far as to have a centralized table that is stored in an mdb on the server,
that keeps track of all of the separate project databases that are using a
particular image.

Dale
 
Back
Top