Below code is to update a value of one row in MS-ACCESS db.
If i use the same code(sqldb) to update sql table , keeping break point on '.commit' then no other connection(user) can read or update the same row (a = 1), but can update other rows of same table
But for ms-access, keeping break point on '.commit', the entire table is locked for update(err:"could not update; currently locked") !?
Is there a way to lock single row using "oledb" ?
Dim sConn As OleDbConnection
sConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Pc99\d\db\Accounts.mdb;")
Dim sTransaction As OleDbTransaction
Dim OleCmd As OleDbCommand
OleCmd = New OleDbCommand("update tableA set b = b + 1 where a = 1", sConn)
sTransaction = sConn.BeginTransaction()
OleCmd.Transaction = sTransaction
Catch ex As Exception
sTransaction.Rollback() End Try
NO! MS Access is not a multi user database, it says so right there in the documentation, therefore it has PAGE locking. Thats right it grabs a whole chunk of records at the end of the table and lock the lot because there should only be 1 user so it does not give a rats.
This problem is so old it probably predates some of the developers here on CP, live with it or move on the a proper database (SQL Server).
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)