Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MS-Access OleDb
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 
        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 14-Dec-12 4:00am
Comments
Amir Mahfoozi at 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 at 17-Dec-12 0:01am
   
same problem with readUncommitted.
thnaks.

1 solution

Rate this: bad
good
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).
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 335
1 OriginalGriff 250
2 DamithSL 170
3 Kornfeld Eliyahu Peter 130
4 Peter Leow 95
0 OriginalGriff 7,315
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,917
3 Maciej Los 4,866
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2014
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