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