Pictures/Images in Tables

  • Thread starter Thread starter adrian007uk
  • Start date Start date
A

adrian007uk

I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
 
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

You post is not clear.
Do you mean you are storing the pictures in "C:\My Folder"
and (correctly) storing just the above path to the image in your
database?

If so, you can run an update query to change the drive designation.

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.
 
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

What you might want to do is store JUST the filename (IMG3122.jpg say) in the
text field in your database, and have a separate, single-row table tblFolder
with just one field, Folder (e.g. "C:\Documents and Settings\Adrian\My
Pictures\"). Then in the code that displays the picture you can concatenate
the two fields.

When the path changes you will then need to just edit the one field in
tblFolders.

Alternatively you can run an Update query updating all the records in your
images table:

UPDATE Images SET filename = Replace([filename], "C:/", "D:/")
 
Hi Fredg

Thanks for your comments.

Sorry my post was unclear. Yes, if i created the database that pointed to
images on 'C' and say in six months i moved that folder to 'D', what would
the best way to design the database so this change would be quickly
implemented? I guess if i did not think in advance of this issue i may have
to go through every image and point it to the new location.

fredg said:
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

You post is not clear.
Do you mean you are storing the pictures in "C:\My Folder"
and (correctly) storing just the above path to the image in your
database?

If so, you can run an update query to change the drive designation.

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.
Click to expand...
 
Thanks John

Fred and yourself has given me somethings to think about. I will need to do
some more reading up on your suggestions as am still slightly confused as to
how i can implement them (I am quite new to access).

Adrian

John W. Vinson said:
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

What you might want to do is store JUST the filename (IMG3122.jpg say) in the
text field in your database, and have a separate, single-row table tblFolder
with just one field, Folder (e.g. "C:\Documents and Settings\Adrian\My
Pictures\"). Then in the code that displays the picture you can concatenate
the two fields.

When the path changes you will then need to just edit the one field in
tblFolders.

Alternatively you can run an Update query updating all the records in your
images table:

UPDATE Images SET filename = Replace([filename], "C:/", "D:/")
Click to expand...
 
My main qualification here is that I only know 1/10 th as much as John.

Here's how this mere mortal does it: We have two fields in the table
relevant to this: (shorten my long names)

PictureFileName Enter the picture file names here

PicturePathAndFile (only loaded by the below query)

And make an update query which updates the PicturePathAndFile field to

"Type in the path here" & [PictureFileName]
 
adrian007uk said:
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

Just a thought, and I've not tested this so there might be a red herring
element, but if your images are all in the same generic location, eg
X:\MyFolder\MyImages\ ... then you *could* store this location in a lookup
table and use DLookup to return it. So in your query you could have a
calculated field

ImageLocation: DLookup("MyField", "tblMyTable") & [ImageFolder]

where [ImageFolder] is a field to store the " ... " element of
"X:\MyFolder\MyImages\ ... "

.... and if the location changes, say to X:\MyOtherFolder\MyImages\ ...

then you only need change it once in the lookup table.

Keith.
www.keithwilby.co.uk
 
Hi Fredg

Thanks for your comments.

Sorry my post was unclear. Yes, if i created the database that pointed to
images on 'C' and say in six months i moved that folder to 'D', what would
the best way to design the database so this change would be quickly
implemented? I guess if i did not think in advance of this issue i may have
to go through every image and point it to the new location.

fredg said:
I have read up on best practice conerning displaying images in a database.

I am planning to link the images from my PC. What i would like to know is
if the location of ther folder where the images are stotred changes (e.g.,
from 'C' to 'D') is there any way it can be changed within the database
quicly without having to change every image location as well?

Thanks in advance for any suggestions.
Click to expand...

You post is not clear.
Do you mean you are storing the pictures in "C:\My Folder"
and (correctly) storing just the above path to the image in your
database?

If so, you can run an update query to change the drive designation.

Update MyTable Set MyTable.[Path] = "D" & Mid([Path],2) Where
Left([Path],1) = "C"

The above will change "C\My Folder" to "D:\My Folder" for all records.
Click to expand...
Click to expand...

Both John and I have given you correct answers. Take your pick.
 
Thanks John

Fred and yourself has given me somethings to think about. I will need to do
some more reading up on your suggestions as am still slightly confused as to
how i can implement them (I am quite new to access).
Click to expand...

What is the actual structure of your table?
If you're storing a picture filename in a text field in a table, HOW are you
storing it - just as "IMG3145.jpg" or are you including the path?
How are you getting pictures into the image control? Please post your code.
 
Hi John

My table structure is as follows:

Recording ID
Title
Artist
Music Category
Label
Year
Format
Number of Tracks
Notes
Catalogue Number
Picture (image)

I plan to have a further two tables for music categories and for the tracks.

I plan to point the database to the images which will be stored in a folder
on my PC.

I am trying to plan my database on paper first which is why i am trying to
resaerch the best approcah to take in case i need to move the folder to
another location in the future. My thinking is that if i move the folder
containing all the images to a different path they will not show up unless i
was to go through and change every image path. Is this thinking correct? If
not i have nothing to worry about but i am sure i need to plan in advance for
this issue.

I am using access 2007 so i plan to use the 'unbound' function of an OLE
object (if that makes sense).
 
Hi John

My table structure is as follows:

Recording ID
Title
Artist
Music Category
Label
Year
Format
Number of Tracks
Notes
Catalogue Number
Picture (image)

I plan to have a further two tables for music categories and for the tracks.

I plan to point the database to the images which will be stored in a folder
on my PC.

I am trying to plan my database on paper first which is why i am trying to
resaerch the best approcah to take in case i need to move the folder to
another location in the future. My thinking is that if i move the folder
containing all the images to a different path they will not show up unless i
was to go through and change every image path. Is this thinking correct? If
not i have nothing to worry about but i am sure i need to plan in advance for
this issue.

I am using access 2007 so i plan to use the 'unbound' function of an OLE
object (if that makes sense).
Click to expand...

If the picture is in an Image field in your table, then it's not ON the C:
drive or the D: drive; it's right there in your table so the path is
irrelevant!

If you are indeed going to "point" the database to images which will be stored
in a folder, then don't use a Picture field in your table; instead use a Text
field. I'd really recommend storing (e.g.) "A1234.jpg" in this text field, and
having a second table, tblImagePath, with just one text field Path. When you
need to display an image use an Image control on your form or report and set
its Picture property to a string concatentating the path value to the
filename.
 
John W. Vinson said:
If the picture is in an Image field in your table, then it's not ON the C:
drive or the D: drive; it's right there in your table so the path is
irrelevant!

If you are indeed going to "point" the database to images which will be stored
in a folder, then don't use a Picture field in your table; instead use a Text
field. I'd really recommend storing (e.g.) "A1234.jpg" in this text field, and
having a second table, tblImagePath, with just one text field Path. When you
need to display an image use an Image control on your form or report and set
its Picture property to a string concatentating the path value to the
filename.
Click to expand...

Hi John

Your advice makes sense. However, by following your guidance if I move the
folder where the images will be stored will I only have to change the
tbleImagePath code once (as every entry will have a different image)?
 
Hi John

Your advice makes sense. However, by following your guidance if I move the
folder where the images will be stored will I only have to change the
tbleImagePath code once (as every entry will have a different image)?
Click to expand...

Yes. The data table will contain nothing concerning the path - only the
filename. tblImagePath will contain only one record, with the path. You'll
combine them as needed, so there's only one place that you need to make the
change.
 
In message <[email protected]>, John W. Vinson
Yes. The data table will contain nothing concerning the path - only the
filename. tblImagePath will contain only one record, with the path. You'll
combine them as needed, so there's only one place that you need to make the
change.
Click to expand...

If the photographs are stored in a hierarchical folder structure it's
possible to store the path to a particular file in two pieces. That
would be the base path and the path from the base to a particular file.
That way if the entire folder structure is moved to a different location
there is still only one piece of information that needs to be changed.
 
In message <[email protected]>, John W. Vinson


If the photographs are stored in a hierarchical folder structure it's
possible to store the path to a particular file in two pieces. That
would be the base path and the path from the base to a particular file.
That way if the entire folder structure is moved to a different location
there is still only one piece of information that needs to be changed.
Click to expand...

Absolutely.

Even if you have several folders (e.g. for different projects), it's only a
little bit more work to have multiple records in the tblImagePath table, one
for each folder. The image display query would need to select the proper
record from the table.
 
Okay i will have a practice on a small data set and see how i get on. Thanks
for all your help and advice (John, fredg, Fred and Bernard).

Adrian
 
John

Is the realationship TbleImagePath (many) to Picture (one)?
Click to expand...

No relationship at all. Since tblImagePath has only one record, you can just
include it in the query with no join line - the one value will be available.
 
Thanks John

I have made the changes you have suggested.

I have a table (TableImagePath) containing just G:\VinylImages\

and in the other table (VinylTable) i have rows including the text row
called 'Picture' that contains the image name e.g., Vinyl1.jpg

If i put G:\VinylImages\Vinyl1.jpg in the VinylTable and make a form and set
an Image control to 'Picture' the jpg shows up (brilliant!).

When i concatenate TableImagePath and Picture and get the expression to show
G:\VinylImages\Vinyl1.jpg. However this does not show any jpg?

Could you please try and help me find out where i am going wrong.

Thanks

Adrian
 
Thanks John

I have made the changes you have suggested.

I have a table (TableImagePath) containing just G:\VinylImages\

and in the other table (VinylTable) i have rows including the text row
called 'Picture' that contains the image name e.g., Vinyl1.jpg

If i put G:\VinylImages\Vinyl1.jpg in the VinylTable and make a form and set
an Image control to 'Picture' the jpg shows up (brilliant!).

When i concatenate TableImagePath and Picture and get the expression to show
G:\VinylImages\Vinyl1.jpg. However this does not show any jpg?

Could you please try and help me find out where i am going wrong.

Thanks
Click to expand...

Sure. Please post your code; it's a bit hard to tell where you're going wrong
when you don't tell us where you're going!
 
Back
Top