Complex Problem with Access

[adornis]

I've used access to do some simple things and know my way around a bit, but I need help from the experts now because the task I'm currently working on will require some code...

I have a single table containing 10,000 rows of user data. The table headers are PersonID, Surname, FirstName, and MiddleName. However, there is a current need to add a column in which I intend to attach a passport photo to each of these 10,000 names.

I currently have all 10,000 passport photographs (saved using each person's Surname, Firstname, Middlename, and PersonID in jpeg format) in a folder, but it is going to take forever to attach pictures one after the other. I know it is possible to write a script or module or something which would instruct MS Access to read the user data from the table, locate the picture folder, open the folder, search for the corresponding Surname, FirstName, MiddleName, and PersonID picture file, and attach this jpeg file to the correct row in the table.

I would more than grateful for any assistance i can get.

 

[PaulO]

I can't help you with the script, but can warn you of the impact of importing thousands of JPEGS into a table.

Even if the JPEGS are carefully managed in terms of actual filesize and/or resolution, you will experience severe database 'bloat' meaning that the database filesize in increase exponentially to several hundred MB if not a couple of GB.

The trick, as I understand it (but haven't yet tried), is to leave the JPEGs outside of the database but instead put the filename and path in a new text field within each record.

The images can then be called upon whenever required (forms, reports). It's this last part that I've not been able to fully understand but there are others on this Forum who can perhaps upload a working example?

 

[Simon_MT]

The easy bit getting access to read the jpegs. Access 2007 native image formats are supported. Prior to 2007 images are stored as bitmaps no matter what there native format bloating the database.

If the Directory for the images C:\Databases\Images\ and the image was stored as PersonID

Your file would be C:\Databases\Images\PersonID.jpg

In the underlying query is Thumbnail_File = [PersonID] & ".jpg"

It is better to declare the PicturePath separately, I have the Image Directory on the Main Menu. Create a control on a Form (whatever your location for the images) eg

[ImageFile]=[Forms]![Menu]![Image_Directory] & [Thumbnail_File]

Then an Image Control (Linked)

[ImageControl] =[ImageFile]

Simon

 

[adornis]

Please explain this in more detail... I don't know anything about access codes and scripts. I have implemented a picture field in my table and used hyperlink as my data type for that field. I currently have only the table, can you give me a step by step procedure on how to setup the form and where to enter the codes? I'm very appreciative of this help.

 

[Simon_MT]

The first step:

An image is just a picture all you have to do is associate that image with your record eg

PersonID = 1 etc

therefore the corresponding image would be 1.jpg

In your Query behind the form or report would have an expression:
ThumbnailFile: [PersonID] & ".jpg" and result in 1.jpg

All you are doing is associating a record with the image.

Simon