Appending Attachments from one table to a new table

[unclefink]

I currently have an unnormalized table that consists of individuals (employee profiles) which currently has a field for attachments. I am using the "attachments" for photographs of each employee however I dont want to maintain photographs for former employees, or if I do not forever. I was told to normalize the table by removing the "attachment" field and placing it in a new table with referene to the employeeID. Since I currently have something like 200+ records where I want to relocate the employee picture, i tried to create a make table query off the origional table and only referencing the employeeid and "attachment" to the new table however I keep getting an error when running the query. The error indicates something to the affect of having multiple values.

Anyone have any reccomendations that might help me transfer these photographs to a new table without having to do it one at a time?

Respectfully,

David

PS: Thank you in advance for any help you may be able to provide. I created this database before I knew everything necessary and then after going to a class, I discovered I didnt normalize the database enough.

 

[StarGrabber]

Hi David,

in attached file you'll find my solution for your task. Please note the tables are empty. I didn't want to upload bitmaps. For further details refer to the contained module.

 

[unclefink]

Hi David,

in attached file you'll find my solution for your task. Please note the tables are empty. I didn't want to upload bitmaps. For further details refer to the contained module.

I finally got around to looking at this in depth; however unfortunately its not working. It is in fact pending the id numbers but the pictures are not moving into the image field. This probably has a lot of relevance; but the pictures are of .jpg format.

 

[CJ_London]

Depends on what version of Access you are using, but if you are 2007 or later, a way to do it is as follows.

Copy all of your images to a windows folder
in your employee table have a text field which is populated with the path to the folder and picture name inc. extension

In your form, add an image control and in the Control Source put

=PicName

where PicName is the name of the field in the employee table.

One of the benefits is that this reduces db bloat and will also work in a continuous form

 

[StarGrabber]

Before uploading I've tested the procedure successfully with files of jpg format, so I cannot comprehend that "pictures are not moving into the image field."

But if you say you are not able to use the moved 'long binary data', i.e. display the corresponding pictures in a control, then you are right. I apologize for my flippancy.

If you don't want to do it the way described by CJ London and if you have the library OGL.DLL on your computer (directory
C:\Program Files (x86)\Common Files\microsoft shared\OFFICE12 or \OFFICE 14), then you should try the enhanced and updated file.

Please read the top lines of "mdlTransferPics" first and note the relocation doesn't work with .gif files.