Open password-protected Access 2010 database in VB

Question

I need to open a password-protected Access 2010 database from a VB2010 application. The connection works fine without a password, so I know I have all other parameters set correctly. But when I add the password to the connection string I get an error, "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." I know the password is correct because it works when I copy and paste it into the password dialog when opening the database directly in Access.

Here is the code I use. The error occurs at the adapter.fill command:

    Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + Application.StartupPath + "\MyData.accdb;Jet OLEDB:Database Password=MyPassword;")

    Dim command As OleDbCommand = New OleDbCommand()
    command.Connection = conn
    command.CommandText = "SELECT * FROM MyTable"

    Dim table As DataTable = New DataTable()

    Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(command)

    adapter.Fill(table)

Answer 1

Worked on this problem for a while. All the clues are mentioned above, but had to incorporate them all together...

Under File - Options - Client Settings (scroll to the bottom)...

Default Open Mode = Shared

Default record locking = No Locks

Encryption Method = Use legacy

Unencrypt and re-encrypt the database

    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .ConnectionString = "Data Source=c:\dt\GenericDetail.accdb;Jet OLEDB:Database Password='ThePassword';"
    .Open

Reference: https://social.msdn.microsoft.com/Forums/en-US/210e52e6-cae7-4312-a08a-20c3e50bc17d/ace-oledb120-excel-password-protected-access-trouble?forum=adodotnetdataproviders

Answer 2

I think it's a bug in Access 2010 : https://www.connectionstrings.com/access-2010/

Quote :

With database password This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; 

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

Note! Reports say that a database encrypted using Access 2010 - 2013 default encryption scheme does not work with this connection string. In Access; try options and choose 2007 encryption method instead. That should make it work. We do not know of any other solution. Please get in touch if other solutions is available!

Also, as a tip, you don't need to say

Dim conn As OleDbConnection = New OleDbConnection("Connection String")

You can just do

Dim conn As New OleDbConnection("Connection String)

Answer 3

From:https://stackoverflow.com/posts/22485495/edit

The method I used to do this is actually quite simple:

Set db = CurrentDb
Set dblink = DBEngine.OpenDatabase(strDbFile, False, False, ";PWD=" & strP)

For Each strTable In strLinkedTablesArray
     DoCmd.TransferDatabase acLink, "Microsoft Access", dblink.name, acTable, _
         strTable, strTable
Next

Hope that helps