Cataloging images using an Access database

I'm wondering if anyone has written their own image cataloging and

retrieval system using the Microsoft Access database. Well, I'm sure

someone, somewhere has, but has anyone reading this question done it?

 

I've played around with Access a little and written some small

applications. It certainly wouldn't be hard to write something that

stored images with entries for id#, category, keywords, shooting data

etc.

 

Two things I'd like to know. First am I missing something here. Is

there a good reason NOT to do this, some intrinsic limitation of

Access (other than it being a Microsoft product). Second how easy is

it to link to and display thumbnail images in an Access database via

queries? Since I've only written text based applications this isn't

something I've yet tried. It's all very well searching for all images

with "ferret" in the keywords, but a list of filenames is a lot less

useful than a page of thumbnails!

 

The reasons for considering this is are (a) I can configure it to my

exact requirements, plus I can modify it at any time and (b) I

already have Access and I'm cheap.

Bob, I keep thinking the same thing. And never do it. :(

Well you can have a version of MySQL for free and it runs on many more OS's than Access. It all depends on your needs. Personally, I like archives that last, so that I don't have to redo everything and there are plenty of conversion tools for Access, but basically you're limited to the Windows-platform and the technical limitations of Access unless you wish to fool around with conversion. This might work perfectly for you or it might not, depends on your needs.

 

I can't see any other reasons, but I'd go crazy using Access on a regular basis, so I'm probably not the best person to judge...

Well, I actually did something like this in a very simple way 3 or 4 years ago for a work project. It didn't take long to get something sort of useable. I vaguely remember struggling with the images though. I think I managed to an image to display, but like I said, it was a few years ago and I only spent a couple of hours on it. I didn't do anything with it at the time and I don't know what happened to the code. I currently have Access 2000, and the help file isn't very clear on whether it will display .jpg files without some additional "filters". Seems to depend on which version of Access 2000 it is (pro, home, idiots, crippled etc. and/or what other MS Office applications I may or may not have on the machine). Typical MS BS

 

Before I look at it again I thought I'd ask, since database experts may know some reason why I'd be wasting my time trying to do this!

 

I presume the way to do it would be to store the paths to the files in the database rather than the files themselves and then create some sort of link which would display the image.

 

On the other hand there are probably dozens of freeware or shareware application that try to do this and that don't cost an arm and a leg. Whether any have as much flexibility as something I could write myself is another matter, but then again maybe flexibility isn't needed if the application is well designed.

<p>Have you looked into pre-made solutions yet, like <a href="http://www.cerious.com/thumbsplus.shtml">ThumbsPlus, <a href="http://www.photools.com/imoverview.html">IMatch</a>, and others? I understand the appeal of building your own system, but the biggest problem isn't getting started, it's getting finished! (I wonder what happened to <a href="http://www.photo.net/bboard/q-and-a-fetch-msg?msg_id=0029ry">The Ideal Photography Database</a>.) So far every package I've tried puts a huge speed bump into my workflow, but I work with embedded IPTC data, so my requirements might be different than yours.</p>

 

<p>Have you considered using Access purely for storage, but web tools run locally for the user interface (e.g., ASP, FrontPage)? If you have a working knowledge of SQL and HTML this might be worth it. File paths become viewable images in a hurry with IMG tags.</p>

 

<p>Good luck on your quest, wherever it leads you. BTW, if anyone knows of an API-driven IPTC index/query engine I'd love to hear about it.</p>

<i>I presume the way to do it would be to store the paths to the files in the database rather than the files themselves and then create some sort of link which would display the image.</i>

 

<p>Your assumption is correct, the Right Thing to do in an SQL-RDBMS is having links to the files, as the SQL datatypes are designed more with text and numbers in mind, rather than raw binary data. The modern way to do this is with a browser-interface to dynamic pages, I believe someone named Balint Kis posted not-so-long-ago a link to his software that did this for free by using open-source tools (and I think you replied to that thread.)

 

<p><access-criticism>If you want to connect to webserver and use ASP or similar to browse/manipualte the data, I suggest you look at more "serious" DB's, as the "advanced-features-made-easy" software like Access are usually not easier but less advanced than the "advanced-only" tools.</access-criticism>

Have you looked at Adobe Album? In it you can store images in "albums" you create. Then you can view an image and tag it directly with several key words. Then later you can do a specific key-word search in a specific album, albums, or all albums. I'm cheap too, but this is very slick and for $100 far less trouble than trying to figure out how to do it in Access! BTW, PS Album also offers a set of very good image adjustment, cropping and formatting tools.

 

Cheers,

Thanks for the tips and pointers so far.

 

As to the cost issue, while $100 may not be much in the grand scheme of things, once you have your basic software set supplied with your camera/scanner/printer and then you start adding up $50 for better scanner software, $100 for database software, $100 for a better noise reduction program, $50 for a better RAW conversion program, $100 for a better upsizing program, $100 for a set of "ad-on" filters, $300 for a printer RIP, and do on and so on, you start to run into serious money!

Uh-oh Bob. I think that's what the film people keep saying. If you add up all these things you need to take, store, maintain and catalogue digital, it does get expensive.

 

As far as I can tell, everything about photography is expensive in one way or another and it just depends on what you do, what you expect and what you need to determine what is the most reasonably financially.

Conni

I've thought about doing this kind of thing before as well, but I just figured that the big boys like Extensis and Cumulus probably have implemented nicer user interfaces than I have time to do on top of basically the same kind of infrastructure.

 

Of course, you have to pay them for the code, but it doesn't take much of my time to get up to the cost of an off the shelf program.

If you don't want thumbnails, developing a photo database in Access is a pretty trivial task. Being an MS application it has enough "Wizards" and hand holding that you don't even have to know what SQL is, nevermind how to use it, and all the graphical interface stuff is done for you.

 

Of course if you want thumbnails or you want to link to it from a web server, then you have to do some work.

 

Digital can get expensive if you get carried away with the software, however I think most of it isn't very cost effective. There are exceptions, but there's a lot of stuff out there selling for $100 and up that really doesn't do much you actually need to do or can't do at least 95% as well with basic software that comes "free" with your digital hardware.

 

I suppose we could start a "photo.net Access database open source" software project! Unless of course MS has something in their licence agreement about not giving away source code generated by any part of the Access package. I wouldn't put it past them.

Bob - IN A RELATIONAL DATABASE, the key is database design, normalized to the extent possible (probably down to the 3rd normal form). You'd also probably want to use foreign keys to associate records across multiple tables. I'd say you're talking about 10 tables, maybe less.

 

Once you'd designed and normalized your tables, then it shuld be simple enough to write SQL commands to join tables and retrieve whatever you want. That's how I'd do it using Oracle or Sybase or another rel db.

 

The same principles probably apply to MS Access.

I agree with Jack. MS-Access does not have any inherent limitation, at least not for the scales relevant to the application you are considering. The key is to design a good database, and that may not be as simple as it sounds if you have no formal knowledge of relational databases.

 

Regarding your question about thumbnails, yes it can be done. An easy way to go about doing this is store the paths to image files in the database and then display them in various sizes (for the needs of each screen/form) using an image display control. I'm not sure what quality you will get with reduced size images - the image resizing algortihm used by the standard image control is a bit crappy, but you probably don't care that much about this.

Bob: I am familiar with Access but in my opinion, you would be re-inventing the wheel and putting in a lot of work only to come up with a horse and buggy. The new Adobe Photoshop Album is just that, a database with more bells and whistles and practical do's that you or anyone else for that matter could ever build into Access. Do yourself a favour, let Access to what it was intended for and go for Album. Adobe has invested more money into Album that you or anyone could afford to put into Access. For $50.00, i'ts a no brainer.

I'm a bona fide Access guru, and I would say: Not. It won't take jpg's, so you would have to do a lot of work generating a format it will take. Personally, I just use the thumbnails in Explorer. I have tried some commercial products and found their database capability somewhat limited. I put about 25 scans (2700 dpi) in a directory because this is what a CD can store. I haven't tried Adobe Album.

I was thinking of creating my own database because I want to code in my own system for categorizing my images. Maybe I could do that with commercial software, but I know if I write it myself I can have fields for all the items I want, with dropdown menus listing my own sets of options.

 

Perhaps such customizable software already exists and I should do more research into what's out there. It would certainly be nice if a program could also extract various EXIF fields and automatically insert them into database fileds, and if a search not only generated a list of files, but thumbnails to go with them.

Remember that MS markets SQL Server as their "serious" RDBMS platform, I'm not quite sure what the intended market for Access is, as you can do many lighter tasks in Excel pretty well. Maybe they only designed Access to be used as an ODBC front-end? Anyway, I suggest you search the net, there are plenty of apps for EXIF extraction, image cataloging etc. available for free, but they're probably not as comfortable to use as the proprietary professional image-management systems.

I just tried something in Access 97 Pro, and there are certainly controls for including images in forms. While I didn't create a real application, with tables and code, I believe it should be possible.

 

My personal approach to this is about the same, although I used Delphi and Paradox to create my own "PhotoDB" application. Fairly straightforward, and very much engineered towards my own needs and ideas for a photo database.

 

As mentioned before, the problem is indeed finishing the product so that you can actually use it. I have spent many, many evenings writing and altering code. I now have a product that works. For me, that is. Suited to my ideas about rolls, photos, keywords and scans.

 

I haven't used Access 2000 or XP, but the earlier versions always looked like "great end-user reporting tools", but "terrible developer tools", even more so if you used a Dutch version. Access 2 and Access 95 were by no measure real development tools and I vastly prefer something like Delphi and a stand-alone database (although Paradox effectively reduces multi-user to multi-loser if it loses track of its indices).

 

You might also think of building something web-enabled. Maybe you'd like to be able to access your database when you're not at home, maybe you'd even like to serve the photos to the rest of the world (searchable and all). Coding in Access will then probably prove to be a hindrance (if not an impossibility), but coding in IIS/ASP[.net] and using an Access database could be a viable solution.

 

HTH, IMHO, YMMV etc. :-)

