Solved

Bulk Image Database Update Tool

Posted on 2015-02-10
22
176 Views
Last Modified: 2016-02-11
I have a product catalog in SQL Server.
One supplier of products has changed their logo and product labels and I need to update 300 products.
I have VB.Net and MS Access at my disposal and know how to write code.
I have a folder full of all the new images for the products.

Here is what I would like to do - or to be given suitable alternatives.

I would like a window with 2 sides. The left side is a list of all products. The right side are thumbnails of the images. Then you click and drag an image from the right on top of a name on the left. I would like it to update the image path & name for that product and remove the image from the right hand side.

This way you could drag and drop your way through them all.

Any ideas as to how I might accomplish this?

Thanks in advance!
0
Comment
Question by:GenesisTech
  • 10
  • 7
  • 3
  • +1
22 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40602138
Nope.
Not without a lot of pain.
But...
On the left side a couple of cascading dropdowns and a listbox
On the right side, the bazillion images.
Double-click the image and image path gets updated as do both sides of the form.
That's entirely doable in MS Access
Drag-and-drop?
For a one-off project?
That's a hell of a lot more work than it needs to be.

Better yet, a form with two subforms
The left subform has the product names and existing images
The right subform has the new images and paths
A command button in between
The command button updates the path of the left subform's image and deletes the record from the right subfrom and requeries it.
0
 

Author Comment

by:GenesisTech
ID: 40602167
Nick67,

Thanks for posting. First thing, it is not a one-off project. This happens with all of our suppliers and we currently have 8,500 products and growing. If I created the tool, it would be useful for years (I just left all of that out of the original question to not muddy the waters).

In your examples, what object or control would I use to list/display all of the images on the right hand side so that I could double click and obtain the path/name?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40602221
I went with Option B
Quick & Dirty
Put the Images folder in c:\temp
Full Path c:\temp\images\updater.mdb
Look at frmUpdater

Now, this was built in Access 2003
That version does NOT have support for binding an image control to a path
Access 2010+ does, so you can actually move the image control into the form detail and see all the images at once
The controlsource needs to be the path for that to work

Before then, you had to be content with a control in the header that gets updated in the Current event.
Which is what I built for the demo.
Have a look.
Images.zip
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40602989
This would be better suited to VB.NET, I would think, as .NET has a much greater breadth of image handling methods.

I'd start with a single form with a Split container, with a Listbox/Listview on the leftside, and a ListView setup for Images on the right (i.e. add an Image list, and load that image list from your disc folder contents). Drag-Drop is inherent in .NET, so you can drag an image and "drop" it on the item you want to associated, and then handle the DragDrop event to run code to manage the association.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40603369
<No Points wanted>
I agree with both Experts above.

Nick has given you a great starting point for doing this in Access

How practical/robust this will end up being will depend, in large part, on your skill level with programming...
For example, Access has no direct support for Drag and drop.
There is a third party Drag an Drop utility for Access though...:
http://www.peterssoftware.com/dd.htm
...Hence Scott's suggestion of moving to .net.

JeffCoachman
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40603567
<grin>
'...a great starting point...'!?!
I post a working sample that all you'd have to do is link in the products table, and link in a .csv file created in the command window with
dir /o/b/s >dir.xls
switch the record source of the two subforms, and get to work and you call that
... a great starting point...
LOL

It's stuff like this that Access is indispensable for.
It was about a half an hour to get something up and working from scratch that would do the job.

Nick67
0
 
LVL 84
ID: 40603993
But ...

You can't drag-drop between the two, and it's sort of ... clumsy. While it's a good start, it's certainly not a ready-to-run application that gives the OP what they're looking for. It's a good start, as Jeff posted.

But you won't be able to D-n-D with it, which is (IMO) an invaluable asset for stuff like this, and something end users today demand.

Which was the reason I mention that .NET, with built in support for images and DnD, would probably be a better choice.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40604062
No disagreement there :)
Hence
Nope.
 Not without a lot of pain.

And in terms of pain, while you can knock it together in .Net, Access isn't know as a premier RAD tool for small deployments for nothin'

But if you have Access 2010+ available, the image control can be bound to the .Picture property, and then can be given a double-click event, obviating the need for the control button.
Select in the left subform, double-click in the right subform
Rinse and repeat.

