Basic Java Code to connect to a MS Access table

I need a simple example of Java connecting to a MS Access database table on the same desktop PC.

I would like to be able to add, delete, change records in the Access table.  

Will add more points if your willing to help me learn more.

Thanks,
DanC3
DanC3Asked:
Who is Participating?
 
girionisConnect With a Mentor Commented:
 Ok here we are:

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;

public class DatabaseConnection
{
     public static void main(String [] arguments)
     {
          Connection connection = null;
          Statement statement = null;
          ResultSet rs = null;
         
          try
          {
               // Load the database drievrs. An ODBC: JDBC bridge
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               System.out.println("drivers loaded");
               
               // Obtain connection to the database.
               connection = DriverManager.getConnection("jdbc:odbc:Reviews", "", "");
               System.out.println("Connection established");
               
               // Cerate statement to read and write to/from database.
               statement = connection.createStatement();
               System.out.println("Statement created");
               
               rs = statement.executeQuery("SELECT * FROM Emails");
               
               // Get next row.
               rs.next();
               System.out.println("data is: " + rs.getString("email"));    
               
               // Close statement and conenction.
               statement.close();
               connection.close();
          }
          catch (ClassNotFoundException cnf)
          {
               System.out.println("Could not find class: " + cnf);
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem: " + sqle);
          }
     }
}

  Things to notice:

  The above code assumes that you have an Access database running on you systems named "Reviews" and you do not need a password and username to connect. If you have any other database change the:

connection = DriverManager.getConnection("jdbc:odbc:Reviews", "", "");

and instead of Reviews put the name of your database and your username and password connection = DriverManager.getConnection("jdbc:odbc:<your database here>", "<username>", "<password>");.

  If your database ir running on a remote host change the jdbc:odbc to: jdbc:odbc://www.somecompany.com:400/database

  This statemtn: rs = statement.executeQuery("SELECT * FROM Emails"); assumes that you have a table caleld "Emails". If your table is called something else then change the "mail" to the name of your table.

  This one:
    rs.next();
    System.out.println("data is: " + rs.getString("surname"));

  reads only one column that its name is "surname". You have to change ti to match the name of the column in your table.

   If you want to read all the data you have to use a while loop:

  while(rs.next())
  {
    System.out.println(rs.getString("<columnname>");
    System.out.println(rs.getString("<anothercolumnname>");
    ...
    ...
  }

  or if you know the number of columns use a number instead:

  while(rs.next())
  {
    System.out.println(rs.getString(1);
    System.out.println(rs.getString(2);
    ...
    ...
  }

  Hope it helps.
0
 
lhshyongCommented:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

You can use this code to connect to MSAccess through ODBC.
You also need to setup in the ODBCDataSource,

then you can use package java.sql to update, select..with the database.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
girionisCommented:
 First of all, the simplest example, using type 1 drivers (i.e. ODBC:JDBC bridge) you have to configure a MS-Access database to use. You can do it from Start.. Settings... Control Panel... ODBC Data sources.

  After you configure the database you need to load the drivers using:

// Load the JDBC-ODBC bridge driver
try {
      Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
    } catch( ClassNotFoundException ee) {  
        ee.printStackTrace();
    }

and connect to the database using:

 // Connect to the database
Conenction con = DriverManager.getConnection("jdbc:odbc:<the name of your dataabse here>", "<username>", "<password>");

  where username and password are the suername and password required to connect to the database.

  After this you have to create a statement object like:

Statement stmt = con.createStatement();

 and send simpel SQL commands like:

ResultSet rs = stmt.executeQuery("SELECT * FROM <table name>");

  where table name is the table you want to access the data from. Teh ResultSet contaisn all the results that you get back from the tabale.

  Hope it helps.
0
 
DanC3Author Commented:
I'm new to Java and I need a working example if possible.

I can create the Access database/table easy enough.
If I had a complete a ?.java I can modify it down to meet my needs.

Anyone have something close?
0
 
raid999Commented:
I dont know what operating systems are you using but any way go to the control pannel and then try to find the ODBC then create a connection from odbc to the Access database and then you can use this code in java:

try{

                    Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

               }catch(Exception e){

               }

               try{
                    String url = "jdbc:odbc:UGC";
                    Connection con = DriverManager.getConnection(url);
                    Statement stmt = con.createStatement();
                    String dateid;
                    String date;
                         //list categories
                         ResultSet rs = stmt.executeQuery("SELECT * FROM dates");
                   

                         while(rs.next()){
                         
                              date=rs.getString("Date");
                              dateid=""+rs.getInt("Date_ID");
                              out.println("<p align=\"left\"><a href=\"http://80.195.36.75:8080/servlet/LookupServlet?Date="+date+"\">"+date+"</a></p>");
                         
                             
                         }
                    rs.close();
                         
                   
                    out.println("");
                    con.close();

               }catch(SQLException ex){
                    ex.printStackTrace(out);
                    out.println("<br/>");
               }
0
 
raid999Commented:
Sorry for got one thing The name that u create in the ODBC you use it insted of my UGC in the url STRING
____________
cheers
0
 
DanC3Author Commented:
girionis, yours was the first (easiest for me) solution I tried and yours is what I will accept.  Thank you very much! DanC3


raid999, your solution is similar but harder for me to understand (especally the URL part, I said I was new to Java).  Give me a chance to try your solution and I will setup a new question titled "Points for raid999".  I need to understand: 1)UGC - is it an MS Access DB named 'dates'?, 2) what is the href=\"http://... adding to the out.println method?.
Give me a chance and I will test your code.

Thanks all,
DanC3
0
 
raid999Commented:
DanC3,

   UGC is the ODBC Name so when you create an ODBC connection calle it UGC or just change the name and change it in the code.

   the second this was the out.println("http://........") this will retreve what ever data i needed from the database and then have that URL?date=+date that means when i click the next servlet will now the date that i have choosen.

  If you have any other question please ask?

________________
Cheers
0
 
girionisCommented:
 DanC3 I am glad I helped :-) If you have any more questions please post.
0
 
DanC3Author Commented:
girionis,
Do you have code that does not use the JDBC Bridge method?  
For a desktop Java application what database(s) are available besides MS Access or Oracle that are license free.  The anticipated project DB needs are very limited, as in we are above using plain .txt files but should have some limited SQL capability, but definatly license free.
I know about MS Jett and MSDB but what do Java developers prefer?


0
 
girionisCommented:
 My personall preference is MySQL. It is completely free to use and the drives come at no cost. Take a look here: http://www.mysql.com/

  Hope it helps.
0
 
DanC3Author Commented:
What I asked for was perfect for my needs.

Thanks again Girionis!
0
 
girionisCommented:
 Thank you for the points. I am glad I helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.