Results 1 to 14 of 14
  1. #1
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8

    Cool Import Photos to access


    I have designed a database for a historical society and included a field to attach the relevant photos. However when all the data is entered there will be hundreds of photos. Is there a way to tell access to find and import the photo when the information on that photo is called up. I there a command I can put in the database instead of the "attach" command that I have at present. If all the photos end up attached to the database itself it will make it a huge file.

  2. #2
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Instead of importing photos into db (like OLE or attachment field), store files outside of db (which you're doing anyway I guess) and store only paths to them in text field in db. You will need means like FileOpen Dialog to select photos to be stored, FollowHyperlink or Shell command to open them, or creating another form with dynamically changed Image object to display photos. Start with reading about those functions and come back when you get stuck.

  3. #3
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    Thank you, I will try again. When I tried to add a shell command, I got an error message that the syntax was not correct.

  4. #4
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Well, it's slightly different from simple attachment field as you have to program everything yourself, but you're better off in the end and have more flexibility managing those photos. If you have more questions about process (you will) don't be afraid to ask here but try to be more specific. I.e. show us code that's returning error, etc.

  5. #5
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    The error message is "the expression you entered contains invalid syntax" "You have entered an operand without and operator. I entered this in the Default Value line. I have also realised that I have another problem. How do I direct the request to the same Category and Photo no as I have entered in the data. The expression I entered was: =get"C:/Margaret/Documents/ClarenceRiverMuseum/Photographs/B001" There will be at least five different catagories of photos and the photo that is called up must be the same category and number as entered in the data. Is this possible or should I just stay with attaching the photos

  6. #6
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Almost everything is possible, just more or less complicated.
    For starters, I don't understand what you're doing now and where. Default Value of what? Where did you get that =get"C:/...... expression from? Syntax of it and path itself look invalid at first glance. Tell us more, show us screenshots and/or upload your db (after stripping confidential data, using "compact and repair" and compressing it.

  7. #7
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    Thanks. It is very late here. I will get back to it tomorrow.

  8. #8
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    Here are the screen shots.Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	64.8 KB 
ID:	23919Click image for larger version. 

Name:	Capture2.PNG 
Views:	24 
Size:	18.7 KB 
ID:	23920Click image for larger version. 

Name:	Capture3.PNG 
Views:	24 
Size:	158.3 KB 
ID:	23921Click image for larger version. 

Name:	Capture4.PNG 
Views:	22 
Size:	86.0 KB 
ID:	23922
    Quote Originally Posted by cyanidem View Post
    Well, it's slightly different from simple attachment field as you have to program everything yourself, but you're better off in the end and have more flexibility managing those photos. If you have more questions about process (you will) don't be afraid to ask here but try to be more specific. I.e. show us code that's returning error, etc.

  9. #9
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    I posted the screen shots as you suggested. I tried to post a compressed version file but it would not let me.
    1. in the Ättachment" cell I need a pointer to the Photo instead of attaching the actual photo. the photos will all be in separate folders on the hard drive.
    2. I also need to be able to create reports on any of the data entry headings and only get that information.
    3. Also is there a way to create a report that will tell me the number of photos in any category.
    4. The information in this file will be entered at times by people with little computer experience and probably no knowledge of Access so I have to make it easy and user friendly.

  10. #10
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    First - there's no "cells" in Access. It's a field in a table. Cells are in Excel and you can't think of Access as a "bigger Excel".
    But to the point:
    1. Let's assume that your photos are in subfolder of where your database is, called "Photos". All are in same format (let's say .jpg). You need to create text field containing ONLY photo file name (instead of attachment field), i.e. "Photo001", "Photo002", etc. Even easier would be to name them after ID field, in that case you wouldn't need to create any additional fields, simply concatenate strings, i.e. PhotoFileName="Photo" & Format([ID],"000") & ".jpg". You'll need unbound image control on your form and reports, and the Picture property for it will be something like Image1.Picture=CurrentProject.Path & "\Photos" & PhotoFileName.
    That's all simplified version of course, you may want to use FileOpen Dialog to select photos or do other things, but that's to make you start.
    2. I'm not sure what are "data entry headings"
    3. Use DCount() function or aggregate queries.
    4. Good approach, it's all up to you how friendly it will be. Do it

  11. #11
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    Did you find the screen shots that I uploaded? Can I put in a command that will get the computer to look in the fields where I have input the category name and the Photo ID numbers, and then direct it to find the photo. That way there need only be one command format that can be repeated in every entry. Where do I put the command so that it will automatically be put into the data entry screen?

  12. #12
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Well, this approach requires some VBA code. You will need to use form OnCurrent event to check what is category and photo ID then concatenate image path to load on the form. As I said, it's slightly more complicated than just selecting attachment file and then displaying it on the form, as you need to code selecting and displaying process yourself. If you're not familiar with VBA it may be quite steep learning curve.
    Also, looking at your screenshots - you should avoid data input straight to tables, use forms for this. You should also avoid using spaces and other special characters (underscore being exception) in field names and generally object names in Access, it seems ok at the start but when VBA is involved it may cause all sorts of trouble.

  13. #13
    margml is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    8
    Click image for larger version. 

Name:	Capture5.PNG 
Views:	19 
Size:	22.5 KB 
ID:	23929Click image for larger version. 

Name:	Capture6.PNG 
Views:	19 
Size:	19.4 KB 
ID:	23930I tried putting in a command using the "Expression Builder" in Access. What I typed was the command showing in the screen shot. I got the error message showing in the second screen shot. Am I on the right track or completely lost.

  14. #14
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Unfortunately you're not on the right track.
    Never mind the fact that your string is totally messed up (double quotes opened but not closed, field name enclosed in braces on one side and square bracket on the other, etc.).
    As I said before, you'll need VBA and events for that. Don't put anything in default value property of your new field, leave it empty. Open your form in design mode, place unbound Image object on it. In property window of form look at Event tab. There will be event called "On Current". Click three little dots next to it, select "Event procedure" if asked, that will open VBA editor with something like:
    Code:
    Private Sub Form_Current()
    
    
    End Sub
    Between those 2 lines goes your code. You want Image to be filled with your photo. So code will be something like:
    Code:
    Private Sub Form_Current()
        Me.Image1.Picture = "C:\Users\marlee\Documents\Clarence River Museum\Photos\" & Me.Field3 & Me.Field2 & ".jpg"
    End Sub
    You really need to get some grasp of VBA first, I'd suggest watching some introductory videos or courses. And of course ask here if you can't understand something.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-18-2016, 03:30 PM
  2. Import Photos
    By balajigade in forum Import/Export Data
    Replies: 3
    Last Post: 06-01-2014, 01:34 AM
  3. Replies: 6
    Last Post: 02-06-2013, 06:10 PM
  4. How to put photos as records in access 2003
    By Klaudio in forum Reports
    Replies: 1
    Last Post: 09-07-2012, 12:06 PM
  5. Inserting photos as records in access 2003
    By Klaudio in forum Access
    Replies: 5
    Last Post: 09-07-2012, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums