Click here to Skip to main content
15,903,839 members
Please Sign up or sign in to vote.
5.00/5 (1 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.



VB
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 
        Try
	    sConn.Open()
            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
Comments
Amir Mahfoozi 16-Dec-12 2:18am    
Hi,
Maybe calling the BeginTransaction with specifying the transaction level solve your problem : so try to call it by this way : BeginTransaction(IsolationLevel.ReadUncommitted).
http://msdn.microsoft.com/en-us/library/aa325885%28v=vs.71%29.aspx
Good Luck.
srikrishnathanthri 17-Dec-12 0:01am    
same problem with readUncommitted.
thnaks.

1 solution

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).
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900