OLE Questions

[Reese]

From what I understand an OLE field would be able to hold a picture instead of text, correct?

Can I use an image in an OLE field as the image in a report? Here's my situation:

I have a report that we use to print an invoice that we send to clients. I want to include a scanned copy of a staff member's signature on the invoice but there are multiple staff members who would use this.

If I create a table with a Staff_Name field and an OLE field of Staff_Signature, which would hold the scanned signature image, could I use controls in a form to select which signature is included on the invoice report?

I'm not necessarily looking for how to do this right now, just if it is possible. I doubt I have time to really work on this in the immediate future, I'm just wondering if it is something I should look into.

I already have a function with multiple copies of the invoice report, each one with a different staff member's scanned signature. But that's bulky and it means that the reports and command buttons need to be changed with any staff changes. I would like to streamline this process.

Thanks for the help!

 

[vbaInet]

It is possible but you don't need to use an OLE field. What version of Access are you running?

 

[Reese]

I'm using Access 2010.

 

[vbaInet]

Ok, you can store the images in a folder and display them using an Image control.

Your table will contain two fields:
1. Path to folder: e.g. "C:\Users\Username\" or a Server path
2. FileName: e.g. "smith.jpg"

A combination of 1 and 2 will give you the full path to the imag:

[ImageFolder] & [ImageName]
"C:\Users\Username\" & "smith.jpg"

You will then set the Control Source property of the Image control to the above and it will automatically display that image.

Ideally, all the images will be stored in the same folder so you could create just one table with one record that will store the ImageFolderPath and use that one record in your other table. But you can look into that later.

You might find some other useful information about this.

 

[Reese]

Cool! I didn't know you could use pathways in Access fields like that. I'll look into that more when I have time.

I'm assuming that this technique works with a shared server as well. My database is a split database with a front end and a back end.

To clarify, the table would need three fields, not two, correct? One field for staff name and then the two that you suggested. So it would be:

[StaffName] [ImageFolder] [ImageName]

And then the staff name would then be used to select which signature to use, right?

Thanks for the help. I'll leave the discussion open in case others have any ideas but I'll certainly return to yours when I have time to investigate this further.

 

[vbaInet]

Yes it will work on a shared folder.

Depends on your setup. If it's going to be included in your Employees table then you need only those two extra fields. If you want to have a separate ImagesSignature's table then you would need more than two fields. You need to think about what you're going to call the image, e.g. first letter from staff ID + firstname, or are you actually going to use the StaffID as the image name?

My setup would be as follows:

ImagesPath table - will hold the path to the relevant folder
--------------------
PathID, Number/Text, Primary Key - if you're going to be linking to other image folders for other uses, then this field will indicate which folder to point to.
ImageFolder, Text - will hold path to the folder
PathDescription, Text - describing what kind of images are store in that path


Employees table - to include the following extra fields
--------------------------
PathID, Foreign Key - linked to the ImagesPath table
ImageName, Text - a unique name that identifies the image. If the image name is going to be a mixture of the StaffID & FirstName then this field won't be necessary.

Best not to have spaces in your file name.

 

[Reese]

I'm thinking of having the path and image names as part of the staff table. Why bother separating the two when each signature is directly related to a staff member and wouldn't be associated with anything else?

 

[vbaInet]

PathID, Number/Text, Primary Key - if you're going to be linking to other image folders for other uses, then this field will indicate which folder to point to.

As explained above. But besides that, you do it because it's repeated data and should be normalised. If the path changes for whatever reason, you simply change the path in one place (i.e. the new table) and not in every record in your Employees table.

 

[Reese]

I see your point about normalizing the table but there will never be more than 3 or 4 staff members using this system.

If it was a large number of staff who would use it, I would agree with you, but in this case I think it's easier to just have one table. Less confusing and it's not difficult to change the content of a field in three records on a small table.

 

[vbaInet]

It's a future proof system, but obviously your call.

 

[Reese]

I agree it is future proof, but not under my circumstances. We're a small non-profit zoo and this system is for just the education department. We don't even have an IT department; I am an environmental educator but I also know computers so I'm also the defacto IT guy.

If we had a large employee base, an IT department and someone dedicated to databases, etc. your suggestion would be best. But I need to think about how to keep things simple for someone who doesn't know databases in this kind of situation.

 

[vbaInet]

That's why I said it's your choice Reese. You know your business model, I don't. All I do is advise on possible solutions and the poster implements whichever way s/he sees fit