3 Easy Ways To Insert Image In Access Database

Summary:

Our today’s tutorial topic is on how to display image in MS Access database form, table. Through this post you can explore different ways to insert pictures in Access database.

Can Microsoft Access Store Images?

Yes, you can store images in your Access database. Well this task is possible by using attachments which stores multiple types of files in a single field.

Suppose, you have made a job contact database. By using the attachments, you can attach one or more resumes for each contact’s record, and can also add a insert a photo to of the contact.

Previous versions of MS Access database make use of a technology known as Object Linking and Embedding (OLE) for storing documents and images. By default, for each image and documents OLE generates a bitmap.

These bitmap files are quite large in size approx. 10 times greater than original file. when user view their image and document from their database, OLE displays only the bitmap not the original one. Apart from that OLE needs programs known as OLE servers to work.

In contrast, MS Access attachments store the attached file in their native formats. So, you don’t need to install any additional software to see images in your database.

What Are Techniques To Handle Image In Access Database?

This is one of the most commonly asked question regarding insertion of image in Access database. Well there are many ways or techniques to add image in Access database. so, let’s discuss them one by one.

  1. In the OLE field store the image you want to insert. After then for the image display just make use of bound object frame.
  2. Store image path in the text field and to show the image make use of image control.
  3. Another way is to store the picture in Binary Large object bitmap (BLOB) with in an OLE field. Whenever you need just extract the picture you want and make use of image control for easy view of the image.

All the technique mentioned here to insert picture in Access database has some advantages & disadvantages too. So, let’s discuss them in brief.

How To Insert Image In MS Access Database Table Through Attachments

For using attachments in your Access database, you need to first add an attachment field in at least a single table of your database. MS Access provides two way to add such attachment fields within your database table. Either you can add attachment fields in Datasheet view or in Design view.  Here we are trying this with Datasheet view.

Add an attachment field in Datasheet view

  1. Open your access database table in Datasheet view, and tap to the first available blank column. It’s not that tough to fetch a blank column, just look for the words “Add New Field”on column header.
  2. Now on Datasheet tab, within the Data Type & Formatting group, tap to the down arrow present next to Data Type. After then tap to the Attachment

For the attachment field Access will set the data type. Also place one icon in field’s header row. The shown figure is of new Attachment field. You can see an icon of paper clip in header row of field. You are not allowed to put text in the attachment field’s header row.

Add an attachment field in Datasheet view

  1. It’s time to save all the changes you have done.

Note:

You are not allowed to convert any new field into another data type. but if any error take place then you can delete the field.

Add An Attachment To A Table

  1. In the table having your attachment field opened in datasheet view. Make a double tap on the attachment field.

This will open the Attachments dialog box as shown in below figure:

Add An Attachment To A Table

  1. Now click to the Add after clicking on the Add option, Choose File dialog box appears.
  2. Make use of the Look in list to browse for the file/files which you need to insert in your record. After making selection for the file tap to the Open option.
  3. Now in the Attachments dialog box, tap to the OK option for adding the files with your table.

Access adds files with the field and increases the number of attachments according to that. The below shown figure shows the field with two attached images:

Add An Attachment To A Table 1

  1. Repeat the same step whenever you requires to add files to the current field or any other fields of Access table.

How To Insert Image In Access Using OLE Object Fields

OLE object is one such data type that helps you to easily save files generated by some other programs like Excel spreadsheets, Word documents or graphics. This section gives you the idea on how to work with the OLE object fields.

Steps To Insert Image In Access Using OLE Object Fields

Here we are using a table name tblEmployees and form name frmEmployees which is previously made.

  1. Choose the table tblEmployees after then tap to Design button.

Now insert the OLE field into this tblEmployees table.

  1. Tap to the firstly found blank Field Name row after then type text “Photo”. Hit on the arrow list containing the  Data Type. Then from the list of  data type, choose the OLE Object.

Great job, your OLE Object field is created now..!

  1. Now save all the changes. To view table in the datasheet view hit on the View button from toolbar.

Now we are adding some OLE object into the record of Maria Wyatt.

  1. Tap to any of the field for selecting Maria Wyatt after then scroll on the right section. Tap to the Photo field to get into the Maria Wyatt record, and choose the Insert » Object option from menu.

This will open the dialog box of “Insert Object” on your screen.

For creating and inserting any new object select the option “Create New”. Other than this, just make a click on  “Create from File” option for inserting an already existing file.

How To Insert Image In Access Using OLE Object Fields

  1. Here we have chosen the option “Create from File”. So, after choosing it you need to assign name and destination of that file which you requires to insert.

How To Insert Image In Access Using OLE Object Fields 2

  1. Now tap to the Browse option. After making press on this option make selection for the image file which you needs to insert in your document.
  1. Once you get it make a double-click on it eg: Maria.bmp file.

On the side of “browse” button you can see a “Link” check box.  This check box is for determining whether this OLE object, merely linked with the Access database or actually embedded into the database.

