Using MS Access with Java through JDBC-ODBC connection

Tagged:  

Microsoft has provided a method to build a quick Jet-Engine database on your computer without the need for any specific database software (it comes standard with Windows). Using this method, we can even create a blank Microsoft Access database without having MS Access installed!

MS Access data bases can be connected to via ODBC. Instead of accessing the database directly, we can access it via a Data Source Name (DSN). Here's how to set up a DSN on your system:

  1. Open Windows' ODBC Data Source Administrator as follows:
    o In Windows 95, 98, or NT, choose Start > Settings > Control Panel, then double-click the ODBC Data Sources icon. Depending on your system, the icon could also be called ODBC or 32bit ODBC.
    o In Windows 2000, choose Start > Settings > Control Panel > Administrative Tools > Data Sources.
  2. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
  3. Click Add to add a new DSN to the list.
  4. Scroll down and select the Microsoft Access (.MDB) driver
  5. Type in the name "mdbTEST" (no quotes, but leave the cases the same) for the Data Source Name
  6. Click CREATE and select a file to save the database to (I chose "d:\java\mdbTEST.mdb") - this creates a new blank MS Access database!
  7. Click "ok" all the way out Now our data source is done!

Here's a complete program showing how to access your new DSN data source:

import java.sql.*;
public class Test
{
    public static void main(String[] args) 
    {
        // change this to whatever your DSN is
        String dataSourceName = "mdbTEST";
        String dbURL = "jdbc:odbc:" + dataSourceName;
        try { 
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection(dbURL, "",""); 
       }
       catch (Exception err) {
        System.out.println( "Error: " + err );
      }
  }
}
As stated in the code, modify the variable dataSourceName to whatever you named your DSN in step 5 from above.

 

Running a SQL Statement on your Access Database

 

Once you have your connection, you can manipulate data within the database. In order to run a SQL query, you need to do 2 things: 1. Create a Statement from the connection you have made 2. Get a ResultSet by executing a query (your insert/delete/etc. statement) on that statement Now lets learn how to make a statement, execute a query and display a the ResultSet from that query. Refer to the following complete program for an understanding of these concepts (details follow):
import java.sql.*;
public class Test
{
      public static void main(String[] args)
      {
        try {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          /* the next 3 lines are Step 2 method 2 from above - you could use the direct
          access method (Step 2 method 1) istead if you wanted */
          String dataSourceName = "mdbTEST";
          String dbURL = "jdbc:odbc:" + dataSourceName;
          Connection con = DriverManager.getConnection(dbURL, "",""); 
          // try and create a java.sql.Statement so we can run queries
          Statement s = con.createStatement();
          s.execute("create table TEST12345 ( column_name integer )"); // create a table
          s.execute("insert into TEST12345 values(1)"); // insert some data into the table 
          s.execute("select column_name from TEST12345"); // select the data from the table
          ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
          if (rs != null) // if rs == null, then there is no ResultSet to view
          while ( rs.next() ) // this will step through our data row-by-row
          {
            /* the next line will get the first column in our current row's ResultSet 
            as a String ( getString( columnNumber) ) and output it to the screen */ 
            System.out.println("Data from column_name: " + rs.getString(1) );
         }
        s.execute("drop table TEST12345");
        s.close(); // close the Statement to let the database know we're done with it
        con.close(); // close the Connection to let the database know we're done with it
    }
    catch (Exception err) {
        System.out.println("ERROR: " + err);
    }
  }
}

Comments

i hav an error:
unsuccessful .java.sql.SQLException:[microsoft][ODBC DRIVER Manager] datasource name not found and no default driver specifed.
what should do??plz reply....

i also got the same error.can u plz help me

Make sure you have successfully added datasouce, and datasource name might be case sensitive.
Another issue might be, the driver does not exist. You are using JDK, instead of JRE, right?

what can i do if i am using JDK

IT IS OK ... WORKING FINE ..
HOW TO MAKE CHANGES TO TABLE LIKE , ALTER...
ONCE AGAIN THANK YOU

if the result swet is null and if v want to do perform some operation what we should do?

I like kittens

It worked, Thanks

© Hossain Khan - Some rights reserved.
Creative Commons License This site is licensed under a Creative Commons Attribution-Noncommercial 2.5 License.