Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Microsoft Access Imaging Seminar
Work with Images and Objects in Access

 
This seminar will teach you how all about working with images, pictures, graphics, OLE objects, attachments, and pictures from the Web. You will learn how to store pictures in your database, and how to work with images that are NOT stored in your database. You'll also learn how to work with the new Attachment data type.

This seminar came about because one of my customers had a need to work with a large number of images in his database, but storing pictures in an Access database is NOT a very efficient use of space. Your database gets big and slow very quickly.

So, in this seminar, I will show you a technique for selecting a file by browsing your drives, copying that file to an Images folder, and then displaying that image in your database forms and reports WITHOUT storing it in the database.

But... we don't stop there. Since we're going to learn about images, we might as well learn everything about images. We'll start with basic OLE objects (bound, unbound, linked, embedded, etc.) and move right into VBA coding for Image controls. You'll learn about the new Attachment field type in Access 2007 (but don't worry, almost everything else is perfectly valid for Access 2000 and 2003 users) and lots more.


 

 
Seminars - Access Imaging
Description: Learn how to work with images and other types of objects in your Access databases.
Versions: I will use Access 2007, however most of the lessons are valid for all versions of Access back to 2000 and up to at least 2013. I will show any differences between 2007 and 2003. The lesson on Attachments is specific to 2007. The lessons on selecting files require 32-bit versions of Access. We cannot guarantee compatibility with 64-bit Access. Read this.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 3 hours, 32 minutes
Cost: $89

 

This seminar has several main goals. You will:

1. Learn about the different types of OLE objects
2. Discuss the most efficient way to work with images
3. Embed and Link OLE objects into your tables and forms
4. Work with non-image objects (spreadsheets, documents)
5. Display external images in your forms & reports
6. Browse your hard drive to select a file
7. Copy files to a central "Shared Images Folder"
8. Display images found on Web sites in your Database
9. Work with the new Attachment type in Access 2007
 

We will begin by learning about all of the different types of objects you can use in Microsoft Access. You'll learn about bound and unbound OLE objects, images, linking vs. embedding, non-image objects, when and when NOT to store images in your database, and you'll learn about the new Attachment data type.

 

Next we'll build a table and form to store embedded OLE objects right in our database tables. This is OK if you don't have a very large number of images to work with. You'll see how easy it is to edit these images in place.

 

In the next lesson, you'll see how easy it is to work with non-image objects in your database. We will embed a Microsoft Excel spreadsheet into a database form and give our users the full functionality of Excel without ever leaving Access.

 

Next you'll see how we can embed a company logo ONCE in a single form, and then use that same image in all of our other forms and reports without making separate copies of it. This saves a lot of space in our database.

 

We will then learn how to work with the Image control. This allows us to specify the path and filename of a picture file on our hard drive (or network) and view the image without having to store it in the database.

 

 

Next we're going to look at the Microsoft Common Dialog Control. This was an ActiveX control that shipped with Access 2003 and earlier, but is no longer available in 2007. It allows you to quickly and easily browse for a file on your hard drive.

 

Don't worry, Access 2007 users. I've got a solution for you. Using a little bit of VBA code, we can access that Common Dialog window without a special ActiveX control.

 

Once we learn how to select a file, now we can copy that file up to a central Shared Images folder (or just your database folder if you prefer). This way we can keep all of our images together in one place, and even rename them if we want. If you're working on a network, you want to put your images where everyone has access to view them. Don't panic... I will explain all of the VBA programming code you need to know to get this to work.

 

Next you'll see how you can give your end-users the ability to actually EDIT those pictures using Microsoft Paint. Even though the images aren't stored in the database, we can launch Paint and load up the pictures with just a few lines of VBA code.

 

If you have a Web site with images on it, you can view those images and use them in your Access database forms and reports. This is especially handy if you already have all of your product pictures online. Just point to them, and voila...

 

Next, Access 2007 users will learn how to use the Attachment data type. You can store multiple objects (pictures, text files, documents, spreadsheets, you name it) as attachments to database records... just like Email attachments.

 

 

This seminar is perfect for anyone who wants to learn how to work with images, pictures, or any kind of objects in Microsoft Access databases.

This seminar is long (over three hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish. 

All of the sample database files for this seminar are available on my Web site (instructions on where to download them are in the course videos). They are available in Access 2007, 2003, and 2000 formats. Click here to download the sample database files.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first lesson absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Imaging Seminar Outline

0. Welcome (14:40)

1. Intro to Objects (14:04)
OLE Objects v. Images
Bound v. Unbound OLE Objects
Linking v. Embedding
When and when not to store objects
Access 2007 Attachments

2. Database Setup (2:41)
Creating the Database
Customizing Options
Trusted Locations

3. Table Form Setup (5:41)
Setup Employee Table
Build Employee Form

4. Bound Embedded OLE Objects (10:02)
Insert Object
Icons v. Full Images
Edit or Display Object
Changing Registered Program Type
Zoom, Clip, Stretch

5. Linked OLE Objects (9:42)
Linking to Objects
File Folder Considerations
Shared Network Folders
Editing Linked Copies

6. Non Image Objects (6:55)
Excel Spreadsheet Fragments
Links to Word and Excel Files
Employee Report

7. Unbound OLE Objects 1 (5:05)
Shared Linked Excel Sheet
Editable Sheet Stored in Form

8. Unbound OLE Objects 2 (8:56)
Main Menu
Shared Logo on Forms Reports
Editable Logo in Multiple Places

9. Image Control 1 (11:18)
Inserting an Image Control
Manipulating the Picture Property
Loading an Image from your Drive
Change Image Button
Loading an Image when the Form Loads
Empty Image Control Box

10. Image Control 2 (12:00)
Show Image on Bound Data Form
Change Image for Each Record in VBA
Load Picture from Server or C: drive
Creating Your Own Subroutine in VB

11. Common Dialog Control (9:52)
Up to Access 2003 Users Only
Use CD Control to Pick a File
Save Filename in Field
Does Not Work in Access 2007

12. Common Dialog DLL (9:08)
All Versions of Access
Use DLL Call to Pick a File
Works With ANY Version of Access
Location of Sample Database Files

13. Copy Files to Server 1 (8:22)
Create a SettingsT Table
DLOOKUP Folder Path
Change Display Code
Null vs. Empty

14. Copy Files to Server 2 (11:25)
Separate Filename from Path
GetFilenameOnly Function

15. Copy Files to Server 3 (12:34)
Determine if File needs copying
FileCopy command

16. Copy Files to Server 4 (9:17)
Ask to Copy if File Already Exists
Dir Function
Make EmployeeID part of Filename

17. Handling Errors (9:13)
Cancel on Select File
Picking a Non-Image File Type
Image Files Moved or Renamed

18. Editing Images (6:27)
Creating Your Own Edit Event
Shell Command

19. Embedding OLE with VBA (4:21)
Pick a File
Embed the File in OLE with VBA code

20. Display Images from Web (10:53)
WebBrowser Control
Determining an Images URL
Navigate to a Web URL
LoadPicture Subroutine

21. Attachments (10:57)
Added in Access 2007
Attachments in Table Fields
Pros and Cons
Attachment in Form
VBA to Work with Attachments
Count Attachments
Cycle Through them on Form

22. Image Report (5:22)
Create a Report with Image Control

23. Review (2:55)

 


 

 
 

Student Interaction: Access Imaging Seminar

Richard on 1/1/2007:  Learn how to properly work with images and graphics in your database. This 3.5 hour seminar covers: - OLE Objects, Linking, Embedding - Display external images in forms - Browse drive to select a file - Copy to a "shared images" folder - Embed non-image objects - Display Web-based images - New Attachment data type
Rick R on 3/7/2010: IMPORTANT NOTICE: There is an even BETTER WAY to display an image on a form. Here's a link to a free addendum video that explains everything.

IMAGING SEMINAR ADDENDUM VIDEO

I discovered this AFTER I finished the seminar.

Richard on 3/7/2010: PLEASE NOTE: The web page for the sample database files and source code can be found in LESSON 12, about 1:41 into the video.
 James on 3/8/2010: Great Class again Rick. Keep the VBA comming I love it. Great stuff here. I especially like the copy to server classes.
 Dave Sterner on 3/9/2010: I am not seeing the time index you mention by at about 3 minutes when you drop the image control on the form it looks like there is a significant difference between Access 2007 and 2003. With my Access 2003, when I add the image control it automatically starts to browse for a picture and when I click cancel in the browse window, the image box gets deleted. To get an empty box, I had to load a picture and delete it from the picture property of the image control just like in the previous lesson.
 Rupert Bennett on 3/9/2010: Hi Richard,
I hit a snag while working through the Images Seminar. I am using Access 2003 and am tyring to use the Common Dialog DLL. I am not saving to a server, so I have my VB Code as EmployeePicture = (me, "C:\Program Files\Microsoft Office\Office11\Samples")
I am getting the following error
"Wrong number of arguments or invalid property assignment"
Can you spot my mistake?

Thanks for your help.
Rupert

Reply from Richard Rost:

Rupert, remember you're working with the picture PROPERTY of your image control. Change your code to:

EmployeePicture.PICTURE = (me, "C:\Program Files\Microsoft Office\Office11\Samples")

 Dave Sterner on 3/9/2010: You might want to warn students about making the EmployeePicture field (the one that contains the path) text size larger than 50. I tried to load some jpg's from my MyDocuments\MyPictures folder and got an error message telling me my field was too small.

Reply from Richard Rost:

Thanks Dave. You just did. :)

 Dave Sterner on 3/9/2010: Correction on my previous comment about cancelling out of the image object not being able to get an empty image box - I have Access 2002

Reply from Richard Rost:

Yep. I think I mentioned that in the video for 2003 and earlier users.

 Rupert Bennett on 3/12/2010: At 08:25 in this lesson, we are comparing the length of the string in the Imagesfolder to determine if we need to copy up to the server. What if we are in another folder on the server, with the length of the PathFilename identical to that of the ImagesFolder? Would that not create a problem?

Reply from Richard Rost:

It's not just the length we're checking... we're comparing the beginning of the folder string to see if it's identical to the same length of string from the chosen file. I did think of one problem, however. If you have your Images folder set to:

C:\

Then any file you choose on the C:\ drive will show up as being in the Images folder for the purposes of this function. I suppose a better algorithm is needed. Until I have a few minutes to come up with one, just make sure your Images folder name is UNIQUE on your hard drive, like:

C:\Data\dbImages\

Or something like that - something that's not going to be easily confused with another folder.

Ray McCain on 5/6/2010: The image files which I am copying have been set up in multiple album amd sub-album folders, which I need to include in the complete pathfilename. How do I set up a mid string to identify everything between C:\Images\ and the filename and then be able to include it along with the pathname. It is also possible that no album name exists so I would need to check for an empty string and ignore it if one is found.

Reply from Richard Rost:

Hmmm... how about making "C:\images\" your root folder, and then just including any subfolder information either in another field, or as part of the image filename. You would then store your images relative to the root folder. For example:

joe.gif
subfolder\sue.jpg
another\subfolder\bill.gif

Would this work for you?

 Alan Hill on 5/6/2010: The launchCD function is just what I have been looking for. It even opens up at the correct folder. That's great. I love it.
Thankyou very much.

Reply from Richard Rost:

That's what you pay me for. :) LOL

Alan Hill on 5/6/2010: Hi Richard
Common Dialog DLL
I know the length of my filepath.
N = Len(filepath): P = Len(FullPath)
L = P-N
so I used ImageTitle = Right(FullPath,L)to get the ImageTitle
Seems to work OK
Just about to watch copy files to server

Best wishes Alan Hill

Reply from Richard Rost:

Good approach.

Ray McCain on 5/21/2010: Due to the use of multiple folders to set up the albums needed for this photo directory, I need to retreive the entire path all the way to FileFolder which ends with "Images\". Since the backslash is in all likelihood not the last one in the path name, I need to know how to rewrite the mid or instr functions to accomidate this.


Reply from Richard Rost:

You can start from the end of the string containing the path/filename to isolate just the pathname. I would start a loop that looks for that last backslash. Let's say you have:

S = "C:\Users\Richard\Images\something.jpg"

X = len(S)
While X > 0 and mid(S,X,1)<>"\"
X = X - 1
Wend

Now you have the position of the last "\" in the string. At this point you can cut off the filename, which you can do with:

S = left(S,X)

Now you have just:

S = "C:\Users\Richard\Images\"

If you want to get what's BEFORE the Images\ folder, you can now just chop that off:

S = Left(S,Len(S)-7)

Since you know that "Images\" is 7 characters. Now you're left with:

S = "C:\Users\Richard\"

Hopefully this is what you're trying to find. Now, I haven't fully tested this (it's off the top of my head) and I didn't add any error handling (what if there are no \ in the string?) but it should get you started.


 Lynn on 7/5/2010: So Far I have enjoyed the Access Imaging Seminar
Dan O'Neal on 12/17/2010: Richard, thanks for this seminar. I've got things working as needed by using code to target the needed images on a form and it works great on my computer, however, I need this to be portable. This is what I need....I'm using a variation between 5 different images on a form. Currently I point to the differing images using a separate button control to choose which image using VBA. AND it works great, but I'd like to store these images in the database without nessesarily storing each image in a record. Currently, if I use this database on another computer, the path is changed because of different users, so I need to include only five varying images within the database. I thought perhaps these 5 images could be stored in a table and changed by a control on the form, but after reviewing the seminar, just don't see how without imbedding each image into a record, therby inflating the database. How do I target a bitmap stored in a table (if this is even the right approach)Forgive me if this was covered in the seminar. Thanks in advance for any help. You da man!

Reply from Richard Rost:

If the images need to travel with the database, then yes, you should embed them in a table. It will inflate your database, but if that's what you've got to do for your solution, then that's what you gotta do. I did cover this in the seminar. It's not hard. You could just use one separate table for the images and then refer to them - you don't need a separate copy for each record in your main table. The only other solution would be to put them on a web server (if all of your users are online while using your database). Then you could just all point them to a URL for the images.

 Rick on 1/8/2011: Richard,

I just finished lesson 15. My employee picture does move to the shared images folder, but the selected picture does not display on the form anymore.

Great job on this course!

Thanks,
Rick

mohd bafgish on 5/14/2011: how i fix this problem please
wihen i click cancel in commondialog dll
in access 2007
show to me this message
A file was not selected

and this code inside button commond

Private Sub Command11_Click()

sourceImage2 = LaunchCD(Me, "C:\Users\Bafgish\Desktop")
updatepicture
End Sub

Reply from Richard Rost:

You should just be able to say:

if SourceImage2="" or IsNull(SourceImage2) then
msgbox "No image selected"
exit sub
end if

Put that right before your UpdatePicture line.

Gene Hightower on 7/9/2011: Richard, how to add multible images to a form and report?
Ellesha on 8/10/2011: Attachments Question

Hi Richard,

For access2003 Table DataType there is no Attachment. What should I do If I wanted to create a Attachment box which allow user to upload anykind (word. jpg. xcel attachment) to database?

Also, allow more than 1 attachment.

Thank you.

Alex Hedley on 8/12/2011: Hi Ellesha,

The Attachment is new to Access2007+ so you won't be able to do the same in 2003.

As Richard suggests in the Seminar you could just copy the file to a shared folder and save the file path in a field then open the file either with a Shell command or open the folder that it's to quickly find it.

Al

Lindray on 9/19/2011: I need help .... I made a form with a continuous subform where I put a vb code like this in the course of seminar image. the problem is that it shows the same image for every record, the right image shows only when I click on the record. the code is put into the subform as the rest of the code ..... This is the code

Private Sub Form_Current ()
Dim S As String

S = ImagesFolder
S = S & Image


If Not IsNull (Image) And Image <> "" Then
CustomerImage.Picture = S
else
CustomerImage.Picture = ""
End If



End Sub

Ann  Butler on 11/1/2011: I am getting message The operation on the OLE object failed. The OLE server may not be registered. To register the OLE server, reinstall it. ???
 Jimmy on 12/29/2011: i have another way to get the file name without the path with this function:

Public Function FileNameNoExt(strPath As String) As String
Dim strTemp As String
strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
End Function

i get it from this web site http://www.vbaexpress.com/kb/getarticle.php?kb_id=767

eddy geijselaers on 2/20/2012: On lesson 11/12:

For those who installed AC 2007 or 2010 on their System using an older version of MS Office like me (XP), the "Common Dialog Control" works the same as in the older versions of AC.

Still the lesson 12 settings are better, as Rick explains, especialy if you want to distribute your DB later.

eddy peanut

eddy geijselaers on 2/23/2012: Lesson 9;10;11

The Common Dialog Control works nice, but, one minor thing to get around --
Since I didn't have a solution for my LaunchCD problem I tried the CD.

After canceling in the browser it still pick's the first file in the folder.

How to prevent this to happen?

eddy peanut

eddy geijselaers on 2/24/2012: Hi,

I've got it fixed, seems to be something that was upsetting my system-setting. Everything works fine now.

eddy

Silvano Natalizi on 3/23/2012: This seminar is very good. I wanto to develop an application in access 2010. This application must create a word document. I spent the full day studying, without a result, the libraries to check on tools-references, but i get always the same error for the instruction dim doc1 as Word.Application "user defined type not defined". What can i do?
Thanks.

Silvano on 3/23/2012: I solved the first problem, i checked microsoft word 12.0 object library on references,
but again stopped. Very easy:
1) dim doc1 as word.application
2) set doc1=CreateObject("Word.application")
3) doc1.visible=true
4) with doc1
.selection.typetext("paperone")
end with
--->run time error 91 object variable or with block variable not set ?
Thanks for help.

