Menu
Navigation
Home
Forum
Search
Links
Fun Stuff
Comical Banners
Downloads
Funny Feed rss
Uselessfacts
Weird Laws
Pics
Claim to fame
USA 2004
Edinburgh 2005
Bristol 2005
Italy 2005
Lugano 2005
Minneapolis 2006
Lugano 2006
London 2006
Longleat House 2006
Paris 2006
Nashville 2007
The Peak District 2007
New Mexico 2007
Silverstone 2007
Geneva 2007
Silverstone 2008
Florida 2008
Atlanta 2009
New York 2010
Norway 2010
Projects
Adding Custom Songs to Guitar Hero III
BOINC FAQ Service
BOINC Stats Services
Cancer Stats Service
Fractal Applet
Go-Karting
JDBC-ODBC Tutorial
MC68000 IDE
Maze Applet
NetSend
PCF FAQ
SAAS
Seti Graph
Seti Stats Service
TLL Compiler Applet
Wii GH3 Guitar with PC version

Using Java with Microsoft Access

I wrote this tutorial because I found it very hard to find a decent one myself on the web. I hope that this tutorial is enough to get anyone started who is familiar with the Java programming language and Microsoft Access databases. The tutorial aims to show you how you can create programs that rely on a Microsoft Access database.

Notes

The screen shots for the tutorial were taken from a Windows XP Pro system.

Microsoft Access 97 and Microsoft Access 2000 databases have been tested with all sample code in this tutorial.

The test machine had the following installed:

  • Microsoft Access 2000
  • Visual Studio Service Pack 5 (updates Microsoft Access Data Components to version 2.5, including ODBC drivers)
  • Java 1.3 SDK Standard Edition
  • Contents

    Getting Started
    Setting Up ODBC
    Java
    Where next?
    Your Comments

    Getting started

    You will need at least JDK 1.1 and of course, at least Microsoft Access 97 installed on your system.

    I assume that you know how to use Access 97, so I will now assume that you already have a MS Access database that you want to be able to access.

    Microsoft uses ODBC (Open Database Connectivity) drivers with most of their software. Java uses JDBC (Java Database Connectivity) drivers to connect to various databases. JDBC cannot access an ODBC database directly, instead you have to use the JDBC-ODBC Bridge driver.

    Before you can even touch Java, you need to tell the ODBC driver on your PC where your database is stored, so that it can create a data source that the JDBC-ODBC bridge driver can open.

    For Windows 9x systems, go to Control Panel and open the "ODBC Data Sources (32bit)" applet.

    For Windows XP and (I think) Windows 2000 systems go to Control Panel and open the "Data Sources (ODBC)" applet.

    Important note for Windows XP users:

    If the Data Sources applet is not visible in Control Panel then you can still access "Data Sources" by going to Control Panel> Administrative Tools> Data Sources (ODBC).

    Alternatively, you can make the applet visible in Control Panel, by downloading PowerToys for Windows XP from Microsoft's Expert Zone

    When you install the Power Toys, ensure you install "TweakUI".

    Then fire up TweakUI, and go to Control Panel and tick "odbcc32.cpl"

    Setting up ODBC

    Now that you have the ODBC control panel applet open, you should be presented with the following information:

    Next you have to add a data source that the JDBC-ODBC Bridge driver can open.

    To do so, click add.

    The following dialog will now appear:

    Make sure you select "Microsoft Access Driver" as shown above and click finish.

    In the next screen enter a name for your data source - this is the name you will use in your Java code to open your database. Also enter a description. Use the "Select" button to browse for your database on your PC.

    Below is what you should now have:

    Click Ok. Your database will now be in the list of data sources:

    Java

    Phew! Now all the ground work has been done, we can get down to the fun stuff. The following part of the tutorial shows you how to verify that you have done the above properly, and how to create a table in your database.

    In your Java application, make sure you include the java.sql package.

    Here is some sample code:


    import java.sql.*;

    public class Temp {

    public static void main(String args[]) {

    Connection con; // The connection to the database.
    // The following code can throw errors, so they must be caught.
    try{

      // First, tell Java what driver to use and where to find it.
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      // Next, create a connection to your data source.
      // Specify that you are using the ODBC-JDBC Bridge.
      // And specify the data source from ODBC.

      con = DriverManager.getConnection("jdbc:odbc:Temp");
      // Create an SQL statement.
      Statement stmt = con.createStatement();
      // Execute some SQL to create a table in your database.
      // If the table already exists, an exception is thrown!

      stmt.executeUpdate("CREATE TABLE COFFEES " +
      "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
      "SALES INTEGER, TOTAL INTEGER)");

    }
    // Catch any exceptions that are thrown.
    catch(ClassNotFoundException e){

      System.out.println(e.toString());

    }
    catch(SQLException e){

      System.out.println(e.toString());

    }

    }

    }


    The following screen shot shows that the above code did infact create the table specified by the SQL:

    Where next?

    My tutorial is only to get you started. For further information on how to access databases, create relationships etc. please see the Java Tutorial by Sun.

    Copyright © Neil Munday 2002