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)
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)
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
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
