Abstract:This Java program retrieves the latest data from an Access database table via ODBC and inserts it into a PostgreSQL database table. The program also handles a timestamp column and multiple rows of data.


  1. Install the JDBC driver for PostgreSQL in your Java environment.
  2. Set up the ODBC connection to the Access database and the PostgreSQL database connection details in the program.
  3. Modify the SQL statements in the program to match the table structures and column names of the Access and PostgreSQL tables.
  4. Compile the program and run it using the command java Main.
  5. The program will retrieve the latest data from the Access table and insert it into the PostgreSQL table. The timestamp column will be handled automatically, and multiple rows of data can be processed in a single run.
import java.sql.*;
import java.util.Calendar;

public class AccessToPostgres {
    public static void main(String[] args) {
        Connection accessConn = null;
        Connection postgresConn = null;
        Statement accessStmt = null;
        PreparedStatement postgresStmt = null;
        ResultSet rs = null;

        try {
            // Connect to Access database via ODBC
            String accessUrl = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\access\\database.accdb;";
            String accessUser = "";
            String accessPass = "";
            accessConn = DriverManager.getConnection(accessUrl, accessUser, accessPass);

            // Connect to PostgreSQL database
            String postgresUrl = "jdbc:postgresql://localhost:5432/mydb";
            String postgresUser = "myuser";
            String postgresPass = "mypassword";
            postgresConn = DriverManager.getConnection(postgresUrl, postgresUser, postgresPass);

            // Prepare statement for inserting data into PostgreSQL table
            String insertSql = "INSERT INTO mytable (id, name, timestamp) VALUES (?, ?, ?)";
            postgresStmt = postgresConn.prepareStatement(insertSql);

            // Retrieve the latest records from Access table
            accessStmt = accessConn.createStatement();
            String selectSql = "SELECT TOP 10 id, name, timestamp FROM mytable ORDER BY timestamp DESC";
            rs = accessStmt.executeQuery(selectSql);

            // Loop through the result set and insert the data into PostgreSQL
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Timestamp timestamp = rs.getTimestamp("timestamp");
                postgresStmt.setInt(1, id);
                postgresStmt.setString(2, name);
                postgresStmt.setTimestamp(3, timestamp);

            System.out.println("Data transfer completed successfully!");

        } catch (ClassNotFoundException e) {
        } catch (SQLException e) {
        } finally {
            try {
                if (rs != null) rs.close();
                if (accessStmt != null) accessStmt.close();
                if (accessConn != null) accessConn.close();
                if (postgresStmt != null) postgresStmt.close();
                if (postgresConn != null) postgresConn.close();
            } catch (SQLException e) {

By Ray Lee (System Analyst)

iDempeire ERP Contributor, 經濟部中小企業處財務管理顧問 李寶瑞

Leave a Reply

Your email address will not be published. Required fields are marked *