Know how to connect Microsoft Access Database using JDBC..!!!

Interested in connecting to Ms Access using JDBC..!!! The following post will guide you achieve this.

Microsoft has developed a data access method called ODBC, and MS Access databases understand this method. We cannot make a connection directly to an ODBC data source from Java, but Sun has provided a bridge from JDBC to ODBC. This bridge gives the DriverManager the understanding of how to communicate with an ODBC (ie a MS Access) data source.

As we know that we can get the connection object of java.sql.Connection with the use of DriverManager class. We tell the DriverManager what type of driver to use to handle the connections to databases, and from there, ask it to give us a connection to a particular database of that type.

So the first thing we'll do is set up our DriverManager and let it know that we want to communicate with ODBC data sources via the JDBC:ODBC bridge. We do this by calling the static forName() method of the Class class. Here is an entire program that accomplishes what we're after:

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

Now their are two ways to get Connection Ms Acess :

1)Get a connection by direct access: I will not suggest you to go for this approach, but still for your understanding below is the code for this approach. Here get a connection is to go directly after the MS Access database file

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String filename = "c:/java/customerInfo.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection( database ,"","");
}

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

2)Set up a DSN and get a connection through that: This approach is the recommended one.

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!

As we learned earlier, 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:
* 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.
* 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:

String dataSourceName = "customerInfo";
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 );
}


Now you have established connection with your database successfully.