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!
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:
- Open Windows' ODBC Data Source Administrator as follows:
o 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.
o In Windows 2000, choose Start > Settings > Control Panel > Administrative Tools > Data Sources. - In the ODBC Data Source Administrator dialog box, click the System DSN tab.
- Click Add to add a new DSN to the list.
- Scroll down and select the Microsoft Access (.MDB) driver
- Type in the name "mdbTEST" (no quotes, but leave the cases the same) for the Data Source Name
- 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!
- 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:
import java.sql.*; public class Test { public static void main(String[] args) { // change this to whatever your DSN is String dataSourceName = "mdbTEST"; 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 ); } } }
Running a SQL Statement on your Access Database
Once you have your connection, you can manipulate data within the database. In order to run a SQL query, you need to do 2 things: 1. Create a Statement from the connection you have made 2. Get a ResultSet by executing a query (your insert/delete/etc. statement) on that statement Now lets learn how to make a statement, execute a query and display a the ResultSet from that query. Refer to the following complete program for an understanding of these concepts (details follow):
import java.sql.*; public class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); /* the next 3 lines are Step 2 method 2 from above - you could use the direct access method (Step 2 method 1) istead if you wanted */ String dataSourceName = "mdbTEST"; String dbURL = "jdbc:odbc:" + dataSourceName; Connection con = DriverManager.getConnection(dbURL, "",""); // try and create a java.sql.Statement so we can run queries Statement s = con.createStatement(); s.execute("create table TEST12345 ( column_name integer )"); // create a table s.execute("insert into TEST12345 values(1)"); // insert some data into the table s.execute("select column_name from TEST12345"); // select the data from the table ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query if (rs != null) // if rs == null, then there is no ResultSet to view while ( rs.next() ) // this will step through our data row-by-row { /* the next line will get the first column in our current row's ResultSet as a String ( getString( columnNumber) ) and output it to the screen */ System.out.println("Data from column_name: " + rs.getString(1) ); } s.execute("drop table TEST12345"); s.close(); // close the Statement to let the database know we're done with it con.close(); // close the Connection to let the database know we're done with it } catch (Exception err) { System.out.println("ERROR: " + err); } } }
Comments
I like kittens