Heck, you can go one further with the VBA and throw on a button for a FileDialog, aim at a folder, and generate the paths for the new images with Dir() or FileSystemObject.
And if the images need to go from a staging area to a final locale, you can get that on the fly, too.
Throw a column in the products table for DateImageUpdated and update that as well as the path, and you can remove the images from the left subform on the fly, too.

And no disrespect intended, but if you have to ask the question, then chances are very good that it'll take you ten times longer to code the same functionality with more panache in .Net than it'll take to code something very functional without drag-and-drop in Access.

If you didn't have to ask the question, it might be a fairer fight in terms of effort expended.
Just sayin' and no disrespect intended.
There's a fair bit of heavy lifting involved with getting .Net going, but the end results can scale far beyond Access.
Still, this sounds like a one-user-at-a-time tool.

Much depends upon the existing skillset of the Asker.
If you're coming from the ASP.Net direction and Access isn't really your thing, then the shoe could be on the other foot.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40604232
@Nick
<grin>
'...a great starting point...'!?!
I post a working sample that all you'd have to do is link in the products table, and link in a .csv file created in the command window with
dir /o/b/s >dir.xls
switch the record source of the two subforms, and get to work and you call that
... a great starting point...
LOL
Not examining your file fully, I was just making that presumption based on none of use being crystal clear on the exact specs of the desired system..

There are many times that I have posted what I believed to be "exact" solutions, ...only to have it rejected by the OP.
:-O

...No offense was meant or intended.

JeffCoachman
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40604249
<grin>
None was taken.
It made me laugh.
' There are many times that I have posted what I believed to be "exact" solutions, ...only to have it rejected by the OP.'

Been there done that.
Sometimes what you offer isn't a match to the skillset/proclivities of the OP.
First and foremost as the Asker, you've got to be able to understand and maintain what you go with.
And that sometimes isn't always the best solution that was on the table.
That's life

Nick67
0
 
LVL 84
ID: 40604519
Access isn't know as a premier RAD tool for small deployments for nothin'
Same could be said for .NET, especially for those who "know how to write code" (as the author indicated). All too often we see someone who is try to force Access to do something it doesn't do well (like manage images, and do DnD). Granted you can get it to work, but in many cases you end up spending a HUGE amount of time working around the limitations. In cases like that, you're often better investing your time in knocking together a VB.NET application (especially given some of the templates and samples floating around).

Not to say you can't do this in access, but to make the inference that it's the better choice is, in my opinion, wrong in this case. And don't get me wrong - I love Access, and have made a very good living over the past few decades with. It's just that, early on, I also learned that not everything should be crammed into Access ...
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 26

Expert Comment

by:Nick67
ID: 40604622
I know you have facility with both tools.
For 300 image paths in a SQL Server table that needed updating perhaps once a month, and only you needed to use the tool, which would you build it in?

Access doesn't do drag-and-drop, and anytime someone wants that they get told to hold the phone, because it ain't happening.  But Access does do image management very, very nicely if you build up the skillset for it.  The WIA library exposes almost everything you could want short of a full-blown image editor.
For fun this summer, I had Access trigger a webcam every thirty seconds, facing north from my house, for ~30 hours on the longest day of the year.  I then built a form to roll through the images 30 per second.  The resulting time-lapse was very neat to watch.  I can get 400+ 3" x 2" digital picture images to display on a report, but that took a fair bit of learning to make happen.

But there are limitations.  Large numbers of users, activity when not logged on, web fidelity.  .Net has none of those limitations.  It just has more overhead.

'It's just that, early on, I also learned that not everything should be crammed into Access ...  '
I have no budget.  So over the years I have learned how to wring every last drop of productivity out of VBA that I can.
0
 

Author Comment

by:GenesisTech
ID: 40604738
You guys are AWESOME. This is why I love Experts Exchange.

Thank you, Thank you, Thank you for all of your efforts and discussions.

My partner and I currently have about 6 "small" tools that we have written in VB.Net and Access 2013 that we use to make managing certain situations easy and end-user friendly, We have discovered many bizarre situations over the last 5 years while managing a large e-commerce catalog that have required a simple tool to save many, many hours. Which is really good if you can create the tool yourself (which we can).

I apologize that I did not jump into the discussion today as I got side tracked and was busy.