I have a question for those of you in this thread that really know Access. I want to create a database for a professional photographer friend. What he needs is a customer database - the usual business information (name/address/phone number/work done for the customer/etc.). Since there is a lot of work done both with film and digital, I am thinking that the database should show where the negatives and/or digital files are stored, but probably not have any embedded photos.

 

My question is this - can Access link to some sort of viewer software and pass a filename and location to that viewer to look at the digital photos? If so, a short clue to how would be appreciated.

 

I've built some relational databases using Access (and other DB programs), but I'm weak in the VBA programming area.

Skip, at the risk of venturing way off-topic here... have a look at the shell function in Access:

 

<pre>

Private Sub Button0_Click()

Shell ("C:\Program Files\ACDSee\ACDSee.exe e:\temp\image1.jpg")

End Sub

</Pre>

 

The above works quite well on my system (it starts ACDSee and shows the image).

Fantastic, Patrick. That's just exactly what I was thinking I could do within Access, but I didn't have a clue how to look for it in my Access books. The indexing in most of those books is so horrible for someone not fully aware of all the possibilities.

Bob, if you have some description of your desired cataloging system, or anything close to user requirement for this application, would you care to email it to me? I am in the process of creating a cataloging system for photos and I am sort of collecting requirements from different people to figure out a good scheme that would be customizable to meet special needs.

