none
ADO .NET Exclusive connection to MDB file is broken after last Office Update RRS feed

  • Question


  • Hi every one,

    We have a mature Software build with C# + Windows Forms that uses ADO .NET to connect an Access MDB file. It has been working well for years.
    The problem is that after de last Office Updates we are getting the following Exception.
     0x80004005 - Couldn't use ''; file already in use
    After some research we detect that almost all the connections still work well but when we try to connect to de MDB file in Share Exclusive mode. Of course, the file is not in use and we have all the grants because we are testing on local Desktop.
    The MDB files has a password that sometimes is changed. Thus, we need to set at the Connection String with the following configuration: 'Mode=Share Exclusive;'
    If the software tries to open de file without Share Exclusive mode, goes well.
    We have found a Workaround. If we reinstall de Data Base Engine the problem goes out but as soon as Office is updated the problem comes again.
    We have to reinstall the Database Engine again for Fixing and it has to be done each week if you have the automatic Office update active.
    I'm sure this is an Access Driver Error because we have no changed this code for years and we are experimenting these same problem on tens of independetns enviroments.
    Does anyone know, how to permanent fix it without disabling Office Updates?
    Has anyone any idea if this is going to be fixed?
    Tips for reproduce the error:

    .NET Framework 4.0 x86 / x64 target compile
    Office 2016 or Office 365 x86 / x64
    C# Code:
     
                string strConnection = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\"{0}\"; Jet OLEDB:Database Password={1}; Mode=Share Exclusive", new object[] { file, oldPass });
                string sql = null;
                oldPass = string.IsNullOrEmpty(oldPass) ? "NULL" : oldPass;
                newPass = string.IsNullOrEmpty(newPass) ? "NULL" : newPass;
                sql = String.Format("ALTER DATABASE PASSWORD {0} {1}", new object[] { newPass, oldPass });
                OleDbConnection conexion = null;
                OleDbCommand cmd = null;
                try
                {
                    conexion = new OleDbConnection(strConnection);
                    conexion.Open(); //Produces the Exception: 0x80004005 - Couldn't use ''; file already in use
                    cmd = new OleDbCommand(sql, conexion);
                    cmd.ExecuteNonQuery();
                    ok = true;
                }
                catch (Exception ex)
                {
                    ok = false;
                }
                finally
                {
                    if (conexion != null && conexion.State == ConnectionState.Open)
                        conexion.Close(); //Respetar, revisado
                }
    
                return ok;



    Other tips:

    We have the same software compiled for 32 and 64 bits.
    If the Office is 32 bits, it breaks the 32-bit Data Base Engine not the 64 one, and the opposite happens if we update Office 64 bits, it breaks the 64-bit Data Base but not the 32 one.

    Any clue on this is welcome
    Many thanx  in advance
    Monday, March 9, 2020 8:42 AM

Answers

  • Hello,

    A month or so ago there was a lot of developers having a similar issue with .accdb where Microsoft had a patch to fix the issue, during this time I did see anyone using .mdb.

    What I suggest you do is to post a question in the "Access for developers" forum about this problem as my thought is there is no C# solution to the problem as it;s environmental. If no luck there you might consider moving up to .accdb as the problem from several months ago has been resolved. If this is an option you will need to change the security as staying with how security was done with .mdb has changed slightly. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Iñigo Panera Tuesday, March 10, 2020 8:35 AM
    Monday, March 9, 2020 9:46 AM
    Moderator

All replies

  • Hello,

    A month or so ago there was a lot of developers having a similar issue with .accdb where Microsoft had a patch to fix the issue, during this time I did see anyone using .mdb.

    What I suggest you do is to post a question in the "Access for developers" forum about this problem as my thought is there is no C# solution to the problem as it;s environmental. If no luck there you might consider moving up to .accdb as the problem from several months ago has been resolved. If this is an option you will need to change the security as staying with how security was done with .mdb has changed slightly. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Iñigo Panera Tuesday, March 10, 2020 8:35 AM
    Monday, March 9, 2020 9:46 AM
    Moderator
  • Hi Iñigo Panera,
    As offical document said, if a user opens a database and doesn't have create rights for the folder in which the database is located, the Microsoft Jet database engine can't create the locking information file (*.ldb in Access 2003 and in earlier versions of Access and *.laccdb in Access 2007/2010) that is necessary for multiple users to open the database. 
    Therefore, a user who doesn't have create rights opens the database exclusively if a corresponding .ldb file or .laccdb file does not already exist.
    So you should make sure that all users who open the database have read, write, and create rights for the folder in which the database is located.
    And access is set to open in shared mode on all of the users' computers.
    Here are some related documents you can refer to.
    [You may receive a "file already in use" error message when you try to open an Access database that is located on a server]
    [Access “couldn't use … file already in use”]
    Hope these are helpful for you
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 5:25 AM
  • Hello Karen,

    many thnax for the advise, I really apreciate it.
    I have open a new question on the "Access for developers" thread.

    Here is the link:
    https://social.msdn.microsoft.com/Forums/es-ES/f6bc655f-f04f-407c-be79-a57714c2cff4/ado-net-exclusive-connection-to-mdb-file-is-broken-after-last-office-update?forum=accessdev

    I hope is now the right place to ask.
    Tuesday, March 10, 2020 8:35 AM
  • Hi Daniel,

    I'm afread that these is not the problem because I have done test even on localhost with new files on my own Desktop were I have all the privileges and no one could stay conected.

    Still thank you very much for the advice.
    Tuesday, March 10, 2020 8:46 AM