Silvano on 3/23/2012: I solved the problem.
I add the instruction :
.Documents.Add
before .selection.typetext("something")

 Yolande Villeneuve on 5/23/2012: How do you do a server\SharedData\Images folder and link the table to the web site. I need that to be able to enter/or show the monuments in the original table

Please give me help ASAP as it is needed Presto.

Thanks for your support!

Reply from Richard Rost:

Hi Yolande. In this seminar, I really don't cover moving your images up to a WEB server. I just cover a normal FILE SERVER on your network. Uploading to a remote WEB server is a whole different ballgame. It's not super hard, but it involves some ASP programming.

Now, if your WEB SERVER is in your physical office and hooked up to your NETWORK (like one of mine is) then that's not a problem. Just set up the shared folder that you're using for your images on your WEB site. If you're using Microsoft Windows Server, you can do that in your IIS control panel (your IT person should know how). You can make any folder on your network a Virtual Folder in your web site.

If you have a web server in a DIFFERENT location (like hosted space on GoDaddy or some other ISP) then you'll have to upload the image files to that server. Setting up an AUTOMATED routine would take a good deal of programming. I could show you how, but it would probably have to be a separate seminar (at least a mini-seminar) in ASP.

If you upload the image files YOURSELF to the server using whatever web editor you have (FrontPage, Expression, etc.) then my Imaging Seminar DOES show you how to DISPLAY those images in your Access database (Lesson 20). But GETTING them there is the tricky part.

