ACCESS FEED: Re: [MS_AccessPros] Adding Photographs or Links to Photographs to a Collection Database

Hi Michael,

glad you had a "eureka" moment :) Attachments are fine if that what works for you now. When and if you want to save the attachments as files again, then you will need to write code ... sometime in the future -- so don't worry about it today :)

> Are you suggesting that I set up my photos the same way - by setting-up a separate table
> with the list of file names for the photos
> and then a selecting the appropriate photo file name from that table on my "main" table?

not exactly sure what you are asking ... but my recommendation is to make one (or more) table for attachments and put all attachments there -- then don't use attachment fields in tables with the rest of your other data. If your attachments table gets really big, you can put it into its own back-end and link to it :) I number all of my tables so each has a TID (TableID). Every table to share this utility will also have a Long Integer (or AutoNumber) that is that is a Primary Key or has a Unique Index.

In the attachments table, store:

- TID - table this attachment belongs to -- FK to your list of Tables and their TIDs
- RecID - PK of the table so you know what record this attachment belongs to
- attID, autonumber, PK
- Pic, attachment
- etc

WHERE
attID is the name of your primary key -- you can change this, of course :)
Pic -- whatever the name of your attachment field is

Warm Regards,
Crystal

Learn Access on YouTube
http://www.YouTube.com/LearnAccessByCrystal

*
   (: have an awesome day :)
*

________________________________
From: Michael K. Davis <abbsgen@gmail.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, August 29, 2012 9:14 AM
Subject: RE: [MS_AccessPros] Adding Photographs or Links to Photographs to a Collection Database

Crystal and John:

After sending my "frustration" email last night, I had a "eureka" moment. My
difficulty was in updating my existing form to reflect the Attachments that
I had added to my existing table (I assumed that Access would do this
automatically). Last night I gave-up on the existing form and created a new
form from the modified table; and, low and behold, the Attachment photos
appeared in the new form. This is the functional outcome that I wanted.
However, I am concerned about Crystal's warning re the ability to migrate my
database with Attachments.

Crysal- Currently my table and form have "drop down lists" linking to data
on other tables (i.e. customer list and vendor list). Are you suggesting
that I set up my photos the same way - by setting-up a separate table with
the list of file names for the photos and then a selecting the appropriate
photo file name from that table on my "main" table?

Thanks for the help and suggestions.

Regards,
Michael

-----Original Message-----
From: Crystal
Sent: Wednesday, August 29

John, thanks for explaining ;)

Michael, I understand you are scared off by code -- don't look at that part
first ...
Attachment fields are easy, now, but what will you do when you need to move
data out of them?
You have to put time in, might as well do it now.

In my example, there is ONE table that stores:

- full path and filename -- complete reference is here, no need to do
anymore OR -relative path (represented by \ at beginning) and filename to be
rendered on the form when the path is input on the top. When the form
opens, it assumes the path is:
c:\databasepath\Images

where
databasepath is CurrentProject.Path but it is not calculated that way ... it
uses code that also works in Access 97. Really there is not that much to
it. I have some custom functions to calculate things like PathFromFilename
-- all you need to know is how to call it and use it -- IF you take
advantage of relative path referencing.

open the table and look at the data.
modify the sample data to be paths and filenames of stuff that you have

instead of starting with \ to be relative, use full path and filename to
stuff that you have -- that is so easy!

----------------------------
Extract everything from the ZIP file into one place. the Images directory
will be where it needs to be. I have some sample JPG files there -- put
YOUR files there. If you have a bunch of them, like hundreds, they can be
loaded VERY quickly using data generated by my ListFiles utility

ListFiles Database
by and modified by Crystal
http://www.AccessMVP.com/strive4peace/DB_ListFiles
_________________

There is ONE form shat shows what is in the table and also an image
control. The image control has a calculation for the ControlSource - but
that is only because \Filename.jpg might be stored instead of
c:\path\filename.jpg
If that is too confusing for you, simply make the source of the image
control = fieldname with path and filename instead of the equation. Easy!
then you are ready. Table has full paths and image control uses table
without any calculations

~~~

Look at the design view of the form. First go to the Builder ... for
the form RecordSource and see if any calculations are in there. There
is one. Notice that * is used to get all fields from the table.

On the form, the gray fields are calculated and you can't change them. The
only thing you can do is change the Path\Filename, and look at the
pictures. When you open the form, you should see them if you extracted
all the files to the same place.

Michael, spend a couple hours looking at what I posted. I have spent more
time explaining this because I feel it is your best way to go. Gotta run
now

Good luck!

Warm Regards,
Crystal

*
   (: have an awesome day :)
