Best Method and/or Tips in storing JPEG's

[PaulO]

I have for many years been storing JPEG's within table records, and accessing them with an unbound form field.

Basically, this works fine, allowing easy copying/pasting from a photo-editing package.

My problem/question arises from Access's handling of the JPEG insofar as the overall database file size seems to grow exponentially to the size of any JPEG that's added i.e. I might add a 100kb JPEG but the database might grow by 1MB.

Also, any images above 100kb take a noticable time to load within the form and much larger images actually freeze the program for longer periods.

Knowing these features, I have created my own 'rules' to ease this problem:-
1) Keep JPEG size <= 100kb
2) Image dimensions no greater than 6 inches squared
3) Resolution 72dpi
... however I feel I must still be missing a trick or two?

e.g.

a) Is it best to link the images, rather than embed them? If so what's the best way to achieve this
b) Is JPEG an inefficient image type?
c) Anything else?

 

[Lightwave]

a) Is it best to link the images, rather than embed them? If so what's the best way to achieve this

For the majority of people yes linking is a far better way of storing JPEGS.
There are a number of different ways of doing it. You can go all fancy and have a button that will open up a file dialog box (ie the file manager) and allow you to navigate to a particular location and then click on the file. It will then remember that location within the database field.

A very quick and easy way to do it is as follows.Add an additional text field to the relevant table. (I prefer text format rather than hypertext because it can be difficult to edit hypertext once on the form).

Then ensure that you have all your jpegs in one particular location.
Now never change this location. As an example I've used the make believe directory

c:\Directory1\

Within the form add the field created in the table. Lets call it FileNameField.
Save the design and then go to a particular record and type into the field the name of the jpeg. The code below is written assuming you leave out the extension.
Now place a button on your form and behind the onclick event place the following code

    Dim FileName As String

    FileName = Me.FileNameField.Value
    Call OpenDocument(("c:\Directory1\" & FileName & ".jpg"))

This is very simple and works everytime. You will have to ensure that you get the name exactly correct, always leave off the extension, only try to open jpegs and always store in the same location. I don't find this a problem with datasets infact storing in the same location for all sametype files is often a real bonus. The fancy systems will store the path as well. You can get access to multiple directories by altering the above code however if you store the path to each file as well as the file the big downside is using my method you would have to type out the path everytime which lets face it is going to be tedious. By storing all your files in one place it makes moving it about the place potentially quite easy.

Moving a database will however require you to copy the directory and information and then recreate it in any target machine but this can be very easy IF you have stuck to the convention of keeping all like files in same directory. Even if you don't transfer the jpeg files across the new database will still work on other machines but clicking on the open document button will just do nothing.

You will probably realise you are not limited to what file you open. You can open anything which your machine has the ability to open. You would however need to change the code and then ensure that you typed in the extension in the field as well as the name as obviously the OpenDocument function looks to the extension to determine what program to open the file and if it uses the wrong program to open a file you'll get some kind of error. If you haven't guessed when I say access opens it what really happens is that the file (whatever it is) will be opened in the default program for that particular extension type. So if its movies it may be media player, jpegs paint etc...

End result

Welcome to the world of really small databases that only hold what they should. Text and numbers. Jpeg size becomes totally irrelevant as your database will only increase by the amount of memory required to save a simple text string relating to the filename in one field. You probably won't even notice it.

b) Is JPEG an inefficient image type?
c) Anything else?

b) nope nothing wrong with it.
c) not really

 

[PaulO]

Many thanks! Will have a go at this over the next few days (hopefully).

A couple of immediate questions jump out ...

1) will I be able to view the JPEG's in an access form?
2) will i be able to incorprate these images within viewable/printable Access reports?

 

[Lightwave]

Ok two things that change things

My code will open up the jpegs in whatever default program you usually view your jpegs in and the code will not result in the files being viewed within a form.

Yes they're both possible but more complicated - you'll need to manipulate the VB modules.

Here's an example with code which you can adapt.

http://www.access-programmers.co.uk/forums/showthread.php?t=120834

 

[Steve R.]

Some additional thoughts since I have been working on storing images myself. To make the database "transportable" to other computers, I use "CurrentProject.Path" to establish the working directory. All the images are stored in one or more subdirectories within the project path.

NOTE: Image locations are stored ONLY with the (relative) pathname from the working directory.

 

[PaulO]

Thanks Guys!

Just to give you some context as to my end goals

