Access 2007 Batch Import ID pictures into 800+ records

[EHOHP]

Access 2007 on XP Pro
Each record contains two fields - StID (a unique ID number) and Pic (an attachment field to hold a jpg). Is it possible to create a batch import that places the picture in the appropiate Pic field for each of the records - and "skip" a record if it's picture can't be found.

ie StID=3076, Pic would import 3076.jpg.

All jpgs are in the same folder, CafePics.

Thanks

[missinglinq]

This subject has been discussed here and on other forums frequently, so to begin with you'd do well using the Search Feature with the search terms storing and images. Here are a couple of external sources as well:

http://www.vb123.com/toolshed/99/externalimages.htm

http://www.granite.ab.ca/access/imagehandling.htm


The general wisdom has always been to store the path to the image rather than the image itself because images, especially jpegs, create tremenous database bloat in Access.


Good Luck and Welcome to TheScripts!


Linq ;0)>

[EHOHP]

I had tried that when we were using Access 2003 with limited success. I thought, with 2007, file size issues had been corrected with the attachment feature. I am experimenting with the information found on the web sites you've given.

Thanks

[rhkodama]

Hello. The attachment feature is an improvement in image handling. Access 2003 was incredibly braindead in imbedding images. If an image file in compressed format, such as JPEG, was imbedded the database size would increase by the size of a corresponding uncompressed bitmap (!!). More remarkable is that this behavior was the same if you asked it to link to the file. In contrast, MS-Word handles linked images the way you might expect (file size increases neglibly). The only tolerable way to put lots of images in a Access 2003 database was to use a hyperlink (ick) and/or a low-res thumbnail (ick).

Access 2007 attachments are a curious beast. It pretends to keep a reference to the original file, reporting a list of the file names. However, the file is actually imbedded in the database file. You'll see that if you move or delete the original image, that the attachment is still there. If you try to open it, via the attachment manager, Access will automagically create a copy of the file in a temporary internet files directory for the default viewer to open. The main improvement over Access-2003-style imbedding is that the space it takes up in the database is *only* the original size of the file (not an uncompressed bitmap). In my opinion they should have just fixed the broken functionality in 2003 (but then MS couldn't tout it as a new feature!). Anyway, it does make life slightly more managable if you want high-res images in a database. It also does quite well for attaching highly-compressible, line-art type images using PNG format.

Cheers!
Richard

[youmike]

Linq's advice about storing the link rather than the image cannot be emphasised too strongly. Since you have already chosen to name the .jpg files with an Id No as the file name, the process is really simple. Apart from the bloat that the .mdb file will suffer, my experiments suggest that you wil slow perfomance too if you choose to embed.

I've done this with pictures of plants for a nursery catalogue, using a custom built VBA module, which responds to a no match with a message box cautioning the user when there is no pic. If, like my application, yours usually needs only a single .jpg at any time, I think you really will see the better performance.