*

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, August 28, 2012 10:17 PM
Subject: RE: [MS_AccessPros] Adding Photographs or Links to Photographs to a
Collection Database

Michael-

That's why I suggested the Attachment data type - no code required. If
you're
always going to use this as a desktop database, then no worries.

I haven't looked at Crystal's code, but it probably automates finding and
loading the file path to the picture. That code can look a bit daunting to
a
"total beginner." As Crystal points out, using a Text field to store the
location of the picture is much more efficient and is portable to SQL Server
and
the web. As of 2007, Access has a Control Source for the Image control.
Point
that to your text field containing the file path, and the control will load
the
picture for you on a form or report. The hard part is getting the correct
file
path stored - which is why you need the "scary" looking code. If you're
willing
to type the file path directly in the text field, then you don't need that
code.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Michael K.
Davis
Sent: Wednesday, August 29, 2012 3:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Adding Photographs or Links to Photographs to a
Collection Database

Crystal:

I just looked at what you uploaded. Thanks for doing that. Unfortunately,
I'm a total beginner with Access, with no programming experience; so, your
upload looks like gibberish to me! I would not know where or how to begin
implementing such a solution - although I am sure it is quite elegant. I'm
a little frustrated with Access because I am able to design a database that
gets me 50% of what I want, but the missing 50% seems out of reach without
programming knowledge.

I just realized that I have one of John Viescas' books (Access 2010 Inside
Out). Maybe I should read through that again, but I did not recall much on
photos/images the last time I looked at it.

Regards,
Michael

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Tuesday, August 28, 2012 11:54 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Adding Photographs or Links to Photographs to a
Collection Database

Hi Michael,

you're welcome -- guess it is already in Files>Crystal

Images_Access2007_070721.zip
Uses form f_Images to browse for a file and display the corresponding image
in a continuous form. Data is stored in table t_Images -- and what is
stored are the path and filenames to the images, not the images themselves.

Well I just uploaded a new version here:
ImageControl_120828_070721_Crystal__Accdb_TXT_JPGs.zip
uses Image Control to show external files on a continuous form.
FilePathName can start with "\" to be relative to current database
location. ie: c:\myDatabasePath\Images\Picture.JPG would be stored as
\Picture.JPG and rendered when the form is shown. Path to use is in an
unbound textbox at top of form - calculated on Load. Includes Images
directory with sample JPGs

> functional difference between an attachment vs. a link?

I think links are easier to deal with ... and it certainly makes the
database easier to move :)

Warm Regards,
Crystal

*
   (: have an awesome day :)
*

________________________________
From: Michael Davis <abbsgen@gmail.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, August 28, 2012 7:33 AM
Subject: Re: [MS_AccessPros] Adding Photographs or Links to Photographs to a
Collection Database

I am certainly interested in flexibility for the future. It is conceiveable
that I might want to sell the collection someday by creating a website. Is
there any functional difference between an attachment vs. a link?

Michael
On Aug 28, 2012 9:16 AM, "Crystal" <strive4peace2008@yahoo.com> wrote:

