Introduction.
Microsoft Access provides the OLE Object and Attachment field types to store and display images directly in tables, which can then be shown on forms or reports.
For example:
Storing an employee photo in a staff table.
Storing product images in an inventory table.
However, storing images this way can quickly increase the database size. In Access 2007, bitmap image (.BMP files) placed in an Attachment field are automatically compressed to the size of a JPG, which helps reduce storage overhead—but the database size still grows with every added image.
If your application involves hundreds or thousands of records with images, the file size will expand rapidly, and this becomes a serious limitation. An Access database can only grow to a maximum of 2 GB.
Identifying the Image with its Related Record.
However, if you anticipate this issue in your project, you can plan ahead by storing all required images in a dedicated folder on the disk. Either on a local machine or a network server, depending on user requirements. This approach eliminates concerns about database size. The key, however, is ensuring that each image can be reliably identified and retrieved for its corresponding record, so it can be displayed on forms or reports whenever needed.
Organizing the Images.
It is important to organize images on disk so they can be easily matched with their corresponding records. Once this is properly planned and implemented, retrieving the correct image and displaying it in an Image Control on a form or report becomes straightforward.
The simplest method is to name each image file using a unique field value from the related table, such as the EmployeeID for employee photos (e.g., 1.bmp, 2.bmp, etc.). Similarly, product images can be named using their ProductCode values, ensuring each record is directly linked to its corresponding image.
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 the disk into an image control on the Form.
To minimize disk space usage, you can choose image formats that are smaller in size, such as PNG, JPG, or GIF. While GIF images are the smallest, they may compromise image quality. It is best to select a single format, JPG, for example, and save all images in that format. This way, your program can use a simple routine to load the image into an Image Control on a form or report efficiently.
We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.
Prepare for a Trial Run.
Let us prepare for a trial run of a small Program.
Import the Employees Table from the 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 will 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 a few images with the names: 1.jpg, 2.jpg, 3.jpg, etc.
If you were able to import the Employees Table from Northwind.mdb, you are in luck—you already have the employees’ photos stored as bitmap images in the OLE Object field. You can save these images to your disk for use elsewhere. I am using Access 2007, so I will explain the procedure for saving images from the Employees Table to disk in this version. If you are using a different version of Access, the menu options may vary slightly.
Open the 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 caption, and select Open from the Bitmap Image Object option. The image will open in the Paint program.
Before saving the image to disk, create a new folder C:\images.
Use the Save As… option to save the image in the folder C:\images with the name 1.jpg.
Save a few more employee photos in this way.
If you could not save images from the Employees Table, then open any image from your disk in the Paint Program and save it as 1.jpg, 2.jpg, 3.jpg, etc., in the C:\images Folder.
Open the Employees Form (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.
The Trial Run.
Open the Form in Normal View.
If everything went well, then you will see the first employee photo in the image control. The Sample Screenshot of the Employees Form with the photo is given below:
Use the record navigation control to move to the next record and display other images.
Image Display on Report.
A sample Employees' Report Design, with Image Control, is given below.
Design a Report using a 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 the Image Control on the Report, as shown in the above Image.
Display its Property Sheet and change the Name Property value to ImgCtl.
Click somewhere on the empty area of the 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 and replace 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.
The Trial Run of the Report.
Open the Report in Print Preview.
The Sample Report Preview is given below:
If you want to give more flexibility to your project, with all 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 SubNote: The Report must be in Print-Preview mode to view the images in the Report. The Images may not appear in Layout View.
No comments:
Post a Comment
Comments subject to moderation before publishing.