Click here to Skip to main content
13,000,636 members (61,824 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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" ?

thank u.

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
            call OleCmd.ExecuteNonQuery()
	    sTransaction.Commit() 'commit transation
        Catch ex As Exception
            sTransaction.Rollback() 'rollback transaction
        End Try
Posted 14-Dec-12 3:00am
Amir Mahfoozi 16-Dec-12 2:18am
Maybe calling the BeginTransaction with specifying the transaction level solve your problem : so try to call it by this way : BeginTransaction(IsolationLevel.ReadUncommitted).
Good Luck.
srikrishnathanthri 17-Dec-12 0:01am
same problem with readUncommitted.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170624.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100