Photos with HR database

[mojo1979]

Hi,

I have a simple HR database with employee data. I would like to add a photo of each employee to the record. What is the easist way to do this?

Thanks!

Steven

 

[AlexCuse]

Probably to store the images' location in your 'photo' column, and then using VBA to display the photos on the form. I know you can store photos in the database itself, but this will probably make your database grow much larger than it needs to and negatively impact performance.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[BNPMike]

The easiest way is to create a field and store the photo in each record. If you create a form in the normal way, the picture control will give you the functionality to load the picture. It's a doddle.

I remember setting up something like this is a few minutes in a race against a Powerbuilder person. Needless to say, Access won the race.

 

[ZmrAbdulla]

Don't add the pictures in the db.. It will kill your db.

What Alex suggested is the best way.
keep the id of the record and the image name same and use VBA to display the on the form. A sample here

Private Sub Form_Current()
    'dim variables
    Dim folderpath, ImageName
    
    'find folder path
    folderpath = CurrentProject.Path
    
    'set image name with extension
    ImageName = Me.txtImageName.Value
    
    'capture new record error
    If Me.NewRecord Then
        Exit Sub
    Else
        'set picture to the image
        If Dir(folderpath & "\" & ImageName) = ImageName Then
        Me.Image6.Picture = folderpath & "\" & ImageName
        Else
        'set a nopicture image
        Me.Image6.Picture = folderpath & "\" & "NoPicture.jpg"
        End If
    End If
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[RuralGuy]

IIRC Larry Linson has examples of three different methods to achieve what you want in his samples.

http://accdevel.tripod.com/imaging.htm

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[BNPMike]

Don't add the pictures in the db.. It will kill your db.
"

If the company's that big I doubt they'd be using a home-brewed HR system...

 

[JoeAtWork]

The easiest way is to create a field and store the photo in each record. If you create a form in the normal way, the picture control will give you the functionality to load the picture. It's a doddle.

I remember setting up something like this is a few minutes in a race against a Powerbuilder person. Needless to say, Access won the race.

Well yes, if you don't mind each 50K picture taking up 500K in your database.

The way it's done in the Northwind database is one of those "look how simple it is" examples that is completely impractical in a real application.

 

[RuralGuy]

JoeAtWork: The way it's done in the Northwind database is one of those "look how simple it is" examples that is completely impractical in a real application.

What version of Access are you using? Are you talking about the Employee form? acXP that I use seems like just the way one would want to display pictures.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[JoeAtWork]

I think I was using Access2000 when I tested it. I suspect that XP will do the same thing.

But just try it out and see. Set up the form as it is in Northwind, and add a few pictures. Then compare how big the database has grown compared to the original size of the pics. You will probably find the pictures take up 10x their original size in the database.

I have implemented a picture database once, but I did not use the "automatic" code that does the bloating. I wrote my own code (adapted from a solution I found on the web) using ADO's GetChunk method, that stored the bytes of the picture file directly, without all the header information that causes the bloating.

 

[RuralGuy]

My Northwind sample uses *external* pictures and pointers to them in a table.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[jadams0173]

Hey all. I'm coming in the middle of this but, for what it's worth I use Access 2000 and tried to put pics into a table. BAD IDEA!! All things that have been previously mentioned happend (bloat, huge performance hit).

So I reverted to storing the paths in the table and the pictures in a different folder as mentioned and it works great. I found all of my help here:

thread702-289543



I tried to have patience but it took to long! -DW

 

[BNPMike]

This is a standard sort of database problem. If some aspect of your data is affecting performance then you are 'allowed' to think of a physical solution. (The relational model only covers logical and external schemas). Although logically the pictures might belong with other fields, you split them off for performance reasons. This degrades ease of access but it is normal practice to consider de-normalising, collapsing and similar strategies.

Separate the pictures into a table that only has pictures and a foreign key. Then you only get involved in large bits of data when you request a specific picture and your index will limit your access to the blocks involved. The mdb might be big but it's unlikely you'll care about that.

 

[RuralGuy]

You can also put the pictures in a separate mdb and link to it if you want to go with storing the pictures *in* your db.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[JoeAtWork]

Ah, they must have changed it. I'll have to try to find my XP version and take a look.

 

[JoeAtWork]

BNPMike - it's not just that it's a large binary field, it's also that Access adds a whole bunch of header information which increases the size by at least 8x (at least the 2000 version did, apparently XP uses pointers to external files as per Rural Guy).

So yes, store in a separate table, but if using Access2000 write your own code to store the data, rather than binding the picture control.

 

[BNPMike]

My feeling is you shouldn't solve problems by writing code. If you want to write code there are many other platforms you could choose. Access's strength is in the things it can do without programming.

The size of the stored pictures (and this could be other data in other applications) should not concern you unless either 1) it hits the mdb size limit or 2) it creates a performance problem. You can't do things like

Sum Picture order by Department etc.

You should only ever be looking at one or a few pictures at a time. Access may be viciously inefficient in the way it stores your pictures but with the current price, speed and capacity of storage, why would you care?