How To Insert Image In Access Using OLE Object Fields 2

Here we leave this link box unchecked, as we desire to embed graphics within database.

  1. After making complete procedure, tap to the OK option. This will close the opened dialog box and access will embed the Maria.bmp picture within Photo field.

But the problem occurs with this OLE object fields, is that it won’t show the native OLE objects. That’s the reason why you need to make use of form with the OLE object fields to work  smoothly.

  1. Now close the table name tblEmployees. Tap to Forms icon present in Objects bar. Hit frmEmployees form,. After then tap to Design option.

Now you need to add a new photo field into your frmEmployees form.

  1. After then tap to button Field List present on toolbar. You can select & drag down the Photo field at any place on your Access database form.

Other than this, you can display field list to choose view>>Field List present on the menu.

You don’t have to worry about the position for placing the OLE object Photo field.  As, by using any form control,  user can easily resize and move OLE object fields.

Let’s check it out how this Photo field appears in the Form view.

  1. Hit View option on toolbar for making switch to the Form view. Tap to Next Record Navigation option until and unless you get the Maria’s record.

Yeh, great work here is your inserted image!

How To Insert Image In Access Using OLE Object Fields 4

How To Insert Image In Access By VBA code

In your Access database make a Memo field for storing the Image. use the following code to insert image into the Access database using VBA code:

Dim FN As Long
Dim FB

FN = FreeFile
Open “C:\Pict1.bmp” For Binary Access Read As FN
FileBinary = Space(LOF(FN))
Get #FN, , FB
Close #FN

‘Open RecordSet RS
rs.AddNew
rs(“ImgName”) = “Picture1”
rs(“ImgMem”) = FB
rs.Update

To retrieving image file From Access:

Dim MPict
Dim FN As Long
‘Place an Image Control(Image1) on the Form
‘Open Recordset RS here

MPict = RS(“ImgMem”)
If Dir(“C:\Temp.bmp”) <> “” Then
Kill “C:\Temp.bmp”
End If

FN = FreeFile
Open “C:\Temp.bmp” For Binary Access Write As #FN
Put #FN, , MPict
Close #FN

Image1.Picture = LoadPicture(“C:\Temp.bmp”)

Insert Picture into M.S Access/SQL table using Java

You can also insert picture into MS Access/SQL table using Java program. For this you need to make a table in your Access database. Do it as shown below:

Table Definitions:

Table Name : Img[InsertPicture.mdb]
Primary Key : Nil
Foreign Key : Nil

Column Definition:

Column Name Data Type
ID AutoNumber
Images OLE Object

Table Description:

Column Name Description
ID Create AutoNumber for image id
Image Image type “OLE Object”

After creating the table now it’s time to create the DSN. Make use of the following source code of InsertPicture.java.

*/

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class InsertPicture

{

        public static void main(String[] args) throws Exception, IOException

        {

                Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

                String url=”jdbc:odbc:MyDsn”;

                Connection conn=DriverManager.getConnection(url);

                String INSERT_PICTURE = “insert into Img(ID,Image) values (?, ?)”;

                FileInputStream fis = null;

                PreparedStatement ps = null;

                try

                {

                        conn.setAutoCommit(false);

                        File file = new File(“laura.jpg”);

                        fis = new FileInputStream(file);

                        ps = conn.prepareStatement(INSERT_PICTURE);

                        ps.setString(1, “001”);

                        ps.setBinaryStream(2, fis, (int) file.length());

                        ps.executeUpdate();

                        conn.commit();

                }

                finally

                {

                        ps.close();

                        fis.close();

                }

        }

}

After then compile the above code by using the javac compiler. At the end run the program.

C:\jdk1.4\bin javac InsertPicture.java
C:\jdk1.4\bin java InsertPicture

now you will see the above code works great to insert a “laura.jpg” name picture within the img table.

Problems Associated With Image Insertion In Access Database

Sometimes, it’s not that easy to insert image in the Microsoft Access table. If you are inserting several images or each of the image file is quite bigger in size then it automatically raises “Access Database Too Large” Issue. Well to resolve this issue just make use of Access inbuilt utility tool compact and repair. Other wise very soon your database will get corrupt.

Sometime user also render issue in which their Access forms and reports not showing images. For this just check out the following post: How To Fix Access Forms And Reports Not Showing Image Issue

Unluckily, if you are already dealing with this Access database corruption issue. Then to get back your crucial data back make use of an outstanding Access Repair And Recovery Tool. It is the most prominent solution available to fix any corruption issue occurred in Access database file accdb and mdb.

Wrap up:

Try all the aforementioned tricks to insert images in Access database carefully. If in case you get stuck in any one of the above-mentioned situations then try the solution mentioned over there. Apart from the mentioned issue if you are getting any other problem in your access database then share it with us in our FAQ section. We will try to fix it, as soon as possible.

3 Easy Ways To Insert Image In Access Database