Direct JDBC Connection to Microsoft Access Databases
Direct JDBC Connection to Microsoft Access Databases (.MDB)
Accessing .MDB Files Without ODBC Data Source Configuration
This document demonstrates how to establish a direct JDBC connection to a Microsoft Access database (.MDB file) in Java, bypassing the need for an ODBC data source setup in the system’s ODBC Data Source Administrator panel.
Java Class Definition
The main class, Access_sin_odbc, encapsulates the database interaction logic.
import java.sql.*;
public class Access_sin_odbc {
Main Program Entry Point
The program’s execution begins in the main method, where a try-catch block handles potential SQL exceptions.
public static void main(String[] args) {
try {
System.out.println("\nJDBC Access");
System.out.println("===========");
Database Connection String Setup
The connection string specifies the path to the Access database file. Two examples are provided:
- A full physical path (e.g.,
D:\folder\subfolder\AccessBD_1.mdb). - A relative path, assuming the database file is in the Java application’s folder.
The JDBC-ODBC bridge URL format is used to connect to the MS Access database.
// Connection string with the physical path to the DB
String db = "D:\\folder\\subfolder\\AccessBD_1.mdb";
// If the DB is in the folder of the Java application
// String db = "AccessBD_1.mdb";
String url = "jdbc:odbc:MS Access Database;DBQ=" + db;
Registering the JDBC-ODBC Bridge Driver
The sun.jdbc.odbc.JdbcOdbcDriver is registered using Class.forName(). This driver acts as a bridge between JDBC and ODBC.
// Register the JDBC driver using the Class.forName classloader
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("\nEstablishing connection...");
Establishing Database Connection
A connection to the database is established using DriverManager.getConnection() with the constructed URL. Empty strings are passed for username and password, as Access databases typically don’t require them for direct file access.
Connection con = DriverManager.getConnection(url, "", "");
System.out.println("\nConnection established: \"" + db + "\".");
System.out.println("");
Executing a SQL SELECT Query
A Statement object is created to execute SQL queries. A SELECT TOP 8 * FROM Table1 ORDER BY Id query retrieves the first 8 records from ‘Table1’, ordered by ‘Id’.
// Execute a SQL SELECT statement
Statement select = con.createStatement();
ResultSet names = select.executeQuery("SELECT TOP 8 * FROM Table1 ORDER BY Id");
System.out.println("Showing the first 8 records:");
System.out.println("");
System.out.println("\tfirst_name");
System.out.println("");
Iterating Through Query Results
The ResultSet contains the query results. The code iterates through the first 8 records, printing the value from the column named “Name”.
Note: The original code used the Java keyword continue as a variable name, which is a compilation error. It has been corrected to hasMoreRecords in the code snippet below for proper functionality. Similarly, the variable go was also renamed to hasMoreRecords.
int col = names.findColumn("Name");
boolean hasMoreRecords = names.next(); // Original: boolean continue = nombres.next();
while (hasMoreRecords) {
System.out.println("\t" + names.getString(col));
hasMoreRecords = names.next(); // Original: go = nombres.next();
}
System.out.println("");
Releasing Resources (ResultSet and Statement)
It is crucial to close ResultSet and Statement objects to free up database resources.
// Free resources
names.close();
select.close();
Retrieving Database and Driver Metadata
The DatabaseMetaData interface provides comprehensive information about the database and its driver. A ResultSet is used to access data, with a cursor initially positioned before the first record. The resultSet.next() method moves the cursor through the records.
Note: The original code had a syntax error in assigning the ResultSet from dm.getCatalogs(). This has been corrected to rs = dm.getCatalogs();.
// Get information about the database manager and database
/* The ResultSet interface represents a set of data resulting from a SQL query
* For access to records that uses a cursor initially points before the first record
* To move through the records resultSet.next method is used () */
DatabaseMetaData dm = null;
ResultSet rs = null;
dm = con.getMetaData();
rs = dm.getCatalogs(); // Corrected from 'dm.getCatalogs rs = ();'
Displaying Driver and Database Server Information
The program prints details about the JDBC driver and the connected database server, including their names and versions.
System.out.println("Information on the Driver:\n");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\nInformation on DB server:\n");
System.out.println("\tDatabase Name: " + dm.getDatabaseProductName());
System.out.println("\tDatabase Version: " + dm.getDatabaseProductVersion());
Listing Databases in the Catalog
The dm.getCatalogs() method returns a ResultSet containing the available database catalogs (which, for Access, typically means the database files themselves). The code iterates through this ResultSet to list each database found.
System.out.println("\nDatabases in this folder:\n"); // Original: NBAS data
// To list the entire catalog of DB
int n = 1;
while (rs.next()) {
// ResultSet.getString() returns the path to the DB in the catalog,
System.out.println("\t" + n + "-" + rs.getString(1));
n += 1;
}
Closing Remaining Resources
Finally, the remaining ResultSet and the Connection objects are closed to ensure all resources are properly released.
// Free resources
rs.close();
con.close();
System.out.println("\nConnection with: \"" + db + "\" closed. \n\n");
Error Handling
The catch block handles any exceptions that occur during the database operations, printing a generic error message.
} catch (Exception e) { // Original: catch (Exception chicken)
// System.out.println("\nError connecting to the DB:" + pollo.getMessage() + "\n"); // Original: pollo
System.out.println("\nError performing some action of the program. \n\n"); // Original: Error to perform some action
}
}
}