How to Manipulate a Microsoft Access Database Via JDBC

Microsoft Access is a proprietary database system used primarily for small data storage management. It supports connections from high level programming languages via the Open Database Connectivity bridge. Java Database Connectivity, or JDBC, is another way of connecting to Microsoft Access and can be used in conjunction with ODBC to perform data manipulation on a Microsoft Access database. Once you make a connection with the database, you can perform a number of data manipulation tasks. These tasks include creating a table, inserting data, selecting data, and dropping a table.

Things You'll Need

• Microsoft Windows
• Eclipse IDE
• Microsoft Access Database
• JDBC-ODBC Bridge Driver

Instructions

Set up Java

1. Open up the Eclipse IDE.

2. Navigate to "File," "New," and select "Java Project."

3. Enter "MicrosoftAccessConnection" in the name field, then press "Finish."

4. Locate the Package Explorer window and expand the newly formed project.

5. Locate the folder named "src" and right-click it.

6. Choose "New," then select the option to create a new package.

7. Name the package "msaccess" and press "Finish."

8. Find the new package in the Package Explorer window and right-click on it.

9. Navigate to "New," then select the option to create a new class.

10. Name the new class "MSAccessTest" and create the new file by pressing "Finish."

11. Double click on "MSAccessTest" and locate the new editor window that appears.

Configure JDBC-ODBC

1. Delete the automatically generated content from the editor window. This can be done with the keystrokes, Ctr+A, delete on a Windows machine or cmd+A, delete on a Mac OSX machine.

2. Enter the following lines of code into the editor window.

   //Import all from Java's SQL package

   import java.sql.*;

   class MSAccessTest{

   public static void main(String[] args){

   try{

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //find the ODBC-JDBC driver

   String PathToDatabase = "d:/java/mdbTEST.mdb"; //create a variable that point to the local location of the database file

   String Database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; //configure driver

   Database += PathToDatabase.trim() + ";DriverID=22;READONLY=true}"; //complete database path with parameters

   Connection connection = DriverManager.getConnection( Database ,"",""); //create a new connection

   Statement statement = connection.createStatement(); //create a new sql statement wrapper

   statement.execute("create table TESTTABLE ( test_column integer )"); //create a sample table in the database

   statement.execute("insert into TESTTABLE values(1)"); //create some test data

   statement.execute("select test_column from TESTTABLE"); //select some data from the newly created table

   ResultSet resultset = statement.getResultSet(); //get the resultset

   if (resultset != null){ //if the result set exists

   while ( resultset.next() ) //step through the result set and print out the data

   {

   System.out.println("Data from test_column: " + resultset.getString(1) );

   }//end while

   }//end if

   //statement.execute("drop table TESTTABLE");

   statement.close(); // close the Statement

   connection.close(); // close the Connection

   }//end of try clause

   catch (Exception e) {System.out.println("Error: " + e);}//end of catch clause

   }//end of main method

   }//end of class definition

3. Navigate to "File," then select "Save" to save the program before running it.

Compile and run the program

1. Navigate to the the menu bar of Eclipse IDE and select Run.

2. Locate the Console window near the bottom of Eclipse IDE.

3. Verify that the output of the program reads:

   "Data from test_column: 1"

   If the program performed without error, sample data should be visible and no error messages have appeared.

Tips & Warnings

• If there are errors during the running of the program, check over the code and make sure that it is copied correctly.

• Additionally, make sure that the file path to the database file is correct.

• To avoid errors, ensure that your JDBC-ODBC driver is installed properly on your machine.

• Consult a programmer if errors still appear during the running of the program.

• Data manipulation through this method cannot be undone. Do not attempt to manipulate data with this method unless you are confident in what you are trying to perform. Data can be corrupted and permanently deleted using Java and SQL statements.

References

• Javaworld: Use Microsoft Access with Java
• Techbookreport: JDBC-ODBC Bridge - A Quick Introduction

Resources

• Baruch College City University of New York: Microsoft Access Tutorial