> Hi Michael,
>
> I suggested an alternate method using linked files. Before you use
> attachments, consider if you will ever want to move the information
> out of an Access desktop database. Linking is more flexible for the
> future
>
> I know I have a sample database on this, I will look for it and post
> it in
> Files>Crystal when I find it ...
>
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> ________________________________
> From: Michael K. Davis <abbsgen@gmail.com>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, August 27, 2012 10:16 PM
> Subject: RE: [MS_AccessPros] Adding Photographs or Links to
>Photographs to a Collection Database
>
> John:
>
> I am not sure how to get an Attachment control onto my form. I added
> the Attachment control to the Table, but I created the Form before I
> added the Attachment control to the Table. Do I need to create a new
> Form to bring over the photos, or can I update my existing Form to get
> the same photo attachments?
>
> Thanks,
> Michael
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John
> Viescas
> Sent: Sunday, August 26, 2012 1:19 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Adding Photographs or Links to
> Photographs to a Collection Database
>
> Michael-
>
> I assume you're using an Attachment control on your form, correct? If
> it works in the table, it should work on your form. But the OLE error
> message implies that maybe there's a problem with the default program
> you have defined on your machine to handle that file type. What
> program opens when you double-click the photo file in Windows Explorer?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007
> Inside Out Building Microsoft Access Applications Microsoft Office
> Access 2003 Inside Out SQL Queries for Mere Mortals
> http://www.viescas.com/ (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Michael
> Davis
> Sent: Saturday, August 25, 2012 11:29 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Adding Photographs or Links to
> Photographs to a Collection Database
>
> Yes, in my database Table, I successfully loaded some photos to the
> Attachment field. I then went to my Form and linked the new photo
> field to a spot on the form. However, when I click on that part of the
> Form, I get an error message (something about an OLE problem). So
> right now, I can only access the photo file from the Table, which is
> not the best GUI to work with. How can I also get the photo link to
> work in the Form?
>
> Regards,
> Michael
> On Aug 25, 2012 5:14 PM, "John Viescas" <JohnV@msn.com> wrote:
>
> > Michael-
> >
> > Click the Paperclip to get a menu to go find and load your photo.
> > If you bind the field to an Attachment control in a form or report,
> > it will display your picture.
> >
> > VBA = Visual Basic for Applications - the powerful coding language
> > built into Access. There are also macros to automate things, but to
> > call the Windows open file dialog, you need VBA.
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007
> > Inside Out Building Microsoft Access Applications Microsoft Office
> > Access 2003 Inside Out SQL Queries for Mere Mortals
> > http://www.viescas.com/ (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Michael K.
> > Davis
> > Sent: Saturday, August 25, 2012 9:24 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: RE: [MS_AccessPros] Adding Photographs or Links to
> > Photographs
> to
> > a
> > Collection Database
> >
> > John:
> > Thanks for the helpful reply. Strangely, Access would not let me
> > change the data type of my "Photograph" field to "Attachment"
> > (although it would let me change the data to other types), so I
> > deleted the "Photograph" field from my Table and created new fields
> > for the Attachments. However, now I find
> the
> > field is not labeled with the name that I selected, instead the
> > table
> just
> > shows a paperclip icon in the title field, as well as in each row
> > (guess I'll have to remember which photo goes where).
> >
> > Ideally, I would like to have Access retrieve copies of the photos
> > in my Forms and Reports. I can live with clicking on the Attachment
> > icons if necessary, but it does make for an extra step in enjoying my
collection.
> > What is "VBA" which you indicated would be needed to accomplish
> > photo retrieval?
> >
> > Regards,
> > Michael
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John
> Viescas
> > Sent: Saturday, August 25, 2012 12:36 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: RE: [MS_AccessPros] Adding Photographs or Links to
> > Photographs
> to
> > a
> > Collection Database
> >
> > Michael-
> >
> > Consider using the Attachment data type to store your pictures.
> > That'll
> be
> > easiest for a "newbie". You *could* store the path to the picture
> > and display it in an Image control on your forms and reports, but
> > you would need some complex code to call the Windows Open File
> > dialog to make it easy to load the path to new pictures. If you
> > want to go the more complex route, let me know, and I'll give you
> > some sample code (requires VBA).
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007
> > Inside Out Building Microsoft Access Applications Microsoft Office
> > Access 2003 Inside Out SQL Queries for Mere Mortals
> > http://www.viescas.com/ (Paris, France)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > Michael Davis
> > Sent: Saturday, August 25, 2012 6:19 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: RE: [MS_AccessPros] Adding Photographs or Links to
> > Photographs
> to
> > a
> > Collection Database
> >
> > Sorry John, I'm using Access 2010 on a Windows 7 Ultimate machine.
> Thanks.
> >
> > Michael
> > On Aug 25, 2012 12:04 PM, "John Viescas" <JohnV@msn.com> wrote:
> >
> > > Michael-
> > >
> > > Which version of Access are you using? That makes a difference.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out Microsoft Office Access
> > > 2007 Inside Out Building Microsoft Access Applications Microsoft
> > > Office Access 2003 Inside Out SQL Queries for Mere Mortals
> > > http://www.viescas.com/ (Paris, France)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > Michael
> K.
> > > Davis
> > > Sent: Saturday, August 25, 2012 5:51 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Adding Photographs or Links to
> > > Photographs to a Collection Database
> > >
> > > Hi all. I'm a new member and a beginner with Access. I have
> > > been designing a database to manage a hobby collection. I could
> > > have purchased a pre-developed database for this purpose, but none
> > > of those available had the flexibility that I was looking for. As
> > > I am a true beginner (never worked with a database before) I
> > > expect to have lots of questions for this group.
> > > I have set up a basic working database, but need to add a few
> > > bells and whistles.
> > >
> > >
> > >
> > > One thing that has me baffled is how to add photographs, or at
> > > least links to photographs, to my Access database. Ideally, I
> > > would like to see a thumbnail photo of the collection item on
> > > screen with the data for that item. How can I do this? Also, is
> > > it more advisable to use links to the photos, rather than the
> > > photos themselves, to reduce the size of the database?
> > >
> > >
> > >
> > > Thanks in advance,
> > >
> > > Michael
> > >
> > >

[Non-text portions of this message have been removed]