I made a little Access database to store information on my photos. Displaying a thumbnail is pretty easy with the activeX controls. My version of Access (2000?) comes with a kodak image edit control which allows one to display thumbnails (integrated with the forms) with a few lines of code. For example, the following code will display a thumbnail in image box.

 

ActiveXCtl36.Image = directory & filename

 

ActiveXCtl36.FitTo 0

 

ActiveXCtl36.Display

 

However, the kodak ctl does a poor job of rendering small thumbnails from larger jpeg images in the sense that the image quality is low. It is certainly adequate for browsing though. I think there are other ActiveX controls that one can get from other vendors that are better.

 

I tried the evaluation version of IMatch and thumbs+. The biggest drawbacks for me are that (1) They don't allow you to keep information on images for which you do not have a digital file (or least I couldn't figure out how). (2) They display all sorts of information that I don't want to see, and do a poor job of displaying the information that I need. Even though you can add user fields, there's no way to set the layout of that information. In access, making a form to display only the information you want, in the style that you want is trivial.

Well I want to be able to have fields for date, location, keywords, Image ID#, technical data and comments.

 

My classification scheme (still under development) uses a letter code. For example WMC might be Wildlife-Mammals-Canines and would include wolves, foxes and coyotes (which could be WMCW, WMCF and WMCC). WBOB might be Wildlife-Birds-Owls-Barred. LSME might be Landscape-Sunrise(set)-Maine. So images might be WMC-0001 or WB0-0052 or LSME-0006. Whether a 3 level code or 4 level code is needed depends on what you're trying to catalog. Maybe a 4 letter code is enough to cover most things. It does give you 456,976 categories if each letter is significant, 33,800 categories if one letter pair is a state.

 

By the way I have hacked together a very basic Access database which includes these fields and seems to be capable of displaying images (including jpegs) via OLE links. I'm still not too clear on embedding vs. linking but I think embedding copies the image into the database, while linking gets it via an external link.

 

I assume links are best, but you still have to have all your images available at all times to the database (not stored on a CD in a drawer somewhere) so you have to at least create thumbnails of all your images and keep then stored on a local disk tha the database can see. This applies to any database or cataloging system of course, but I'm not capable of getting Access to do that for me so I'd probably have to make all the jpeg thumbnails in another program, then enter them one by one into the database. I also doubt I'll be able to write code that will extract EXIF data and load it into appropriate fields in a database without a lot of work and maybe learning Visual Basic a lot better then I know it now!

 

I did look at some simple catalog programs like Thumbs Plus and Adobe Album and neither seems like it's very flexible as a database. They're cataloging programs that don't allow very sophisticated searches, though they do automate much of the "grunt" work of creating thumbnails and automatically putting images in the catalog.

Yes, embedding means you get a copy of the image in the database. This will make the database grow a lot, and if you're talking about cataloging 50-100k images then perhaps you're going to run into trouble - I'm not sure if Access is going to be able to deal with the filesize. Linking just liks to a particular location (path) which also has the added advantage (could be a disadvantage in some cases) that if you modify the image, the modification is automatically visible within the database.

 

If you want to archive on CDs, you could do the following. Designate a location on your harddrive for thumbnails and have the database link to those thumbnails via a file path. Then for each photo, create a field to denote the CD code/number and the path in the CD filesystem, so that for each image you can see the thumbnail and be informed which CD to put in the drive to be able to see the actual image.