The Imaging Seminar is PRIMARILY for using Images WITH your ACCESS database - not a web site.

barry walt on 6/24/2012: Hi tryong to put a photo into my database form but it tells me (can't read the OLE because communication was interuped ) the photo and form are both on external drive
thank you barry


Reply from Richard Rost:

Barry, this is very hard to diagnose remotely. Does your code with with a very small file? It could be just a problem with BIG files - perhaps your interface is too slow. Does it work off of an internal hard drive? Sometimes USB drives are problematic.

 Sam Vodoor on 7/15/2012: Can Access display a Vector file image?

Reply from Richard Rost:

Not by itself, however if you have a program installed like CorelDraw or Illustrator you could store the image files from those apps as an OLE object and they would open up with a double-click. I don't recommend it though. Best to keep them external files with a link.

Dan on 10/15/2012: Hi Richard,

Great tutorials!

I am having a problem though. In video 12 at 7:00 minutes you discuss the code for the image button. However, my image button causes an error with the "LaunchCD". It works ok if I follow it with just "Me". If I add the optional path to begin looking, I receive the error "Wrong Number of Arguments or Invalid Property Assignment." I'm not sure what I'm doing wrong. I followed you tutorial to a "T". I'm using Access 2003.

Thanks,

Phil on 10/15/2012: My version is not copying correctly.

1) The path of the ImagesFolder is correct M:\Human Resources\HRIS\HRIMS_EmployeePhotos\