I will look this information (and the sample DB) over in more detail tomorrow and respond again.

My guess is that DnD with VB.net is going to be the way to go because it will be a low level employee (end-user) that will need to use the tool and DnD seems like a better fit.

Having said that, I LOVE Access and have been able to accomplish sooo much with it over the years and it has a special place in my heart - so I will look at both options with open eyes.

Thanks again guys!
0
 
LVL 84
ID: 40605348
For 300 image paths in a SQL Server table that needed updating perhaps once a month, and only you needed to use the tool, which would you build it in?
VB.NET, every time. It does a much better job of handling images using native controls, like the ImageList and image-aware controls like the ListView, TreeView, etc.

WIA is the Image Acquisition library that's been included in Windows since Win95. While it has rudimentary "management" of images, it's main purpose is interfacing with external devices. Your use for handling a camera for the "longest day" project (which sounds really cool) would seem to support that.

To summarize: If I were handed a project where the company needed to manage Customers, Invoices, etc, then the better choice would very likely be Access. If I were give a project where the company needed to manage Images, Audio, etc, then the better choice would likely be .NET.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40606124
WIA 2.0 shipped with Vista and could be back-installed to XP.
Basically, everything that the Windows Photo Viewer exposes (rotation, resizing, querying EXIF information) and more can get done with WIA, and done PDQ.  Also among its functions, now, since MS decided to tell webcam manufacturers NOT to ship WIA drivers for webcams, is interfacing with external still image devices -- hence Image Acquisition.  But WIA doesn't need to acquire from a device--it can acquire from file, and after an image is acquired, then the question becomes 'what would you like to do with it?'  And the answer is 'anything you'd like.

And fast.
(Looking at the documentation for an ImageList, it wouldn't surprise me at all that whatever WIA is using underneath to do its thing is similarly used by just about everything else that Windows presents natively for image manipulation)

Combine that with Access 2010+ and the ability to bind image controls to paths, and you can do most of what you'd like natively in Access, without a lot of elbow grease, frameworks, installers and packages, but with all the limitations that Access presents.

But not drag-and-drop.  If you want/need that -- and I suspect if you had the facility to develop that you'd want it :) -- then it's .Net

(PS Since MS went out of their way to eliminate WIA-compliant webcam drivers, and DirectShow can't be accessed from within VBA, triggering the image capture got done with WinAPI wm_cap_ calls -- less fun, but doable.  The reason I built all that in the first place was I have a calibrated strain ring that has Mitutoya measuring device that measures deformation in micrometers -- that has no computer capture facilities.  We wanted to correlate that with a pressure gauge that did.  So we got the webcam taking an image five times per second like the gauge, and then correlated the images's DateTaken with the gauge's output.  A lot of scutwork typing the number from the images into a field, a graph, and a linear regression equation and we were in business.  Neat project.  Then I played with it at home)
0
 
LVL 84
ID: 40608229
In .NET, I can drop a few Listviews and ImageLists into a split container, and write a few lines of code to handle the Drag and Drop. Set a couple of properties of the Listviews, and I've got pretty much the full set of requirements of the author (of course, the code would need to handle the data save and such.

The only requirement my target environment would have would be the correct version of the .NET framework. I wouldn't have to concern myself with version of WIA, or references, Access version, etc etc - just make sure the target machine has .NET 3.0 (or 3.5, or whatever framework I'm using) and it should work as-is.

If you're doing image manipulation on the form (i.e. the end user wishes to edit the image), then you're going to be working with ActiveX controls, I'd assume (since the Access Image or Picture control won't do that) - so you've got more dependency issues, and more installation issues, especially as you move into newer versions of Access. .NET includes the Graphics framework, which can be used for things of this nature, and has several native controls you can use to manipulate images. Also, there are dozens upon dozens of 3rd party controls that provide this sort of functionality for .NET, whereas you have only a handful to look at in Access (and you still run into the ActiveX issues we've all seen come about sine 2010).

Not to say it can't be done in Access - I'm a big fan of Access, obviously, and have extended it far beyond what it was intended to do - but IMO for the specified requirements, .NET is a better choice.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40608442
If drag and drop is the requirement, then .Net is the only choice.
Full stop.

