Pictures/Images in Tables

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).

"John W. Vinson" wrote:

On Wed, 1 Jul 2009 00:08:01 -0700, adrian007uk
wrote:

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).

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.
--

John W. Vinson [MVP]

On Thu, 2 Jul 2009 00:08:01 -0700, adrian007uk
wrote:

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).

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 [MVP]

"John W. Vinson" wrote:

On Thu, 2 Jul 2009 00:08:01 -0700, adrian007uk
wrote:

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).

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 [MVP]


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)?

On Thu, 2 Jul 2009 14:46:01 -0700, adrian007uk
wrote:



"John W. Vinson" wrote:

On Thu, 2 Jul 2009 00:08:01 -0700, adrian007uk
wrote:

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).

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 [MVP]


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)?

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.
--

John W. Vinson [MVP]

In message , John W. Vinson
writes


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)?

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.

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.



--
Bernard Peek

On Fri, 3 Jul 2009 10:35:22 +0100, Bernard Peek wrote:

In message , John W. Vinson
writes


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)?

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.

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.

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.
--

John W. Vinson [MVP]

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 W. Vinson" wrote:

On Fri, 3 Jul 2009 10:35:22 +0100, Bernard Peek wrote:

In message , John W. Vinson
writes


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)?

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.

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.

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.
--

John W. Vinson [MVP]

John

Is the realationship TbleImagePath (many) to Picture (one)?

Adrian

"John W. Vinson" wrote:

On Fri, 3 Jul 2009 10:35:22 +0100, Bernard Peek wrote:

In message , John W. Vinson
writes


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)?

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.

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.

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.
--

John W. Vinson [MVP]

On Tue, 7 Jul 2009 02:13:01 -0700, adrian007uk
wrote:

John

Is the realationship TbleImagePath (many) to Picture (one)?

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.
--

John W. Vinson [MVP]

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



"John W. Vinson" wrote:

On Tue, 7 Jul 2009 02:13:01 -0700, adrian007uk
wrote:

John

Is the realationship TbleImagePath (many) to Picture (one)?

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.
--

John W. Vinson [MVP]