However, the File Employee Picture [File Name Returned] is not correct

It is HRIMS_EmployeePhotosEmployeeName.jpg

It should be EmployeeName.jpg

Also, it is copying to the wrong directory -

it copies to M:\Human Resources\HRIS

It should copy to M:\Human Resources\HRIS\HRIMS_EmployeePhotos\

Can you help?

Reply from Richard Rost:

I would need to see your database to tell you exactly what the problem is. I'm sure it's probably an incorrect or misspelled variable name or something similar.

Dan on 10/16/2012: I did some trial and error and was able to make it work.

Private Sub Command7_Click()

Dim PathFilename As String
Dim FilenameOnly As String
Dim L As Integer

L = Len(ImagesFolder)
PathFilename = GetFileNameOnly(LaunchCD(Me))
FilenameOnly = PathFilename

If Left(PathFilename, L) <> ImagesFolder Then
'FilenameOnly = EmployeeID & "-" & FilenameOnly To Add claim number to photo.
If Dir(ImagesFolder & FilenameOnly) <> "" Then
If MsgBox("File Already Exist, Overwrite It?", vbYesNo) = vbNo Then
Exit Sub
End If
End If

FileCopy PathFilename, ImagesFolder & FilenameOnly

End If

Me.EmployeePicture = FilenameOnly

