How to communicate with a MS Access database from a Java program.
quote:Originally posted by colombo_ar
I would like to write a program in Java that retrieves data from an MSACCESS database usign jdbc (direct) without odbc. Where can I get the driver for Access database and how to write the code for the connection or find an example of it?.
First Register the database with the Windows Naming Service
1. Click Start|Settings|Control Panel You will see an icon which sets up an ODBC data source within the control panel. This is a simple naming system which allows your Java programs to communicate with a Microsoft database. The name of the icon will be different on each Windows version, for example it will be marked ODBC Data Sources on NT4 and be found in a folder named Administrative tools and called Data Sources (ODBC) in Windows 2000.
2. Double click this icon.
3. Click the add button on the window that appears.
4. On the next window double click the line starting Microsoft Access Driver.... A new window should then appear.
5. In the field marked Data Source Name enter a ‘nickname’ for your database i.e."dbNickName", then click the Select button.
6. Navigate to the file containing the actual database; you will know that you have reached it when the name of the file (yourDatabase.mdb) appears in the leftmost text area.
7. Select the file by clicking it and then click OK.
8. Get rid off all the windows that you have opened by clicking OK on all of them.
The database file can now be referred to as “dbNickName” within your Java programs.
How to establish a connection to the database (import java.sql.*;)
1. Load a compatible driver. (Inside a Try – Catch block)
2. Create a connection object. (Try – Catch ) Supplying a protocol & DB name, userName, and password.
connect = DriverManager.getConnection(“jdbc:odbc: dbNickName”, “userName”, “password”);
3. Create your SQL statement
String sqlString = “select * from aTable”;
4. Create a Statement object and associate it with the Connection object.
Statement stat = connect.createStatement();
5. Execute the SQL statement and store the result in a ResultSet object.
ResultSet res = stat.executeQuery(sqlString);
6. Extract the data record(s) from the ResultSet object using code inside a while(res.next()) loop. If your table contains three columns each holding a String, String and Integer respectively then the data can be retrieved by using res.getString(1), res.getString(2) and res.getInt(3). Where the arguments represent the position of each column in the database table (the actual table names can also be used if preferred).
You will probably want to store the retreived data as objects in a vector and create an Enumeration object from this vector in order to manipulate the data.
7. Close the Statement, Connection and ResultSet objects