如何从C＃和SQL创建受用户名和密码保护的MS Access 2007文件？

I don't know much about OleDB and I need some information on how to create a MS Access 2007 file as password protected. This is a piece of code which use User Id=admin; Password= but it gives me an error while trying to save saying: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

我不太了解OleDB，我需要一些有关如何创建MS Access 2007文件作为密码保护的信息。这是一段使用User Id = admin的代码;密码=但它在尝试保存说法时给出了错误：无法启动您的应用程序。工作组信息文件丢失或由其他用户独占打开。

EDIT: Now I have error: Cannot open the MS Office Access database engine workgroup information file

编辑：现在我有错误：无法打开MS Office Access数据库引擎工作组信息文件

I have figured out that problems lay in the SQL command. What SQL command should I be using? This command creates a problem, and I can't figure out why. I have used similar syntax from the link that person provided in the comment.

我已经发现SQL命令存在问题。我应该使用什么SQL命令？这个命令会产生问题，我无法弄清楚原因。我使用了评论中提供的链接中的类似语法。

    try
    {
        objOleDbConnection.Open();
        objOleDbCommand.CommandText = 
            "ALTER USER " + storedAuth.UserName + 
            " PASSWORD [" + storedAuth.Password + "] []";
        objOleDbCommand.ExecuteNonQuery();
    }

I could use following code, but what about user name?

我可以使用以下代码，但用户名怎么样？

objOleDbCommand.CommandText = "ALTER DATABASE PASSWORD " + storedAuth.Password + "[]";

EDITTED changed the code what I have now:

EDITTED改变了我现在拥有的代码：

    private void sfdNewFile_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
    {
        // Creating a ADOX object needed to create
        // new MS Access file.
        ADOX.Catalog createMSFile = new ADOX.Catalog();

        createMSFile.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            sfdNewFile.FileName);

        Table nTable = new Table();
        nTable.Name = "PersonData";
        nTable.Columns.Append("DataID", DataTypeEnum.adInteger, 40);
        nTable.Columns.Append("Type", DataTypeEnum.adVarWChar, 40);
        nTable.Columns.Append("URL", DataTypeEnum.adVarWChar, 40);
        nTable.Columns.Append("SoftwareName", DataTypeEnum.adVarWChar, 40);
        nTable.Columns.Append("SerialCode", DataTypeEnum.adVarWChar, 40);
        nTable.Columns.Append("UserName", DataTypeEnum.adVarWChar, 40);
        nTable.Columns.Append("Password", DataTypeEnum.adVarWChar, 40);

        createMSFile.Tables.Append(nTable);

        // It is importnat to release COM object, in this very order
        // otherwise we eill end up with an error.
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(nTable);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(createMSFile.Tables);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(createMSFile.ActiveConnection);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(createMSFile);

        OleDbConnection objOleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + sfdNewFile.FileName);
        OleDbCommand objOleDbCommand = objOleDbConnection.CreateCommand();

        try
        {
            objOleDbConnection.Open();
            objOleDbCommand.CommandText = "ALTER DATABASE PASSWORD [" + storedAuth.Password + "] []";
            objOleDbCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            // Displaying any errors that 
            // might have occured.
            MessageBox.Show("Error opening the " +
            "connection: " + ex.Message);
        }
        finally
        {
            objOleDbConnection.Close();
        }

        MessageBox.Show("File have been created.");
    }

Hope for some tips. Regards.

希望有一些提示。问候。

2 个解决方案

#1

Open connection to DB in exclusive mode, described in Working with Database Passwords in VBA Code.

在独占模式下打开与DB的连接，如在VBA代码中使用数据库密码中所述。

OleDbConnection objOleDbConnection = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + sfdNewFile.FileName + ";Exclusive=1;");

this should work fine as well:

这应该也可以正常工作：

OleDbConnection objOleDbConnection = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;" + 
    "Data Source=" + sfdNewFile.FileName + ";Mode=12;");

Edit:

编辑：

The above is for "Cannot change password on a shared open database.".

以上是“无法更改共享打开的数据库上的密码”。

If you still have an Cannot open the MS Office Access database engine workgroup information file error try add Jet OLEDB:System database to connection string that points to System.MDW file (locate it using "search"). It might looks like:

如果仍然无法打开MS Office Access数据库引擎工作组信息文件错误，请尝试将Jet OLEDB：System数据库添加到指向System.MDW文件的连接字符串（使用“搜索”找到它）。它可能看起来像：

OleDbConnection objOleDbConnection = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0" +
    ";Data Source=" + sfdNewFile.FileName + 
    ";Jet OLEDB:System database=C:\...\System.MDW"
    ";Exclusive=1;");

#2

I don't think you can change a user name directly. Consider than a SQL UPDATE is analogous to a DELETE combined with an INSERT. Likewise, combine CREATE and DROP e.g.

我认为您不能直接更改用户名。考虑一下，SQL UPDATE类似于DELETE与INSERT的结合。同样，组合CREATE和DROP，例如

instead of (pesudocode)

而不是（pesudocode）

ALTER USER HelpNeeder SET uid = onedaywhen; -- no such syntax

try (actual code):

尝试（实际代码）：

CREATE USER onedaywhen pwd H3sJaZ9k2m;
DROP USER HelpNeeder;

Then GRANT the new user the same privileges as the old ;)

然后授予新用户与旧用户相同的权限;）

p.s. I don't think the user name and password values can be parameterized.

附：我不认为可以参数化用户名和密码值。

#1