Database - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Question

I'm getting the following errors messages when I'm trying to run my database program. This is one of the files I'm getting issues from what I'm understanding.

Thanks in advance for your help!

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3080)
    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
    at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at data.DbManager.getAccessDbConnection(DbManager.java:201)
    at data.DbManager.<init>(DbManager.java:26)
    at user.Frame.<init>(Frame.java:10)
    at user.MainP8.main(MainP8.java:16)

DbManager.java

package data;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;

public class DbManager {

//Add to beginning of MS Access DB URL
private  String ACCESS_DB_URL_PREFIX =
    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
//Add to end of MS Access DB URL
private final String ACCESS_DB_URL_SUFFIX = ";DriverID=22;READONLY=false;}";
//File name of database
private final String MY_DB_NAME = "WebsiteDatabase.mdb";

private String fileName;
private Connection myConnection;

//constructor
public DbManager() {
    try {
        myConnection = getAccessDbConnection(MY_DB_NAME);
        myConnection.setAutoCommit(true);
        DatabaseMetaData md = myConnection.getMetaData();
    } catch (SQLException ex) {
        Logger.getLogger
                    (DbManager.class.getName()).log(Level.SEVERE, null, ex);
        JOptionPane.showMessageDialog(null,
            "The database could not be located. Please select the database"
            + " file you wish to connect to.",
            "Database Error", JOptionPane.ERROR_MESSAGE);
        JFileChooser chooser = new JFileChooser();
        chooser.showOpenDialog(chooser);
        fileName = chooser.getSelectedFile().toString();
        try {
            myConnection = getAccessDbConnection(fileName);
            myConnection.setAutoCommit(true);
            DatabaseMetaData md = myConnection.getMetaData();
        } catch (SQLException ex1) {
            Logger.getLogger
                   (DbManager.class.getName()).log(Level.SEVERE, null, ex1);
            JOptionPane.showMessageDialog(null,
                "The database could not be opened", "Fatal Error",
                JOptionPane.ERROR_MESSAGE);
        }
    }
}

//"destructor" method to release the database connection
public void close() {
    try {
        myConnection.close();
    } catch (SQLException ex) {
        Logger.getLogger
                    (DbManager.class.getName()).log(Level.SEVERE, null, ex);
    }
}

//public methods to access the database
public void insert(Website w) throws SQLException {
    String sql;
    //build SQL statement
    sql = "INSERT INTO Websites";
    sql += " (COMPANY_NAME, COMP_ASSETS, YR_FOUNDED, URL_ADD, ALEXA_RANK)";
    sql += " VALUES (";
    sql += "'" + w.getCompName() + "',";
    sql += w.getAssets() + ",";
    sql += " #" +w.getFounded() + "#,";
    sql += " '" + w.getUrl() + "',";
    sql += w.getAlexaRank() + ");";
            insertRecord(sql);
}
public void update(Website w) throws SQLException {
    String sql;
            //SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
            //date.parse(w.getFounded());
//                "#"
    //build SQL statement
    sql = "UPDATE WebsiteS SET";
    sql += " COMPANY_NAME = '" + w.getCompName() + "',";
    sql += " COMP_ASSETS = " + w.getAssets() + ",";
    sql += " YR_FOUNDED = #" + w.getFounded() + "#,";
    sql += " URL_ADD = '" + w.getUrl() + "',";
            sql += " ALEXA_RANK = " + w.getAlexaRank() ;

    sql += " WHERE ID = " + w.getId() + ";";
    updateRecord(sql);
}
public void delete(Website w) throws SQLException {
    String sql;
    sql = "DELETE * FROM Websites WHERE ID = " + w.getId() + ";";
    deleteRecord(sql);
}
public String[] getWebsiteList() throws SQLException {
    String strSql = "SELECT COMPANY_NAME FROM Websites;";
    PreparedStatement ps = myConnection.prepareStatement(strSql,
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    ResultSet rs = ps.executeQuery();

    rs.last();
    int rowCount = rs.getRow();
    String[] items = new String[rowCount];

    try {
        rs.beforeFirst();
        int i = 0;
        while(rs.next()) {
            items[i] = rs.getString("COMPANY_NAME");
            i++;
        }
    } catch (Exception ex){
        JOptionPane.showMessageDialog(null,
            "getWebsiteList: Unable to read website names: " + ex.getMessage());
        System.out.println(ex.getStackTrace());
        System.out.println(ex.getLocalizedMessage());
    }

    return items;
}
public int[] getWebsiteIds() throws SQLException {
    int[] id;
    String strSql = "SELECT ID FROM Websites;";
    PreparedStatement ps = myConnection.prepareStatement(strSql,
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    ResultSet rs = ps.executeQuery();

    rs.last();
    int rowCount = rs.getRow();
    id = new int[rowCount];

    try {
        rs.beforeFirst();
        int i = 0;
        while(rs.next()) {
            id[i] = rs.getInt("ID");
            i++;
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
            "getWebsiteIDs: Unable to read Website IDs: " + ex.getMessage());
        System.out.println(ex.getStackTrace());
        System.out.println(ex.getLocalizedMessage());
    }

    return id;
}
public Website getWebsite(int wId) throws SQLException {
    String[] rec;
    String strSql = "SELECT * FROM Websites WHERE ID = " + wId + ";";
    Website website = null;
    PreparedStatement ps = myConnection.prepareStatement(strSql,
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsMeta = rs.getMetaData();
    int columns = rsMeta.getColumnCount();
    rec = new String[columns];
    try {
        rs.beforeFirst();
        while(rs.next()) {
            for (int i = 0; i < columns; i++) {
                rec[i] = rs.getString(i + 1);
            }
        }

        //use the data to build the Website object
        website = new Website(
            //Integer.parseInt(rec[0]),
            rec[0],
            rec[1],
            rec[2],
            rec[3],
                            rec[4],
                            rec[5]
        );
    } catch (SQLException ex) {
        System.out.println(ex.getStackTrace());
        System.out.println(ex.getLocalizedMessage());
    }
    return website;
}

//private method to establish database connection
private Connection getAccessDbConnection(String fileName)
                                                       throws SQLException {
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    } catch (ClassNotFoundException ex) {
        System.err.println("JdbcOdbc Bridge Driver not Found");
        JOptionPane.showMessageDialog(null, ex.getMessage(), "Driver Error",
            JOptionPane.ERROR_MESSAGE);
        System.exit(0);
    }

    String databaseURL = ACCESS_DB_URL_PREFIX + fileName
                                                     + ACCESS_DB_URL_SUFFIX;
    return DriverManager.getConnection(databaseURL);
}

//private methods to access the database
private void insertRecord(String strSql) throws SQLException {
    Statement st = myConnection.createStatement();
    try {
        st.execute(strSql);
    } catch (SQLException ex) {
        System.err.println(ex.getStackTrace());
        System.err.println(ex.getMessage());
        System.err.println(ex.getLocalizedMessage());
    }
    st.close();
}
private void updateRecord(String strSql) throws SQLException {
    //use prepared statement to ensure that the result set is editable
    PreparedStatement ps = myConnection.prepareStatement(strSql,
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    try {
        ps.execute();
    } catch (SQLException ex) {
        System.err.println(ex.getStackTrace());
        System.err.println(ex.getMessage());
        System.err.println(ex.getLocalizedMessage());
    }
}
private void deleteRecord(String strSql) throws SQLException {
    PreparedStatement ps = myConnection.prepareStatement(strSql,
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    try {
        ps.execute();
    } catch (SQLException ex) {
        System.err.println(ex.getStackTrace());
        System.err.println(ex.getMessage());
        System.err.println(ex.getLocalizedMessage());
    }
}
}

Answer 1

Check

1. You have the MS Access driver installed

2. The descriptive string you use is correct, "Microsoft Access Driver (.mdb)" It has to be exact, check in the Drivers tab in the ODBC control panel app, mine for example is (.mdb, *.accdb)

3. Check you are not mixing a 64 bit JVM with a 32 bit driver or vice versa.

Answer 2

If you are using a 32-bit operating system then I think there would be no such issue, but if you are using a 64-bit OS, then follow these steps:

1. Download 64-bit access driver from here.
2. Run setup, then restart your computer.
3. Go to Start -> Control Panel -> Administrative Tools -> (Data Sources) ODBC -> System DSN.
4. Click the "add" button.
5. Choose "Microsoft Access Driver (*.mdb)"
6. Provide any data source name, then click "create".
7. Choose the directory where you want your database (note: the directory should be same as directory of source directory for your Java programs)
8. Start command prompt for execution of 64bit database connectivity.
9. Copy C:\WINDOWS\SYSWOW64.exe in run dialog box of your computer, then their start your compilation and execution of your database program.