MS Access Prevent Database Locking

Question

When I query my MS Access database, I notice that it's locked and other applications cannot access it. Here is my code:

    public void PopulateDataTableSettings()
    {
        if (_dt.Rows.Count == 0)
        {
            string query = "select * from settings";
            using (OleDbConnection conn = new OleDbConnection(_connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = query;
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(_dt);
                }
                conn.Close();
            }
        }
    }

Here is my connection string:

        OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();



        builder.DataSource = "C:\\shop.mdb";
        builder.Provider = "Microsoft.Jet.OLEDB.4.0";
        builder.OleDbServices = -1;
        return builder.ConnectionString;

Other than upgrading the database to SQL Server, can I specify a connection string property to never lock the database? Is there a property on my data adapter I could use?

Saving database files to "C:\" can cause problems. Try copying (not moving) the database file from "C:\" to a location where users have "normal" read/write access, e.g., "C:\Users\Public\", and see if the issue persists when working with the "Public" copy. — Gord Thompson, Jan 29 '14 at 08:43
@GordThompson Your comment is incorrect a bit. %appdata% is also in C:\ with Full Read/Write permission. — Anirudha Gupta, Apr 27 '16 at 06:12

score 1 · Accepted Answer · edited May 23 '17 at 11:59

It's difficult to tell from your question, but I assume you are accessing this from other MS Office applications? If so, there is a mode under the connection properties called "Share Deny None" - by using this mode, I was able to avoid most (but not necessarily all) of the read/write issues. Access is very fussy though, and it really only works when one person accesses it at one time .

More about Share Deny None: http://msdn.microsoft.com/en-us/library/office/aa140022(v=office.10).aspx Another post with similar problem: Connecting MS Access while another application using the same MS Acess File

MS Access Prevent Database Locking

1 Answers1