JDBC – Example with Access

Home/Java - Tutorial/JDBC - Tutorial/JDBC – Example with Access
JDBC – Example with Access 2017-06-13T10:46:51+00:00

To connect java application to access database we must have at least one database created in access.

Steps to create a database in MS-Access:

(1) Open Microsoft Office Access.

(2) Click on Blank Database.

(3) Type an appropriate name of database in File Name: box for example, HOD_DATA and click on Create Button.

(4) Create appropriate field name in table and value as per the field.

EX.:

(5) Right click on Table1 and select Save. Type the name of Table for example, DATA and click on OK button.

(6) Close the Table by right clicking on DATA and select Close. and Exit from Database

(7) Move this database to the appropriate drive where you want.

Now lets create TYPE 1 driver program for JDBC with access.

?

 


Output:

Serial number : 1 , Name : Ashutosh Abhangi , City : Dhoraji and Age : 27

Serial number : 2 , Name : Kamal Kotecha , City : Junagadh and Age : 24

Key point:

String which we are writing in Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver“) to load the driver.

String which we are writing in Connection con = DriverManager.getConnection(“jdbc:odbc:HOD_DATA“) to create connection with particular database.

Here HOD_DATA is our DSN (Data Source Name).

Steps for creating DSN for access.

(1) Go to Control Panel.

(2) Click on Administrative Tools(Window XP) for (Window 7) System and Security then Administrative Tools.

(3) Click on Data Sources (ODBC).

(4) Select MS Access Database and Click on Add button

Here in Windows XP you can easily add new DSN  but if you are getting an error or not able to add new DSN in Window 7 go to C:\Windows\SysWOW64 and then open odbcad32.exe and repeate step 4.

EX:

(5) Select Microsoft Access Driver (*.mdb,*.accdb) and Click on Finish button.

If you cant find the below driver then you should download JDBC ODBC Driver for ms access.

 

(6) Type Data Source Name, for example HOD_DATA then click on Select button in the Database frame.

(7) Select particular database which we saved on particular drive and created at beginning of this page (HOd_DATA). and click on OK button.

EX:

 

(8) Click on OK button and Check out the textarea of Data Sources Administrator. Now it contains a new DSN as a HOD_DATA.

 

(9) Click on OK button and close the Administrative Tools (Control Panel).

NOTE:

Do not confuse your self due to Database Name and Data Source Name, Here Both are same HOD_DATA but we can take different name too.

One more thing there may be a 32 bit or 64 bit issue like architecture mismatch so java2all recommend you that please make them all same.

Your java IDE tool, Microsoft Aceess and JVM or JDK all must be the same bit (32/64) version.

Now run the above program and check out the output.

PreparedStatement in access:

Lets now move to PreparedStatement example for access.

First of all lets assume that we have table named PA in access.

And our DSN is DATA.

?

 


Output:
inserted
First run the above program with suitable table and DSN after running it refresh your access database and you can see one record inserted as per our program.
 
You can run PreparedStatement program for JSP too with dynamic data.
For this we will cretae two JSP file one for inserting data (simple form with text box as per our table).
Second JSP file contains logic for connecting data base as well as PreparedStatement logic for inserting data.
Insert_data.jsp
?

 


Now insert value in text box as you want to insert in database as shown below.
Remember here too our DSN and Table are same as above program.

You can see the data here which i want to insert in our database.

datams.jsp

?

 


Output:
inserted
Now again refresh your table data and you can see one more new record which we inserted dynamically.
 
With access we can not do CallableStatement because access does not support stored procedure.
We will do it in mysql and oracle in next chapters.

Prev Next