I wouldn't have to concern myself with version of WIA, or references, Access version, etc etc
<grin>You know late binding well enough to know that isn't really an issue and hey, is there that big a difference between a few 'imports' statements at the start of a partial class and a few Access references?  Potatoes, patatas. </grin>
WIA is at 2.0 and ships with everything after Vista, nothing to manage there.
If you're doing image manipulation on the form (i.e. the end user wishes to edit the image), then you're going to be working with ActiveX controls,
Nah.  Acquire, rotate, resize, crop, flip, merge, tag, save as bmp, png, gif, jpg or tiff, all native to WIA
Not the whole kitchen sink, but a good bit of it.
Perhaps all that you'd need.
https://msdn.microsoft.com/en-us/library/windows/desktop/ms630819(v=vs.85).aspx

Stuff that can be really handy if you, for instance, want to print 6000 x 4000 pixel image on an Access report and it just won't come out of the printer.  Or if your field guys take some pictures in portrait and some in landscape and you want everything to print not-sideways.  There's a great deal of hassle-free power available with WIA 2.0

There's a hell of a lot more available in .Net of course.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40608454
I hadn't thought of that until just now
not-sideways

I've dealt with a product catalog in my day, too.
The images aren't always the same size or in the right orientation, or of a size to be useful.
Sometimes they were way to damn big, or so teeny that they wouldn't scale legibly.
How much more beyond straight-up path updating do you want/need your tool to be?
0
 
LVL 84
ID: 40608482
Import statements aren't like References. You are not required to use Imports in a .NET program (you can explicitly refer to the full namespace instead, which many people do), but using them can make coding easier, since you don't have to use the full namespace reference.

My point regarding "Image manipulation" is simply that .NET would include much of this natively, directly on the the form. Access won't do that - the best you could hope for would be to display the image in an Image control, and then provide functionality via buttons or menus to allow the user to manipulate it. .NET allows you to create a Graphics object, which can provide a GDI drawing surface. On that, you can work with the image in realtime, and make changes as you would expect in a typical image manipulation program. You can't do that in Access unless you include some form of ActiveX control.

In other words, as I've said all along, assuming proficiency in both environments, .NET would be the better choice for something of this nature. That's not to say that Access can't handle the bulk of the requirements - it very likely could - but it'd be sort of like using a Prius to haul a load of plywood back from the lumber yard. It'd probably do it, but you'd have to make a lot of adjustments and hope ya don't break anything. Throw that in the back of a Ford F350 and you just turn the key and go.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40608533
'On that, you can work with the image in realtime, and make changes as you would expect in a typical image manipulation program. You can't do that in Access unless you include some form of ActiveX control.'
Agreed.
If you want the full enchilada of mouse-driven image editing, then Access isn't your tool.

'That's not to say that Access can't handle the bulk of the requirements - it very likely could - but it'd be sort of like using a Prius to haul a load of plywood back from the lumber yard. It'd probably do it, but you'd have to make a lot of adjustments and hope ya don't break anything. Throw that in the back of a Ford F350 and you just turn the key and go. '

Nah, you've said that before, but that analogy is off by an order of magnitude, and that's my point.  You could go to the lumberyard and pick up a lift of plywood with a half-ton truck or a deuce-and-a-half.  Both can get the medium duty jobs done with ease, and perhaps a bit of overkill.  If you have to haul back a load of trusses, yeah, then the half-ton won't get the job done by itself, where the deuce-and-a-half would.  VBA is much more than Prius-capable when it comes to images.
0
 
LVL 84
ID: 40609625
True, I have used the analogy before, but it's fitting here as well (although I do agree that VBA is more than Prius-cabable). Fact remains that the Prius is not the right tool for the job, just as Access is not the right tool for the job when you talk images or DnD. Storing images, or image paths, in Access is trivial, and that is IMO a good use of Access. Doing anything else with images is IMO not a good use of Access, and should most likely be taken to other environments (and especially since the author mentioned they were comfortable with .NET).

As I've said many times before, just because you can do something doesn't mean you should.
0
 

Author Closing Comment

by:GenesisTech
ID: 40613221
You guys are awesome. Thank you again for all of the dialogue, information, and attention to my question.

I split the points since you both actually answered my question - 1 for Access and 1 for VB.

I gave Scott the best answer because of the Drag-n-Drop part of the solution.

Thanks again you guys!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now