UpdatePicture

End Sub

Phil on 11/9/2012: I am having a problem with the Images Seminar. I followed all the instructions and my database worked fine. Pictures uploaded and copied to the server file as designed. However, when I followed the instructions in the 2007 addendum that told me to delete all code my picture loads to the database but it no longer copies to the server file. The table just stores the address of wherever the picture was selected. I still want the images to copy to the server.

Reply from Richard Rost:

This is one of those things where it's almost impossible for me to help you without seeing your setup. If it worked before, it sounds like you might have accidentally deleted too much? Try recreating the code again and see where the problem is. Without walking you through EVERY STEP, I can't tell what happened from here.

Ricardo Tolliver on 2/18/2013: I got the image control to work on a form but not on a report. I need to be able to print the image and information, which is better to use for print individual record information.

Reply from Richard Rost:

It should work - it's basically the same control. I'd need to probably see your database to tell you what the problem is... but whenever you want to PRINT something, you should use a report.

Ajantha Rathnayake on 3/2/2013: Richard, can a report display a web image? if so how? I tried webbrowser control in report and it didn't work. Please help.
Thanks Indu

Reply from Richard Rost:

You cannot put a Web Browser Control in a Report or a Continuous Form. I don't know of any way to display a live web image in a report. You would need to download the image locally and then display it.

Brian Bradford on 5/22/2013: Hi Rick,

First of all, your videos have been extremely helpful and we really appreciate the aid you've provided in setting up our database.

We have a question. We are creating a database to store hundreds or even thousands of fairly large product photos, and we've created a database using your "image control" and "copy files to server" methods. We have photos in dozens of folders, and we created one new "shared" folder to hold the copies of the image files used in our database. However, our employer is concerned that making copies for all of these photos could take up too much space on the company server. They also do not want us to move/delete any of the pictures from the original folders.

Is there a possible work-around that you could think of for this issue? I understand that it is a complex situation and may not be easily solved, but we were wondering if you had any thoughts that could point us in the right direction. Thanks so much for your help and for your tutorials, which have also been extremely useful in creating our database.

Cheers!
Brian

Reply from Richard Rost:

Well, you don't necessarily have to copy all of the photos to a new folder. If they're already in a server folder that you can share and access via the database, just leave them where they are. Pick the file, save the path/filename in the database, and use the original.

John Bosko on 5/27/2013: Your videos are great. I like your explanations.

Reply from Richard Rost:

Thanks.

Brian Bradford on 5/28/2013: Hi Rick,

Is there any way to set the default folder for the common dialog (upon using the "select image" button) to be the last folder that was opened, rather than the same folder every time?

Reply from Richard Rost:

Honestly, I'm not sure. I've never done it. I'd have to research it for you. Anybody else try this?

Brian Bradford on 5/28/2013: We figured it out!

This is the page that helped us (under the section "lpstrInitialDir"):
http://msdn.microsoft.com/en-us/library/windows/desktop/ms646839(v=vs.85).aspx

We went to the "Common Dialog" module that you had us create using VB code copied from Microsoft's website, then we simply deleted the following line of code:
OpenFile.lpstrInitialDir = InitialFolder

Reply from Richard Rost:

Awesome. Glad you figured it out. Thanks for sharing!

Anne Tschider on 6/12/2013: is there a way to collapse the image field if it is empty so that that record's space in the report doesn't take up the height of a picture (a log of white space in the report)

Reply from Richard Rost:

Set the image's CAN SHRINK property to YES, as well as the DETAIL SECTION's can shrink property too.

Anne Tschider on 6/13/2013: I am using Access 2010. When I load the module provided in this lesson (599CD.com?XQV89J), it generates a compile error indicating I need the 64 bit version in the declare statement. How do I obtain the 64 bit version, or fix the error?

Reply from Richard Rost:

See this page (and the comments at the bottom) for help.

Mubeezi Micah on 6/14/2013: Dear Richard,

How can i know if the Access 2007 i have on my computer is 64 or 32 bit?

MICAH

Alex Hedley on 6/15/2013: Which folder is it in on the C:\
Program Files (x86)?

Brian Bradford on 6/22/2013: Hi Rick,

In my database, I'm trying to generate a report that displays all of the images the meet certain criteria using a query. I've already generated a report that displays all of the images and it works great, but this query report (with all of the same formatting and properties) is displaying about 6 of each picture instead of just one. In the datasheet view of the query, however, each value only shows up once; there are no repeats.

I've tried setting the "Unique Values" and "Unique Records" properties to yes for the query and neither of those changes have solved the issue. Do you have any idea what might be causing the issue?

Reply from Richard Rost:

Brian, I can't tell what the problem is without seeing the database. If the underlying query shows the proper number of records, I can't imagine why the report would be showing extra copies.

Elisa S on 7/24/2013: First of all, great seminar. I learned a lot and really appreciate your user friendly layout and explanations.
The primary reason for my interest in this seminar was to learn how to link multiple objects into a database - specifically into a table - using VBA code/macros so that not only would the 3000+ entries already in the database have pictures linked to them, but any future entries would also follow suit. Not my first layout choice by far but it's not my database and the owner is against design changes. Thoughts?

