none
Ace OLEDB.12.0 Excel <-> (password protected) Access trouble RRS feed

  • Question

  • I have a huge problem with following. Using Access 2010 (*.accdb) and Excel 2010. 32-bit. Connecting from Excel to Access and everything works great but when I set password to Access file then I cannot get the connection to work. Been searching the internet and found out that I'm not the only one with this...

    Here's a sample code which doesn't work:

    Public Sub TestSub()

        Dim strConnection As String
        Dim cnn As ADODB.Connection

       strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\test.accdb;"

        Set cnn = New ADODB.Connection
        cnn.ConnectionString = strConnection
        cnn.Properties("Jet OLEDB:Database Password") = "test"
        cnn.Open

        Debug.Print cnn.State = adStateOpen

        cnn.Close

    End Sub

    Everything works if the Access file is not password protected but when it is, there's always "not a valid password" error. I'm 100% sure that password is correct. I know also that the code works with Windows XP (Access 2007, Excel 2007) computer but not when Windows 7, Access 2010, Excel 2010 are used. Is this a bug or what's wrong? And what options do I have because password is needed for this file?

    Monday, April 30, 2012 5:18 PM

All replies

  • This looks like Classic ADO and not ADO.NET, but I didn't have any problem when including the database password in the connection string:

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...\Documents\My Database\Access\NorthwindPassword.accdb;Jet OLEDB:Database Password='password';Mode=Share Exclusive"
    BTW, the root in Windows 7 is protected by UAC so you will very likely have problems opening the database at that location.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Tuesday, May 1, 2012 12:05 PM
  • Thanks for the reply!

    Moved the file out of the root and tried also including password in the connection string but it just won't work. Always Run time error '-2147217843 (80040e4d) Not a valid password.

    Happens also if I just create a new database, set a password to it and try to connect. I guess has to reinstall Office or something, maybe it would help...

    Wednesday, May 2, 2012 5:04 AM
  • Have you tried creating a new empty database, assigning it a database password and then opening it with the same code?

    Also, I assume that this database does not implement user-level security (user names and passwords) and that it just uses a database password?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 2, 2012 11:53 AM
  • Yes, I have tried creating a new empty database and assign it with a password but it won't help.

    In Access 2007 and 2010 it is only possible to create database password so user names and passwords are no longer supported.

    After uninstalling Office 2010, I installed Office 2007. Both codes mentioned here worked without problems. Then I uninstalled 2007 and reinstalled Office 2010. Codes for database created in Access 2007 work still fine but if I create a database in Access 2010, assign it with a password and try these codes to connect to it, it gives the error "not a valid password".

    I think/hope I can go around this by using database created in Access 2007 but this is really weird. Haven't yet tested using someone else's computer to create password protected database in Access 2010 to see if this is about my computer.

    Wednesday, May 2, 2012 5:05 PM
  • Hi jvirtane,

    Have you solved the issue? I look forward to hearing from you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 7, 2012 7:07 AM
    Moderator
  • Hi Allen,

    it is actually still unsolved. I had to move forward with my project and haven't come back to this yet.

    Last thing I tried was to change database file security settings like record-level locking but the problem remained. Haven't yet tried creating password protected Access 2010 file with an other computer and using macros to connect to it to find out if there's something wrong in my computer.

    I'll report that later when I've tested it but if anyone has any ideas about this, please feel free to share them. :)

    Br,

    jvirtane

    Wednesday, May 9, 2012 8:08 AM
  • Hi Jvirtane:

    I have the very same problem. I have tried on several computers and the issue is the same: If you protect/encrypt a .accdb file with Access 2010 you won't be able to open it from Excel. Even if you try to open the accdb database from Excel without using code (just try to import data from Access selecting the accdb file) then a dialog box will pop promting you to input de DB password, but even if you type it right, it won't open and will loop forever.

    I'm been trying to solve this for more than 24 hours, and the only workaround I've found is to encrypt the accdb file using Acess 2007. Then you can decrypt it from Excel using code or directly like a charm. Guess it's a bug.

    Anyone got some ideas?

    Allen: Do you have any suggestions/info?

    Thanks a million!!

    Rod.Roy

    Saturday, June 2, 2012 6:34 AM
  • I have the same problem with office 2010 32 bit (and windows 7)

    New access database accdb, password protected and if I try to open it with excel it gives the error "not a valid password".

    Please post here the solution if someone find it. thanks

    Tuesday, June 5, 2012 1:50 PM
  • Here's a link to another thread that's pretty much saying it is impossible to do this using ADO and Access 2010.

    http://www.access-programmers.co.uk/forums/showthread.php?t=220521

    Even though Excel can't do it, InfoPath can. So I went into the XSF file and took out the connection string. I was able to connect to an encrypted Access 2010 accdb database with the following connection string. Let me know if any of you still have issues with this.

    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=<path>;Jet OLEDB:Database Password=<password>;

    Tuesday, June 5, 2012 5:42 PM
  • I don't suppose this will help but I had a similar problem and mine was caused by Access default opening in Exclusive Mode. When I changed this under Options / Client Settings the issue resolved.

    Hope you found a solution.

    Angie

    Tuesday, June 26, 2012 10:34 AM
  • Has anyone found a solution to this problem?  I am having the exact same issue.
    • Proposed as answer by PeteSilverling Tuesday, October 30, 2012 1:18 AM
    • Unproposed as answer by PeteSilverling Tuesday, October 30, 2012 1:19 AM
    Thursday, October 18, 2012 1:53 PM
  • 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

    • Proposed as answer by PeteSilverling Tuesday, October 30, 2012 1:26 AM
    Tuesday, October 30, 2012 1:26 AM
  • I have access database made in access 2010. when i am running it to office 2007 without password it's running well after password protection it's not opened in office 2007 and showing message "cannot open database. it may not be a database that your application recognizes  or the file may be corrupt".
    Tuesday, October 30, 2012 5:57 AM
  • Hi Arun:

    The problem the way I see it is that if you encrypt an accdb file with Access 2010 then Access 2007 won't recognize the encryption format. As a matter of fact, Excel 2010 won't open an encypted accdb file from Access 2010. (Boogagiga posted a workaround but i haven't tried it yet).

    I suggest you to encrypt the file using Access 2007. Then you won't have any problems opening it from 2010 or 2007.

    Regards.

    Friday, February 8, 2013 10:17 AM
  • Hi,

    I have had the same issue as everyone here and spent lots of time trying to get excel 2010 connect to access 2010 ACCDB database from VBA.

    I followed

    PeteSilverling suggestion and still ran into errors. But ultimately I did get it to work. Peter was correct but to add to his explination, you have to decrypt the database, make the changes Peter suggested then encrypt. After that the following code worked without problem;

        With DBconnection
           
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Properties("jet OLEDB:Database Password") = "clearwipe"
            .Open DBaddress
           
        End With

    hope this helps others.

    Regards

    Anthony


    Tuesday, August 20, 2013 4:09 PM
  • Funcionou perfeitamente para mim que uso a seguinte string de conexão:

    Set BD = OpenDatabase("C:\test.accdb", False, False, ";PWD=123")

    > Abrir normalmente o arquivo do banco de dados e fechar o que for aberto para permanecer com o Access aberto;
    > Clique em abrir, meu computador e procure o arquivo .accdb desejado
    > nesta tela, clique no combo ABRIR - modo exclusivo
    > arquivos - opções - configurações do cliente (vá até o fim)
    >>> Modo de abertura padrão: COMPARTILHADO
    >>> Proteção de registros padrão: Sem proteção
    >>> Método de Criptografia: Usar criptografia herdada ( ...
    > Feito isso, é só descriptografar e re-criptografar novamente.
    > Boa Sorte!!!

    Saturday, April 26, 2014 7:02 PM
  • Valmor que versão que você esta utilizando? Estou tentando fazer o mesmo procedimento no Access 2013 e não está funcionando.

    Abraços


    Baldini Fabio

    Friday, September 11, 2015 4:09 AM
  • Hi, I have done excel macros in wxp w7 32b w7 64b, when reading an excel file with

       routeexcel = "C:\book.xls"
       If VBA.Val(Application.Version) < 12 Then
          ExcelStr = "Provider=Microsoft.Jet.OLEDB 4.0;" & _
          "Data Source = '" & routeexcel & "''':" & _
          "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"  ...  for excel 2000-2003 wxp
          
       ElseIf VBA.Val(Application.Version) < 14 Then
          ExcelStr = "Provider=Microsoft.Jet.OLEDB 12.0;" & _
          "Data Source = '" & routeexcel & "''':" & _
          "Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";" ...  for excel 2007 w7 32b
       
       ElseIf VBA.Val(Application.Version) = 14 Then
          ExcelStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source = '" & routeexcel & "';" & _
          "Extended Properties=""Excel 12.0;HDR=YES;\"";"  ...  for excel 2007 w7 64b
       ElseIf VBA.Val(Application.Version) = 16 Then
          ExcelStr = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
          "Data Source = '" & routeexcel & "';" & _
          "Extended Properties=""Excel 10.0;HDR=YES;\"";" ...  for excel 2010 w10 
    Not find file ISAM
     
      now that I migrate the macro to w10 I will not have it, you will have some idea, thanks
    Friday, October 13, 2017 6:38 PM
  • Thanks Pete, that worked for me after trying so many other options found over the web.

    Friday, January 5, 2018 1:41 PM
  • Si funciona este metdo, no saben cuanto batalle para corregir este pequeño error, Gracias Peter...!!!
    Thursday, May 3, 2018 7:24 PM