Click here to Skip to main content
12,451,093 members (44,037 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL 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 3:00am
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

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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2016
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