Reply from Richard Rost:

What kinds of objects are we talking about? I would recommend the method shown in the seminar (and HERE) to save the filename in the table and store the files in a folder on your server.

Elisa S on 7/26/2013: Here is my challenge: An oversized database with well over 3000 entries for items in an inventory. The bulk of the size is, of course, the embedded images for each entry. My task is to reduce the size of the database by linking the images rather than having them be embedded. I also need to fix the database so that future entries are not embedded and start things in the wrong direction all over again. Thanks to your seminar, I have the challenge of the future entries solved. However, I need a solution to relinking thousands of images to existing entries without linking them one at a time. I had thought that there must be a VBA code or macro out there to tell Access to search for and enter image links from the shared server folder and assign them to the proper entries /in bulk/. Anything you can give me to help with this monster would be most appreciated.

Reply from Richard Rost:

If the filenames of the embedded images are the same as the filenames of the images as they're stored on your server, then you MAY be able to get the name of the file from the embedded OLE object, see if it exists on your server, and if so delete the OLE object and set the filename in a text field. MAY. I've never done this before, and I would honestly have to research and test it for you to see if it's even possible. It's certainly not something am going to have the time to look into any time soon, but if you want to submit it via my TechHelp page, I'll see if one of my techs can research it for you.

Richard Rost on 7/29/2013: ADDENDUM... in this class, I teach you two ways to select a file to be used in your Access database. I cover using the COMMON DIALOG control and DLL. Well, this poses a problem for people using the 64-bit versions of Access 2010 or 2013. Comdlg32 doesn't exist.

So... you can quickly and easily replace the code used to select a file by just adding these four little lines of code to your program:

Dim FO As Object
Set FO = Application.FileDialog(3)
FO.Show
MsgBox "You selected: " & FO.SelectedItems(1)


That's it. Now the path and filename of the file you selected will be in the variable FO.SelectedItems(1) and you can do with it as you please.

Elisa S on 7/29/2013: Thank you so much for your prompt reply and ideas. I may take your tech team into consideration.
My main issue is the relinking of so many images into the database one at a time. It wouldn't be such a problem otherwise.
Thanks again. If I find a solution I'll be sure to let you know.

Reply from Richard Rost:

It may be easier (and cheaper) just to hire a temp to sit there and manually type in the file names and delete the OLE objects. :)

Chansun Hong on 8/2/2013: Regarding Private Sub Command5_Click()
I wnat to use linked files such as word, pdf, etc since I did not wnat to increase the file size of my database. I tested the code: employeepicture.action = acOLECreateLink, but it still increse my database. How I can use other type documents without increasing my db?
Thanks,

Reply from Richard Rost:

Using an OLE link still embeds the file PLUS a lot of information in the database. You want to store the file externally and just save the FILENAME in your database.

Anne T on 8/20/2013: I had tried that before posting the question. Sorry, I didn't say that, but setting those properties doesn't work.

Reply from Richard Rost:

Are there objects to the SIDES of your image? They will also prevent Can Shrink from working. Beyond that I would need to see your database to tell you for certain what's wrong.

Anne T on 8/20/2013: I need this solution to get it to work on 64-bit system, so great! But, I'm a novice. Where do I add these lines of code?

Reply from Richard Rost:

I'll be preparing an addendum video to show how to do this. Hang in there.

Jacob Swinney on 10/21/2013: Hello,
I have wrote you previously about learning how to import images and place shapes which relate to a drop down box within a report or legend. This is my first time opening up the Seminar drop down and the access imaging seminar seems like it has just about everything.

I work for a landscape company and my goal is to import map documents we have in a publisher doc. and allow our crew to fill our a maintenance 'report' and mark onto a map where a leak was repaired. Or a where a tree has fallen.

I apologize for the vague description. If it makes sense at all, would this seminar help me do just about all that? (I plan to finish beginner and expert as well.) If i might be missing a key factor could you direct me to possibly a seperate class in the selection?

Reply from Richard Rost:

You can use the techniques in this seminar to work with files of any type, not just images, although the seminar is geared towards image files.

Ramon on 12/8/2013: So you means to add these 4 lines to the code we copied from microsoft web page? I am using procedure described in seminar Access Imaging Seminar) Lesson 12.
Rafi Afridi on 12/8/2013: Dear Sir,
I am watching your videos through you tube from a long time and had get lots of help from these videos. I had also designed some Data base in Ms Access 2003 and 2007. Now a days I am working on a project about school records. but the main promblam which I am facing in Image uploading... I want to make a command button for student Picture uploading... So my very humbly I am requesting you to teach me how can I make a button for Picture upload funcion.
I am trying to give this code but its not working the code is .

