All geek questions in one place

Create an Access database file (.mdb or .accdb) using Java

I currently have one application in which I can access the .mdb or .accdb file from the JdbcOdbcDriver to add some data.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");

but at the same time I need to configure the system DSN. We need to add a new data source (Microsoft Access Driver), and then specify the location of the .mdb file. Only then will it work on the code.

Suppose I want to run the application on a different system, then I need to do the same with this computer. If I transfer my application to the client and he / she does not know how to configure the .mdb file. Then all my efforts will be in vain. Thus, any driver is available with which I create a .mdb file in my Java code, and then add all the data to the table of the .mdb file. Or there is some other way where Java code can create a .mdb file and have access to this database file.

I tried this code that adds data without setting up System DNS:

 public class TestMsAccess { private static Connection con; private static Statement stm; private static String tableName = "EmpDetail"; private static int id_is = 2; private static String name_is = "Employee1"; public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\MSAccessProject/Employee.mdb", "", ""); stm = con.createStatement(); // enter value into table String addRow = "INSERT INTO " + tableName + " VALUES ( " + id_is + ", '" + name_is + "')"; stm.execute(addRow); if (con != null) { con.close(); } if (stm != null) { stm.close(); } } }

But the problem is that this code does not automatically create the .mdb file, but it works when I create the .mbd file and table before running this code.

+5
source share
5 answers

Update for Jackcess 2.x: now databases are created (or opened) using DatabaseBuilder , so to create a new database file we do

 import java.io.File; import java.io.IOException; import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.Database.FileFormat; import com.healthmarketscience.jackcess.DatabaseBuilder; public class JackcessDemoMain { public static void main(String[] args) { String dbPath = "C:/Users/Public/newDb.accdb"; // using try-with-resources is recommended to ensure that // the Database object will be closed properly try (Database db = DatabaseBuilder.create(FileFormat.V2010, new File(dbPath))) { System.out.println("The database file has been created."); } catch (IOException ioe) { ioe.printStackTrace(System.err); } } }

Original answer for Jackcess 1.x (deprecated):

If you want to create a ".mdb" file through java, you can use the Jackcess Java library, which is one of the pure Java libraries for reading and writing to MS Access databases. Supporting versions currently include 2000-2007, I think. Please see the example below for a better understanding:

 package com.jackcess.lib; import com.healthmarketscience.jackcess.ColumnBuilder; import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.Table; import com.healthmarketscience.jackcess.TableBuilder; import java.io.File; import java.io.IOException; import java.sql.SQLException; import java.sql.Types; /** * * @author sarath_ivan */ public class JackcessLibrary { private static Database createDatabase(String databaseName) throws IOException { return Database.create(new File(databaseName)); } private static TableBuilder createTable(String tableName) { return new TableBuilder(tableName); } public static void addColumn(Database database, TableBuilder tableName, String columnName, Types sqlType) throws SQLException, IOException { tableName.addColumn(new ColumnBuilder(columnName).setSQLType(Types.INTEGER).toColumn()).toTable(database); } public static void startDatabaseProcess() throws IOException, SQLException { String databaseName = "C:/Users/compaq/Desktop/employeedb.mdb"; // Creating an MS Access database Database database = createDatabase(databaseName); String tableName = "Employee"; // Creating table Table table = createTable(tableName) .addColumn(new ColumnBuilder("Emp_Id").setSQLType(Types.INTEGER).toColumn()) .addColumn(new ColumnBuilder("Emp_Name").setSQLType(Types.VARCHAR).toColumn()) .addColumn(new ColumnBuilder("Emp_Employer").setSQLType(Types.VARCHAR).toColumn()) .toTable(database); table.addRow(122875, "Sarath Kumar Sivan","Infosys Limited.");//Inserting values into the table } public static void main(String[] args) throws IOException, SQLException { JackcessLibrary.startDatabaseProcess(); } }
+13
source

Now that the JDBC-ODBC bridge has been removed from Java (starting with Java 8), future readers may be interested in UCanAccess , a free, open source Java-JDBC driver for Access databases. UCanAccess includes a connection parameter newdatabaseversion , which will create an Access.accdb or .mdb file if it does not already exist.

Code example:

 String dbFileSpec = "C:/Users/Gord/Desktop/myDb.accdb"; try (Connection conn = DriverManager.getConnection( "jdbc:ucanaccess://" + dbFileSpec + ";newdatabaseversion=V2010")) { DatabaseMetaData dmd = conn.getMetaData(); try (ResultSet rs = dmd.getTables(null, null, "Clients", new String[] { "TABLE" })) { if (rs.next()) { System.out.println("Table [Clients] already exists."); } else { System.out.println("Table [Clients] does not exist."); try (Statement s = conn.createStatement()) { s.executeUpdate("CREATE TABLE Clients (ID COUNTER PRIMARY KEY, LastName TEXT(100))"); System.out.println("Table [Clients] created."); } } } conn.close(); }

For more information on configuring UCanAccess, see

Manipulating an Access Database with Java without ODBC

+3
source

You can use the method below instead of setting up System DSN on your computer.

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/Users/Desktop/your-database-file.mdb", "", "");

Here "your-database-file.mdb" is your MS-Access file. You can specify the full path to the database file in your code to establish a connection. You can also save the database file in the project (application) folder. In this case, you will be able to transfer the database file along with the application to the client, and he / she will be able to use your application without a DSN configuration.

Hope this serves your purpose!

Thank you!

+1
source

download boot library

use this library that will create the .mdb file. Below is the snipet code, download the exit libraries from the above location. add the required jar file to the class path.

`

 import java.io.File; import java.io.IOException; import java.sql.SQLException; import java.sql.Types; import com.healthmarketscience.jackcess.ColumnBuilder; import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.DatabaseBuilder; import com.healthmarketscience.jackcess.Table; import com.healthmarketscience.jackcess.TableBuilder; public class MDBFileGenerator { public static void main(String[] args) throws IOException, SQLException { Database db = DatabaseBuilder.create(Database.FileFormat.V2000, new File("new.mdb")); Table newTable = new TableBuilder("NewTable") .addColumn(new ColumnBuilder("a").setSQLType(Types.INTEGER)) .addColumn(new ColumnBuilder("b").setSQLType(Types.VARCHAR)) .toTable(db); newTable.addRow(1, "foo"); } }

`

+1
source

It seems that there is at least one option for connecting directly to .mdb without JdbcOdbcDriver, that this parameter is commercial. See here. If customization is what you are trying to avoid, have you considered using something built-in database like sqlite ?

0
source

Source: https://habr.com/ru/post/1399523/

More articles:


All Articles