1) Keep database size down to manageable proportions yet add up to 500 JPEG's
2) Retain the ability to create ancestral reports (pedigrees) combining raw text data PLUS jpeg image (this is currently achieved using a data query plus DLOOKUPS to pull the JPEG's in from the data table)

 

[Lightwave]

PaulO

500 is nothing, if you use linking you should expect hundreds of thousands of photos actually I'd be disappointed if I couldn't get a million links in before I hit problems. In all likelyhood something else will become the bottle neck. Plus the massive benefit you can have every pictures in whatever megapixel your given. The definition is pretty insane these days.

Yep OldSoftBosses's database is exactly what you want in terms of picture handling, it is everything you want. The only additional feature you might wish for is as per SteveR s tweak and may not be necessary.

I would suggest you get studying OSB first, get it working with your stuff. If you understand how it all works, SteveR's tweak should be a much smaller step.

 

[PaulO]

The "OSB" database is a neat solution, as you say, for linking to potentially 100's and 1000's of pictures.

The Add New Picture command button appears inactive, but it'd be a simple enough task to link it to a Form to add new records.

I also note that when double-clicking the image the background code included a statement to open it with paint.exe ... easy enough to change I guess but it might be better if this was a controlled variable?

Other than that, as I read elsewhere today, it'd probably work better if all the linked photos resided in the same Folder/Directory and have this Folder/Directory name set as fixed code so that only the file name and extension would require entry into the table.

Relating this back to my end goal, I will now see how I can read the image into existing ancestral/pedigree reports that I have. I anticipate this being a little tricky, but I'm gonna give it a right go!

 

[Lightwave]

Relating this back to my end goal, I will now see how I can read the image into existing ancestral/pedigree reports that I have. I anticipate this being a little tricky, but I'm gonna give it a right go!

I saw the problem with the add as well but if you do add which seems to create a blank record then change it does what would be equivalent to add...
so you could combine the code in someway.

That is great I think you are starting to see that these things tend to always be "tricky".

A little bit of complexity in the requirements can lead to a lot of complexity in the coding! But great that you've realised that.

Why I always go as simple as possible.

It not for no reason that's its taken us best part of 1900 years to come up with Boolean logic.

 

[Simon_MT]

The really important thing about images is the naming convention of the image. Lets say that there is an AncestralID or something similar. The file name of the jpeg image should relate to that ID. Getting the image should require no management whatsoever because there is a direct correlation between the image and the record identifier.

I have a database that handles over 16,000 external images.

Simon

 

[Lightwave]

Simon MTs idea is a really neat way of doing it which truthfully I hadn't thought of.

The only thing about that would be that there would be additional overhead in renaming the images because the camera will autoname your jpegs in a different fashion from the way you have the image name autogenerated in the database.

But set against the overhead of inputting the name into a field on a record and and the time it takes to get an image into the directory anyway you might consider it worthwhile.

 

[Simon_MT]

The camera's naming convention relates to that camera. If you want to use a database you have to rename these files, this is standard procedure when dealing with images. Whilst there is a downside, a small overhead, the upside is that each image can be identified by its name, you now immediately what each image represents.

Simon

 

[stan-ee]

Save the design and then go to a particular record and type into the field the name of the jpeg. The code below is written assuming you leave out the extension.
Now place a button on your form and behind the onclick event place the following code

    Dim FileName As String
 
    FileName = Me.FileNameField.Value
    Call OpenDocument(("c:\Directory1\" & FileName & ".jpg"))

I am trying to modify this for opening a pdf document but when I used this in the onclick event of a command button, I was given an error message that said: "Compile Error: Sub or Function not defined," refering to the OpenDocument function. Could you help me on defining that?

 

[Steve R.]

Stan-ee: You are missing components. See the code sample below. As with any sample, you will need to modify it for your particular use.

Private Sub Command10_Click()
    Rem DCM Program Submission of December 23, 1998
    Rem strAttachmentPVT = "P:\Federal_Consistency\Databases\HelpFiles\OCRM_NorthCarolina.pdf"
    strAttachmentPVT = strPathBackEndPVT & "OCRM_NorthCarolina.pdf"
    If Dir(strAttachmentPVT, vbNormal) <> "" Then
        Call OpenDocument(strAttachmentPVT)
    Else
        Call Error_Message
    End If
End Sub

Public Sub OpenDocument(stDocPath As String)
    Rem this will open any file type
    Rem from Kiwiman @ http://www.access-programmers.co.uk/forums/showthread.php?t=150639&highlight=open+pdf+document
    Dim Result As Long
    Result = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & stDocPath, vbMaximizedFocus)
End Sub

 

[stan-ee]

I actually found a perfect thread and that code worked great! With a few modifications for my personal file configurations, it worked without a hitch!

heres the address if anyone else is having difficulty solving this problem. access-programmers.co.uk/forums/showthread.php?p=1147170#post1147170

(I cant post links yet cause i dont have 10 posts )