How to run queries on Microsoft Access mdb file using Java


If you need to run queries on Microsoft Access database file (mdb) using JDBC(odbc)  driver use the following code snippets;

MSAccessUtil class

/*
 * tunatore
 */
package msaccessjavaapplication;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author tunatore
 */
public class MSAccessUtil {

//this class uses sun.jdbc.odbc.JdbcOdbcDriver
public static Connection connectToAccess () {
Connection connection = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dbConnectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
//to use on Microsoft Operating System use the following path access method
String accessFileLocation =  MSAccessUtil.class.getResource("sampledb.mdb").getPath().substring(1).replace("/", "\\");
System.out.println("location of mdb file ==> " + accessFileLocation);
String URL = dbConnectionString + accessFileLocation;
connection = DriverManager.getConnection(URL);
}catch(Exception ex) {
ex.printStackTrace();
}
return connection;
}
public static void closeConnecion(Connection conn) {

try {
if(conn !=null) {
conn.commit();
conn.close();
}

} catch (Exception ex) {
ex.printStackTrace();
}

}
public static void runSELECTQueryOnAccess(Connection conn, String SQL) {

Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
while(rs.next()) {
System.out.println(rs.getString("firstColumn")
+ " " + rs.getString("secondColumn")
+ " " + rs.getString("thirdColumn"));
}
rs.close();
stmt.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static void runINSERT_OR_DELETEQueryOnAccess(Connection conn, String SQL) {

Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.executeUpdate(SQL);
stmt.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}

}

MSAccessJavaApplication class

package msaccessjavaapplication;
import java.sql.Connection;

public class MSAccessJavaApplication {

public static void main(String[] args) {

Connection conn = MSAccessUtil.connectToAccess();
MSAccessUtil.runINSERT_OR_DELETEQueryOnAccess(conn,"DELETE FROM SAMPLETABLE");
for (int i = 0; i < 10; i++) {
 String SQL =</em> "INSERT INTO SAMPLETABLE VALUES('"+ i + "','" + i + "','" + i + "')";
 MSAccessUtil.runINSERT_OR_DELETEQueryOnAccess(conn, SQL);
 }
 MSAccessUtil.runSELECTQueryOnAccess(conn, "SELECT * FROM SAMPLETABLE");
MSAccessUtil.closeConnecion(conn);

}
 }

Output of the program;
run:
location of mdb file ==> C:\Users\tunatore\Desktop\development\MSAccessJavaApplication\build\classes\msaccessjavaapplication\sampledb.mdb
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
BUILD SUCCESSFUL (total time: 0 seconds)

Download full source (sampledb.mdb file included)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s