java如何向试图连接到MS Access数据库的JDBC:ODBC连接字符串添加密码

Question

这是当前在无密码保护的MS Access数据库上工作的连接字符串

此代码段来自我们的属性文件：

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

如何为受数据库密码（非ULS）保护的MS Access DB向此连接字符串添加密码

谢谢

Answer 1

要使用受密码保护的MS Access数据库2003/2007，请使用以下代码段

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");
    }
}

我的连接字符串url为

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

我在连接MS Access数据库时遇到了一个问题，堆栈跟踪如下

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)

为了解决这个问题，我删除了“DriverID=22；READONLY=true；” 在url字符串中，问题得到解决：） 我已经在受密码保护的MS Access 2003和2007数据库上测试了给定的代码片段，并且运行良好

希望对做新的实验有所帮助

Answer 2

从此处引用：Java Support

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

Answer 3

我知道您要求使用ODBC，但是否无法使用OLEDB，如the connect string provided on ConnectionStrings.com：

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

我不知道Jet ODBC驱动程序是否提供了对数据库密码的任何支持，直到Jet 4才引入数据库密码（在任何版本的Access/Jet/ACE中都毫无价值）