Storing Bitmap Images in OLEObject Attachments Fields
MS-Access Tables have OLEObject/Attachment Field (Access2000/Access2007) to store and display images on Forms/Reports.
Example: Storing of employee’s photo or Product’s image on data table.
Depending on the Staff size of your Company or number of product items in stock the number of images stored directly into their respective table can increase the database size considerably. In Access 2007 the Bitmap Images (.BMP image files) stored in Attachment field are automatically compressed and reduced to the size of a .JPG image. In either case the database size will increase, on addition of each image in table. If you have hundreds or thousands of images to store this way then you can imagine what will happen. The maximum size of a database can be only 2 Giga Bytes.
But, if you can foresee this issue of your Project and plan to store the required images in a dedicated folder on the disk (Server or Local depending on the User requirements) then you don’t have to bother about the database size at all. But, you should be able to identify and pick the correct image, related to a particular record from disk easily, and display it on Form or Report, whenever the need arises.
It is important that we organize the images on disk in such a way that we can easily identify which image belongs to which record. Once this is property planned and executed we can pick the correct image and display it on Form/Report, in an Image Control very easily.
The easiest approach is to give names to images with some unique field value of the related table, like Employee ID of Employee record. For example, an employee’s photo name can be the Employee’s ID value, like 1.bmp, 2.bmp etc. Each record on Products Table will have Product Codes as unique values and images can be named with product code of each item.
When a particular employee’s record is Current on the Form we can read the employee code from the form and add the image extension (like me![ID] & “.jpg”) to create the image name and load it from disk into an image control on Form.
If you prefer to create image type smaller in file size, to save disk space, then you may choose any one of this types: .png, .jpg or .gif. GIF images are lesser in size but image quality may not be good. Select only one of this image type and save all the images in that type, say .jpg, then the program will be a simple routine, to load the image into an image control on the form/report.
We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.
Let us prepare for a trial run of a small Program.
- Import the Employees Table from Microsoft Access Northwind.mdb sample database.
- Use the Form Wizard to create a Quick Form in Column Format.
- Move or resize the Fields (or remove some unwanted fields) so that we can add an Image Control on the Form, big enough to display sample images, which you are going to create now.
A sample Employees Form Design is given below, with the Image Control on it:
- Select the Image Control from the Toolbox and draw an Image Control on the Form, as shown above.
- Display the Image Control’s Property Sheet (F4) and change the Name Property value to ImgCtl.
- Save the Form with the name Employees.
Note: As I have mentioned earlier, the Employee ID field values are 1,2,3 etc. We will create few images with the names: 1.jpg, 2.jpg, 3.jpg etc.
If you were able to Import the Employees Table from Northwind.mdb then you are lucky, you have employee’s photo bitmap images in the OLEObject field. You can save these images on disk. I am using Access2007 and I will explain the procedure of Access2007 to save the image from Employees Table to disk. If you are using any other Access Version the menu options may be different.
- Open Employees Table in Datasheet View.
- Find the Photo field of the first record, the Employee ID of this record is 1.
- Right-Click on the Photo field with the Bitmap Image text, select Open from the Bipmap Image Object option. The image will open in Paint program.
- Before saving the image on disk create a new folder C:\images.
- Use Save As… option to save the image in folder C:\images with the name 1.jpg.
- Save few more employee photos in this way.
If you could not save images from Employees Table then open any image from your disk in Paint Program and save them as 1.jpg, 2.jpg, 3.jpg etc. in C:\images Folder.
- Open the Employees Form (you have saved in Step-6) in Design View.
- Press ALT+F11 to display the VBA Window.
- Copy and paste the following Code into the VBA Window of the Form, below the Module Global Option: Option Compare Database.
Private Sub Form_Current() Dim strImagePath As String, pic As String On Error GoTo Form_Current_Err 'image folder strImagePath = "c:\images\" 'create image name pic = Me![ID] & ".jpg" strImagePath = strImagePath & pic 'validate image name and location If Len(Dir(strImagePath)) > 0 Then 'image found, load it into the image control Me!ImgCtl.Picture = strImagePath Else 'image not found, make image control empty. Me!ImgCtl.Picture = "" End If Form_Current_Exit: Exit Sub Form_Current_Err: 'Not necessary to display a message here Resume Form_Current_Exit End Sub
- Save and close the Form.
- Open the Form in Normal View.
If everything went on well then you will see the first employees photo in the image control. Sample Screen-shot of Employee Form with the photo is given below:
- Use the record navigation control to move to next record and display other images also.
Image Display on Report.
A sample Employees’ Report Design, with the Image Control is given below.
- Design a Report using few fields from the Employees Table, as shown above, and see that you are using the Employee [ID] Field on the Report. This is important because we need this number to create the image name corresponding to the record in print.
- Insert an Image Control on the Report, as shown in the above Image.
- Display it’s Property Sheet and change the Name Property value to ImgCtl.
- Click somewhere on the empty area of Detail Section of the Report. If you have closed the Property Sheet then press F4 to display it.
- Select the On Format Event Property and click on the build ( . . . ) button to open the VBA Window of the Report.
- Copy and Paste the following Code replacing the Detail_Format() Event procedure opening and closing statements:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Dim strImagePath As String, pic As String On Error GoTo Detail_Format_Err 'image folder strImagePath = "c:\images\" 'create image name pic = Me![ID] & ".jpg" strImagePath = strImagePath & pic 'validate image name and location If Len(Dir(strImagePath)) > 0 Then 'if found load the image into the image control Me!ImgCtl.Picture = strImagePath Else 'make image control empty. Me!ImgCtl.Picture = "" End If Detail_Format_Exit: Exit Sub Detail_Format_Err: 'Not necessary to display a message here Resume Detail_Format_Exit End Sub
- Save the Report and close it.
- Open the Report in Print Preview.
Sample Report Preview is given below:
If you want to give more flexibility to your Project, with all the four image types (.bmp, .png, .jpg and .gif) then you may use the following modified Code:
Private Sub Form_Current() Dim strImagePath As String, pic As String Dim strImagePathName As String, strI(0 To 3) As String Dim j As Integer, strType As String On Error GoTo Form_Current_Err strI(0) = ".bmp" strI(1) = ".png" strI(2) = ".jpg" strI(3) = ".gif" strImagePath = "c:\images\" pic = Me![ID] strType = "" For j = 0 To UBound(strI) strImagePathName = strImagePath & pic & strI(j) If Len(Dir(strImagePathName)) > 0 Then strType = strI(j) Exit For End If Next strImagePathName = strImagePath & pic & strType If Len(strType) > 0 Then Me!ImgCtl.Picture = strImagePathName Else Me!ImgCtl.Picture = "" End If Form_Current_Exit: Exit Sub Form_Current_Err: 'Not necessary to display a message here Resume Form_Current_Exit End Sub
Note: Report must be in Print-Preview mode to view the images on Report. The Images may not appear in Layout View.
- Microsoft DOS Commands in VBA
- DOS Commands in VBA
- Opening specific Page of PDF File
- Calculating Time Difference
- Centralized Error-handler & Error- log