Writing to an Access DB locks DB
-
I'm writing an app that updates an Access database i am currently using Jet OLEDB connection to run my update querys. My app is parsing a large amount of data and takes about 10 - 15 mins to complete. I have noticed that If i try to access the DB from access while the app is running the Database is locked. Is there a way I can open the DB with out locking the DB for any other write access? thanks MAconn = new OleDbConnection(); MAconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="+DBPath; MAconn.Open(); MAcmd = MAconn.CreateCommand(); conn_succ = true;
-
I'm writing an app that updates an Access database i am currently using Jet OLEDB connection to run my update querys. My app is parsing a large amount of data and takes about 10 - 15 mins to complete. I have noticed that If i try to access the DB from access while the app is running the Database is locked. Is there a way I can open the DB with out locking the DB for any other write access? thanks MAconn = new OleDbConnection(); MAconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="+DBPath; MAconn.Open(); MAcmd = MAconn.CreateCommand(); conn_succ = true;
Nope. If you wanted this kind of functionality, you'd have to move to SQL Server or an equivilent. My personal choice would be SQL Server Express.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
Nope. If you wanted this kind of functionality, you'd have to move to SQL Server or an equivilent. My personal choice would be SQL Server Express.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
I'm writing an app that updates an Access database i am currently using Jet OLEDB connection to run my update querys. My app is parsing a large amount of data and takes about 10 - 15 mins to complete. I have noticed that If i try to access the DB from access while the app is running the Database is locked. Is there a way I can open the DB with out locking the DB for any other write access? thanks MAconn = new OleDbConnection(); MAconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="+DBPath; MAconn.Open(); MAcmd = MAconn.CreateCommand(); conn_succ = true;
Planker wrote:
Is there a way I can open the DB with out locking the DB for any other write access
It's been awhile since I last used Access, but when using ADO there was several parameters for the connection string you could specify which would affect how the Access db works. The records you update will be locked, but if you're not able to open the database using another connection, sounds like the db has been opened in exclusive mode. You could see the following if there's any help: ADO Provider Properties and Settings[^] However, if you're going to use the db seriously, I'd also suggest you move on to SQL Server.
The need to optimize rises from a bad design.My articles[^]
-
Planker wrote:
Is there a way I can open the DB with out locking the DB for any other write access
It's been awhile since I last used Access, but when using ADO there was several parameters for the connection string you could specify which would affect how the Access db works. The records you update will be locked, but if you're not able to open the database using another connection, sounds like the db has been opened in exclusive mode. You could see the following if there's any help: ADO Provider Properties and Settings[^] However, if you're going to use the db seriously, I'd also suggest you move on to SQL Server.
The need to optimize rises from a bad design.My articles[^]
It's not so much accessing the database from another instance of the app as the problem is Access opens the database DenyShareWrite. His explanation was that he couldn't open the database in Access and still have database writes be made by his application.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
It's not so much accessing the database from another instance of the app as the problem is Access opens the database DenyShareWrite. His explanation was that he couldn't open the database in Access and still have database writes be made by his application.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008Dave Kreskowiak wrote:
explanation was that he couldn't open the database in Access and still have database writes be made by his application.
I understood that and I got the feeling that the first connection to the db (his application) opens it in exclusive mode for some reason. Perhaps I'm still missing something, but I made a test with a fresh db with only 1 table (Table1) where I tried to simulate the problem:
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\...\db1.mdb";
connection.Open();
System.Data.OleDb.OleDbCommand command = connection.CreateCommand();
System.Data.OleDb.OleDbTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "INSERT INTO Table1 (Field1) VALUES ('A')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Table1 (Field1) VALUES ('B')";
command.ExecuteNonQuery();
// Breakpoint here
transaction.Commit();Before the commit I stopped the debugger. While still in debug mode I opened the mdb file from Access 2003 UI and then opened Table1. Didn't see the rows A and B as expected but all the previous rows where present. When I went through the commit and then re-opened Table1 rows A and B were present. So all the time I was able to for example query the table. Also I was able to modify data in another table while transaction from C# was in action. Didn't try to update the same table since I suspect that when using defaults, page level locking is used.
The need to optimize rises from a bad design.My articles[^]