Strange behaviour with Local Accessfile as DB with relativ path

If you are using an Access-File as DB.
And specify it as relative path in your app.config file like this

<add name="MSAccess" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=..\..\AccessFile\LocalAccess.mdb;
User Id=admin;Password=;" providerName="System.Data.OleDb" />

If you are using OpenFileDialog or SaveFileDialog

openFileDialog1.DefaultExt = "pdf";
openFileDialog1.Filter = "Technical paper (*.pdf)|*.pdf";
openFileDialog1.FileName = "";
openFileDialog1.InitialDirectory = lastOpenedPath;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
   // do something
}

Bad Example - No RestoreDirectory, your access-file will not be found after FileDialog...

openFileDialog1.DefaultExt = "pdf";
openFileDialog1.Filter = "Technical paper (*.pdf)|*.pdf";
openFileDialog1.FileName = "";
//HACK PGfader: necessary so that the relative access file is found after this!
openFileDialog1.RestoreDirectory = true;
openFileDialog1.InitialDirectory = lastOpenedPath;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
    // do something
}

Good Example - RestoreDirectory used, local relative access file after ShowDialog


RestoreDirectory = false is default necessary to set on OpenFileDialog AND SaveFileDialog

Exception: Operation must be an updatable query when you use an local Access File (.mdb)

SOLUTION
The Access File .mdb is readonly, from sourcecontrol

Change it on startup with the following Helper: RemoveReadOnlyFlagAccessFile()


Code Snippet to remove ReadOnly

public static string RemoveReadOnlyFlagAccessFile()
        {
            string returnValue = "";

            string connString = GetAccessConnectionString();
            if (string.IsNullOrEmpty(connString))
            {
                returnValue = "ConnectionString not found";
            }
            else
            {
                //FORMAT
                //connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\AccessFile\oracle-reverse-engineered-modified-manually.mdb;User Id=admin;Password=;"
                if (connString.ToUpper().Contains("OLEDB") == false)
                {
                    returnValue = "ConnectionString is not for MsAccess. doesnt contain OLEDB. ConnString: " + connString;
                }
                else
                {
                    int indexStart = connString.IndexOf("Data Source=");
                    if (indexStart <= 0)
                    {
                        returnValue = "ConnectionString is not correct. doesnt contain Data Source=. ConnString: " + connString;
                    }
                    else
                    {

                        string tempString = connString.Substring(indexStart + 12);
                        int indexEnd = tempString.IndexOf(".mdb");
                        if (indexEnd <= 0)
                        {
                            returnValue = "ConnectionString is not correct. doesnt contain .mdb. TempConnString" + tempString;
                        }
                        else
                        {
                            string filePath = tempString.Substring(0, indexEnd + 4);
                            string fullPath = Path.GetFullPath(filePath);

                            if (File.Exists(fullPath) == false)
                            {
                                returnValue = "File doesnt exist: " + fullPath;
                            }
                            else
                            {
                                returnValue = "File " + fullPath + " is: " + File.GetAttributes(fullPath).ToString() + "\n";

                                try
                                {
                                    File.SetAttributes(fullPath, FileAttributes.Normal);
                                    returnValue += "File " + fullPath + " NOW is: " + File.GetAttributes(fullPath).ToString() + "\n";
                                }
                                // TODO catch explicit exception
                                catch (Exception ex)
                                {
                                    Logger.Write("EXCEPTION in File.SetAttributes:   " + ex.ToString());
                                    returnValue += "Exception on File.SetAttributes";
                                }
                                
                            }


                        }
                    }
                }
            }

            //Logger.Write("FileUtils.RemoveReadOnlyFlagAccessFile  returnValue: \n" + returnValue);
            return returnValue;

        }


        private static string GetAccessConnectionString()
        {
            

            if (System.Configuration.ConfigurationManager.ConnectionStrings.Count == 0)
            {
                throw new ApplicationException("Set ConnectionString in .config file!");
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings.Count == 1)
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString;
            }
            else
            {
                // try "MasterMSAccess", then empty, then firstone
                if (System.Configuration.ConfigurationManager.ConnectionStrings["MasterMSAccess"] != null)
                {
                    return System.Configuration.ConfigurationManager.ConnectionStrings["MasterMSAccess"].ConnectionString;
                }
                else if (System.Configuration.ConfigurationManager.ConnectionStrings[""] != null)
                {
                    return System.Configuration.ConfigurationManager.ConnectionStrings[""].ConnectionString;
                }
                else
                {
                    return System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString;
                }

            }
        }

Transactions with MSAccess mdb File

If you use Distributed Transactions within your Application and an Access .mdb a DataContainer then you maybe did get this:

System.InvalidOperationException: The ITransactionLocal interface is not supported by the 'Microsoft.Jet.OLEDB.4.0' provider.  Local transactions are unavailable with the current provider.

SOLUTION:
Don't let Access participate in this Distributed Transaction
System.Transactions.TransactionScopeOption.Suppress


Msdn Link: Supporting Transactions in OLE DB

Access 2007 - Whats good and bad

very interesting info.... sorry forgot the link.... http://allenbrowne.com/access2007.html