How can I add a password to this JDBC:ODBC connection string that is trying to connect to an MS Access database

This is the c开发者_高级运维onnection string that is currently working on a non-password protected MS Access database.

this code snippet is from our properity file:

db.url = jdbc:odbc:Driver\={Microsoft Access Driver (*.mdb)};Dbq\=C:\Inventory.mdb;DriverID\=22;READONLY\=true

How do I add a password to this connection string for a MS Access DB protected by a database password (Non-ULS)?

Thanks!

Referenced from here: Java Support

db.url = jdbc:odbc:Driver\={Microsoft Access Driver (*.mdb)}Dbq\=C:\Inventory.mdb;DriverID\=22;READONLY\=true; UID\=me;PWD\=secret

To work on password protected MS Access Database 2003/2007 use the below code snippet

package jdbcExample;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCExampleOfMSAccess2007 
{
    public static void main(String[] args) 
    {
        System.out.println("Start of Program");
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        String url=null,userID=null,password=null;
        String dbFileName=null;
        String sql=null;

        dbFileName = "C:\\temp\\MYTestDatabase.accdb";
        userID = "Admin";
        password = "Ganesh@123";
        <b>url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};"+
                "DBQ="+dbFileName+";"+
                "Uid="+userID+";"+
                "Pwd="+password+";"; </b>
        sql = "SELECT * FROM tblUserProfile";
        System.out.println("url = "+url); 

        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection(url,userID,password);
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            if(rs!=null)
            {
                while(rs.next())
                {
                    System.out.print("User ID = "+rs.getString("User ID"));
                    System.out.print(" User Name = "+rs.getString("User Name"));
                    System.out.print(" Password = "+rs.getString("Password"));
                    System.out.println(" Access Type = "+rs.getString("Access Type"));
                }
                rs.close();
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
        try {
                stmt.close();
                con.close();
            } catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        System.out.println("End of Program");
    }
}

My connection string url was

    url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};"+
            "DBQ="+dbFileName+";"+
            "DriverID=22;READONLY=true;"+
            "Uid="+userID+";"+
            "Pwd="+password+";"; 

and I was facing one problem while connecting to MS Access Database the stack trace as below.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x162c Thread 0x1e98 DBC 0x38f5924                                                              Jet'.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at jdbc.JDBCForMSAccess2007.main(JDBCExampleOfMSAccess2007 .java:37)

To overcome this problem simply I removed the "DriverID=22;READONLY=true;" in url string and the problem get solved :) The given code snippet I have tested on password protected MS Access 2003 and 2007 Database and work well.

Hope that it will helpful to do new experiment.

I know you're asking for ODBC, but is it impossible to use OLEDB, as in the connect string provided on ConnectionStrings.com:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

I don't know that the Jet ODBC driver provides any support for database passwords, which were not introduced until Jet 4 (and are completely worthless in any version of Access/Jet/ACE).