Example, MS Access Java Application

Hey guys,

I've been programming for quite some time in other languages, but I'm still real green when it comes to Java. Can anyone provide me with an example Java Application or Applet that shows very simply, how I would create, connect, and query an MS Access database using ODBC?

I've got millions of books, but I'm the kind of person who learns best by actually working on something, rather than sitting down in a chair and reading a reference manual. I just don't have the attention span for it.

Any help would greatly be appreciated.

Thank you!

Todd

Todd,

Here's a simple one a did a while ago. You might get more response if you posted this in the JDBC forum. Using this method, you need a ODBC data source (in this case named POData). You can also do what is know as a DSNless connection by using the follow URL as the ODBC_SOURCE:

import java.sql.*;
 
public class DumpSchema
{
  // JDBC Stuff
  private static final String ODBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
  private static final String ODBC_SOURCE = "jdbc:odbc:POData";
 
  public static void main(String[] args) 
  {
    new DumpSchema();
  }
 
  public DumpSchema()
  {
    try
    {
      Class.forName( ODBC_DRIVER);
      Connection ODBCConnection = DriverManager.getConnection( ODBC_SOURCE);
      DatabaseMetaData metadata = ODBCConnection.getMetaData();
      try
      {
        ResultSet tableNames = metadata.getTables( null, null, null, new String[] {"TABLE"});
        while( tableNames.next())
        {
          String table = tableNames.getString( 3);
          try
          {
            ResultSet columnNames = metadata.getColumns( null, null, table, null);
            System.out.println( "TABLE: " + table);
            while( columnNames.next())
            {
              System.out.println( "\t" + columnNames.getString(4));  //Field name
              System.out.println( "\t\tDescription: " + columnNames.getString(12));
              System.out.println( "\t\tType: " + columnNames.getString(6));
              System.out.println( "\t\tSize: " + columnNames.getString(7));
              System.out.println( "\t\tDecimal Digits: " + columnNames.getString(9));
              System.out.println( "\t\tAllow Nulls? " + columnNames.getString(18));
              System.out.println( "\t\tDefault Value: " + columnNames.getString(13));
            }
            columnNames.close();
          }
          catch( SQLException sqle)
          {
            System.out.println( "Unable to get column information for table '"
                + tableNames.getString(3) + "'\n" + e);
          }
        }
        tableNames.close();
      }
      catch( SQLException sqle)
      {
        System.out.println( "Unable to get table information\n" + e);
      }
 
      ODBCConnection.close();
    }
    catch( SQLException sqle)
    {
      System.out.println( "Error connecting to PO database\n" + e);
    }
  }
}

Cheers,

[ September 22, 2005: Message edited by: Tom Blough to add DSNless info]
[ September 22, 2005: Message edited by: Tom Blough ]

Thank you Tom, I really appreciate it!!!

Todd,

One thing I should point out is all the "magic numbers" in columnNames.getString() methods. Those are documented in the Java API for DatabaseMetaData.getColumns().

Have fun,

Originally posted by Tom Blough:
Todd,

One thing I should point out is all the "magic numbers" in columnNames.getString() methods. Those are documented in the Java API for DatabaseMetaData.getColumns().

Have fun,

Hey Tom, one quick question,

it does error out when it attempts to connect to the database...

for the ODBC / PATH string, I have:

I also include java.sql.*;

Is there anything that I am missing that I should have done?

The error I get is:

"Error connecting to PO Database
java.lang.ClassNotFoundException: sun.jdbc.odbc.jdbcodbcdriver"

Thanks!!

Todd

Todd,

Did you set up idxAnalyzerCustom as a user or system data source? If you use that form you need to configure an ODBC datasouce on your machine with that name. For MS Access you can use the alternative URL for the getConnection method that specifies the file/path to the .mdb file that I wrote about.

Java is also case sensitive. sun.jdbc.odbc.JdbcOdbcDriver is not the same as sun.jdbc.odbc.jdbcodbcdriver. The former is correct.

Tom

As Tom points out, you have the wrong class name for the driver. Notice that since your code has the class name hiding in a String, the compiler cannot catch any errors if you use the wrong name. Instead, the JVM will complain because the class is incorrect.

HTH

Layne

Originally posted by Tom Blough:
Todd,

Did you set up idxAnalyzerCustom as a user or system data source? If you use that form you need to configure an ODBC datasouce on your machine with that name. For MS Access you can use the alternative URL for the getConnection method that specifies the file/path to the .mdb file that I wrote about.

Java is also case sensitive. sun.jdbc.odbc.JdbcOdbcDriver is not the same as sun.jdbc.odbc.jdbcodbcdriver. The former is correct.

Tom

Damn! That must be it then...

I did: "sun.jdbc.odbc.jdbcodbcdriver"

Yeah, I already have an ODBC set up with that exact name. "idxCustomAnalyzer".

Thanks everyone for all the help, I really appreciate it.

I'm used to Delphi and Visual Basic, neither of which care very much for the case of the variables and whatnot.

This is very similar to Mumps with respect to case sensitivity.

Thanks!!!

Thank You