StudentPicture.Pictur = (Me, "c:\")

its doznt work... Help me plz



Regards!

Reply from Richard Rost:

This is covered in the seminar.

Ramon on 12/9/2013: Did you posted the ADDENDUM video?. I finally did it with the instructions stated on 7/29/13. It works fine BUT if the user hit CANCEL buttom during the picture selection and I get an error...runtime error.
Khalid on 3/15/2014: Does this seminar apply to an access frontend connected to SQL server as a backend?

Reply from Richard Rost:

I haven't tested it with SQL Server, but I don't see why it wouldn't work. The whole point of the seminar is to show you how to store just the PATH and FILENAME of images in the database, and to store the FILE itself in a regular Windows folder - not the database.

mohammad hanif afridi on 5/22/2014: sir, thank you for your best videos ever.
my question is, I want to save all pdf files in a shared folder in a server on my network, and I want to create a link in an access 2010 form and also pdf file preview.... but if I make ole object to pdf file preview. it increases the size of my db.
how to save pdfs externally and provide a link with preview in a form. the pdf file preview should be as image... and when I click on the link or the picture, pdf file should open.
please reply asap with required code
thanks

Reply from Richard Rost:

Even LINKING to an external file with an OLE Object causes some overhead in your database. I don't recommend OLE Objects because of this. I cover what to do in the Seminar.

Robert Jacoby on 6/25/2014: I have a database that is linked to approximately 1500 images that are all in a single file folder.

I have a report that has 4 of these images per page.

After printing approximately 150 pages the images begin dropping out (not printing) from the bottom of the report. All other record data prints.

I have attempted dividing the database into volumes to see if it solves the problem. No luck.

Does Access have some limits that would create this problem?

Reply from Richard Rost:

I've never experienced this before, but it sounds like an out-of-memory error. Try generating the report in blocks of, say, 10 pages at a time?

Robert J on 6/26/2014: Richard thanks so much for the response. I actually had tried to print in 10 page blocks as you suggested. I even tried the one page technique with no better results.

I have upgraded by memory to 16 gigs thinking as you about the memory issue.

Since I sent my original question I attempted dividing up the images into two folders.

Crazy right??

Reply from Richard Rost:

Yea, I've never seen this before. I'd have to play with your database myself to have a chance at figuring it out. How about printing individual pages? If you just pick, say, page 125, will the images generate?

Robert J on 6/26/2014: I have been printing from the print preview.
I just attempted printing pages from the Layout View instead. It seems to
be working!! Maybe the print preview tries to convert the images to a .bmp.

Robert J on 6/27/2014: Richard,

The "LaunchCD" VB code from Microsoft seems to differ from what is shown in your seminar in that it does not contain the "optional initial folder" code.

I have looked on your site to see where I can download the code, however, I cannot find the file. Would you please direct me to the download location?

Robert J on 6/30/2014: Rather than wait for a response, I spent time closely reviewing the screen shots and with some trial and error, successfully modified the code to read:

Function LaunchCD(strform As Form, Optional InitialFolder As String = "C:\") As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.hwnd
sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
"JPEG Files (*.JPG)" & Chr(0) & "*.JPG" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = InitialFolder
OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "A file was not selected!", vbInformation, _
"Select a file using the Common Dialog DLL"
Else
LaunchCD = rim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function

Reply from Richard Rost:

Glad you figured it out, Robert. I do my best to help people out here in the Forums, but I can't promise a personal reply to each post. Some weeks are just much busier than others.

Ian Garriques on 10/12/2014: Richard,

Near the end of the Common Dialog lesson 12 video, I had problem with the 2 line subroutine.

EmployeePicture = LaunchCD(me,".....")
UpdatePicture

Everything seemed to get hung up on "UpdatePicture"

I substituted

EmployeeImage.Picture=EmployeePicture

and everything seemed to work well. I'm using Windows 7 and MS Office 365. Any thoughts on why the 'Updatepicture' wouldn't work and is there a problem using the line of code I replaced it with? Thanks, Ian


Reply from Alexander Hedley:

Hi Ian,
It's been a while since I've done the Imaging Seminar but don't remember seeing this issue,
If might be worth using the full IF ELSE statement but I don't see a reason why it would hang.
The UpdatePicture sub is used elsewhere, have you noticed it hang when the Form opens or if you change the picture?

Michael Adams on 12/12/2014: HI,

Is it possible to operate upon the data held in an OLE object? Using you example spreadsheet, could it be possible to sum up the months sales on using a calculation on a form rather than in the excel spreadsheet itself?

Or would it require (and is this possible) some other vb code to extract the data and either append it to a table or store it as a temporary variable first (and isthat even possible to do?)?

Reply from Alex Hedley:

You can use operate on othe objects using VBA but it can get quite tricky.
Why not import the data into Access then work on it from there?
Check out Expert 20

Michael A on 12/13/2014: Hi,

Thanks for the reply. Yes, that is what I would mostly likely do if it was just me using the database. unfortunately, I am developing solutions for people in my company who steadfastly refuse to abandon excel as a database, and the data held in the spreadsheets they create is often information other people in the company need. I am hoping to find a way to extract data to share to other people as an ends to better internal communication. We currently rely on people remembering to tell other people they've updated their spreadsheets.

Reply from Alex Hedley:

Have a look into the CreateObject Function.
You can open up an Excel File in VBA and retrieve values from a Sheet referencing the Cells in the Workbook.
I'll see about making a Tip as it's a bit much to go into here or you could submit a TechHelp request.

Michael on 12/14/2014: Cool, thanks I'll investigate that.
Richard Lanoue on 1/14/2015: This is kewl but can it be done with video?


Reply from Alex Hedley:

Just set the URL of the WebBrowser control to the video or page the video is on and you can play it from there.

Timothy Andrews on 2/9/2015: I just completed Lesson 4 in Access Imaging Seminar and converted my jpeg to bitmap but was still unable to display in the form. What am I doing wrong?

Reply from Alex Hedley:

How did you convert it?
If you just renamed it that won't work, you'd need to use Paint or equivalent and save as another file type.
BMPs should show without another image editing software being installed.

Uriel Ramirez on 4/4/2015: I find the FileDialog
Application.FileDialog(msgFilePicker) with Microsoft Officer X.X Object library a bit easier to use and requires less code like shown in this video
https://www.youtube.com/watch?v=AO2-jgVu8V0
Is there a course where you learn to use the application.FileDialog?

Reply from Alex Hedley:

Nothing is coming back in the Search. I was sure I'd seen it in a video but maybe I'm mistaken.
Since it's VBA it hasn't been covered in any of the Expert Lessons, it'll likely be covered in the Advanced Series.

One mention I did find was in this Thread.


Dim FO As Object
Set FO = Application.FileDialog(3)
FO.Show
MsgBox "You selected: " & FO.SelectedItems(1)


Here's an extended Tip on how to use it.

Richard Lanoue on 5/20/2015: I want a continuous form with each record with it's own photo... problem is it takes the first record's photo info and applies it to all the records...making them all have the first records image. How do I correct that for a continuous form?

Reply from Alex Hedley:

Did you watch the Addendum video?

Brendon Brooks on 6/14/2015: I had a similar question, I'm currently building a Sharepoint App for a client. I am attempting to link images from my OneDrive account to each record. How would I do this?


Reply from Alex Hedley:

Why not upload the images to sharepoint?

You can link images either from Sharepoint or from a url.

Robert Whishaw on 8/3/2015: Hey Richard
I'm using Office 2010 on a 64-bit Windows7 machine & I managed to resolve the 64-bit incompatibility that Anne Tschider mentioned earlier. You just need to type "PtrSafe" directly after the word "Declare" in the declare statement so it looks like this: Private Declare PtrSafe Function ...
After resolving this issue I was presented with another one unfortunately. When I click the "select image" button it immediately says "A file was not selected!" & I don't get any dialog box to select a file from.
Can you please help since this feature is the main reason I purchased this seminar.
Thx

Reply from Alex Hedley:

Have you seen the File Dialog Tip?

I'd strongly suggest just using 32Bit because of the issues it causes otherwise.


Philip Anderson on 8/16/2015: I just finished Lesson 15 and as Phil from 2012 post, My images will not display. The copying works well< I have 6 Treatment Images on the form 1-6 i am trying to use.
I replaced the line 'TreatmentPicture1 = GetFilenameOnly(LaunchCD(Me, ImagesFolder))'
before the updateImage1
and now it sort of works though quite often when selecting an image I have to select and open it twice as first time around the image selection is just ignored. Any ideas?

Reply from Alex Hedley:

Are they of a particular file type - png, jpeg, gif?

You could try the FileDialog Tip instead.

Vincent Putiri on 9/16/2015: Hi- I watched the Display Images video and I've watched the Image Report video, and I am trying to pull images from a URL to a report, but I'm not sure how to combine the lessons to do that. What steps should I follow?

Thanks
Vince

Reply from Alex Hedley:

They have to be images locally on a machine, the last time I checked web images weren't supported, unless you used a Web Browser control.
Have you seen my Tip?
You could use this to download the image then point to the local file.

David McMahon on 2/9/2016: G'day Rick, thanks for the seminar on Access Imaging. I have a question regarding image paths for forms and reports. The VBA code you show for image paths in Item 22.: Image Report seems to me for Absolute paths. I have created some code to create a relative path which lets me use the Access mbd on different computers.

My code works fine to allow image display with forms, but I cannot make it work for Reports.

Here is my VBA code:
Public Function GetImagePath() As String
GetImagePath = GetDBPath & "images\"
End Function

Public Function GetDBPath() As String
GetDBPath = CurrentProject.Path & "\"
End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Private Sub Report_Current()
Dim HieroImagePath As String
HieroImagePath = GetImagePath & Me!image_file_name
If Len([image_file_name]) > 0 And Len(Dir(HieroImagePath)) > 0 Then
headword_image.Picture = HieroImagePath
Else
headword_image.Picture = ""
End If
End Sub

Private Sub Report_Load()

End Sub

Private Sub headword_image_Click()

End Sub

Private Sub image_file_name_Click()

End Sub



Reply from Alex Hedley:

Some suggestions on other forums I've found say to create a hidden field on the Form, set the value to be the path then set the Picture object to be that

Me![PictureImage].Properties("Picture") = Me![ImagePath]


David M on 3/4/2016: Thanks Alex, will try that.
Deon Riley on 5/28/2016: Hi Richard/ Alex, just wanted to enquire if there was the option in the LaunchCD VBA code/ module to automatically downsize the image when it s selected and imported into the ImageFolder? It would be so good if this was a possibility.

Reply from Alex Hedley:

You could use ImageMagick and run a command after you've copied the file
http://www.imagemagick.org/Usage/resize/#resize

Jim Pigeon on 7/28/2016: is the link to the source code and the student Database still valid? I cant seem to find or gain access to it. Thanks

Reply from Alex Hedley:

Student Databases
Access Imaging Seminar - Watch lesson 12, about 1:41 into the video

Looks like the link is broken, I'll ask Rich to look into it

Janet Vicario on 12/16/2016: When I start to enter the click code into VBA I am not prompted to enter a c:/ file start path. it only asks for the (strform as Form)
6:24

Reply from Alex Hedley:

Are you using the same function?

Dan Gettinger on 3/9/2017: How can I change the function to ONLY return the filename and not the full path?

Reply from Alex Hedley:

You could use some string manipulation.
Remove file extension
Find the last slash and then . and do a mid to get the filename.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP