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 that store 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 insert a photo of the contact.

Previous versions of the 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 the original file. when a user views 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 questions regarding the insertion of images in the Access database. Well, there are many ways or techniques to add images in the 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 the 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) within an OLE field. Whenever you need just extract the picture you want and make use of image control for an 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 ways to add such attachment fields within your database table. Either you can add attachment fields in the Datasheet view or in the 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 the column header.
2. Now on the Datasheet tab, within the Data Type & Formatting group, tap 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 the field’s header row. The shown figure is of the new Attachment field. You can see an icon of a paper clip in the header row of the field. You are not allowed to put text in the attachment field’s header row.

3. 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 takes 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 the below figure:

2. Now click on the Add button, this will open the Choose File dialog box.
3. Make use of the Look in list to browse for the file/files which you need to insert in your record. After making a selection for the file tap the Open option.
4. Now in the Attachments dialog box, tap 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:

5. Repeat the same step whenever you require to add files to the current field or any other fields of the 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 an idea of 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 the Design button.

Now insert the OLE field into this tblEmployees table.

2. 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 types, choose the OLE Object.

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

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

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

4. Tap to any of the fields 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 the 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.

5. 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 require to insert.

6. Now tap to the Browse option. After making press on this option make a selection for the image file which you need to insert in your document.

7. Once you get it make a double-click on it eg: Maria.bmp file.

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

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

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

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

9. 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.

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

Other than this, you can display the 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 of control, users can easily resize and move OLE object fields.

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

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

Yeh, great work here is your inserted image!

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 MS Access/SQL table using Java

You can also insert picture into MS Access/SQL table using the 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 image table.

Problems Associated With Image Insertion In Access Database

Sometimes, it’s not that easy to insert images 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. Otherwise very soon your database will get corrupt.

Sometimes users also render issues 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 that occurred in Access database file ACCDB and MDB.

Wrap Up:

Try all the aforementioned tricks to insert images in the 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 comment section. We will try to fix it, as soon as possible.