JDBC connection to Microsoft Access

Introduction

In this article, we make a connection using JDBC to a Microsoft Access database. This connection is made with the help of a JdbcOdbc driver. You need to use the following steps for making a connection to the database.

Creating a Database

Step 1: Open Microsoft Access and select Blank database option and give the database name as File name option
i0.jpg
Step 2: Create a table and insert your data into the table
i1.jpg
Step 3: Save the table with the desired name; in this article, we save the following records with the table name student.
i2.jpg

Now Creating DSN of your database

Step 4: Open your Control Panel and then select Administrative Tools.
Step 5 : Click on Data Source(ODBC)-->System DSN.
dsn1.jpg
Step 6: Now click on add option for making a new DSN.select Microsoft Access Driver (*.mdb. *.accdb) and then click on Finish
dsn2.jpg
Step 7: Make your desired Data Source Name and then click on the Select option, for example in this article we use the name mydsn
dsn3.jpg
Step 8: Now you select your data source file for storing it and then click ok and then click on Create and Finish
dsn4.jpg
Step 9: Java program code
  1. import java.sql.*;
  2. public class MsAcessODBC {
  3. public static void main(String[] args) {
  4. try {
  5. // loading thejdbc odbc driver
  6. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  7. // creating connection toth data base
  8. Connection con = DriverManager.getConnection("jdbc:odbc:mydsn", "", "");
  9. Statement st = con.createStatement();
  10. // create an execute sql command on database
  11. ResultSet rs = st.executeQuery("Select * from student order by rollno asc");
  12. ResultSetMetaData rsmd = rs.getMetaData();
  13. // this getColumnCount reurn the number of column in the selected table
  14. int numberOfColumns = rsmd.getColumnCount();
  15. // while loop and with while loop code use for print the data
  16. while (rs.next()) {
  17. for (int i = 1; i <= numberOfColumns; i++) {
  18. if (i > 1)
  19. System.out.print(", ");
  20. String columnValue = rs.getString(i);
  21. System.out.print(columnValue);
  22. }
  23. System.out.println("");
  24. }
  25. st.close();
  26. con.close();
  27. } catch (Exception ex) {
  28. System.err.print("Exception: ");
  29. System.err.println(ex.getMessage());
  30. }
  31. }
  32. }
OUTPUT
cmd.jpg
Resource