EDN Admin
Well-known member
Hi ,
i have a strange problem happening with Ms Access 2007 DB .
In short , i have two instances of the same program using the same shared access DB . DB is on a network share . Users have full control to folder and access DB .
This is what happens :
1) Run test program on one client .
2) Run test program on second client .
wait .....
"Operation need and updatable query " error happens .
Im using VB .NET 4.0 .
Heres the code i use to replicate the error :
Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim CNN as string = " connection string using Access ACE 12 .0 provider or JETDB 4.0 "
Dim cla As New ProvaDB(CNN, "Colonna0")
While True
cla.Worker()
End While
End Sub
Private Class ProvaDB
Private m_cnn As OleDbConnection
Private m_column As String
Public Sub New(ByVal cnn As String, ByVal column As String)
m_cnn = New OleDbConnection(cnn)
m_column = column
End Sub
Public Sub Worker()
Dim sTransaction As OleDbTransaction = Nothing
Dim sel As OleDbCommand
Try
While True
sTransaction = Nothing
m_cnn.Open()
sTransaction = m_cnn.BeginTransaction(IsolationLevel.ReadCommitted)
Try
sel = New OleDbCommand("SELECT Colonna0 FROM Prova ", m_cnn)
Dim cmd As New OleDbCommand("UPDATE Prova SET " + m_column + " = 233 ", m_cnn)
cmd.Transaction = sTransaction
Dim result As Integer = 0
result = cmd.ExecuteNonQuery()
sTransaction.Commit()
Catch ex As Exception
sTransaction.Rollback()
End Try
m_cnn.Close()
sTransaction.Dispose()
End While
Catch ex As Exception
m_cnn.Close()
If (sTransaction IsNot Nothing) Then
sTransaction.Rollback()
sTransaction.Dispose()
End If
End Try
End Sub
End Class
All this to is enter a loop where i read and update the same record .
I would expect an exception like "Invalid Operation . Record already locked" at most , but i dont get it .
All i get is "Operation need an updatable query " .
After i get this exception , DB on net share can only be opened in Read mode . Checking open files handles on the server the share belong to , shows the Db opened by both cliens on Read mode . Trying to open it with access shows the ribbon message "The database has been opened on read only mode .... " . A Strange thing is that when this error happens , the LDB file does not exist . If i open the MDB with access LDB is created but in UNICODE , while it is normally created in ASCII . It seems like the JETDB is locking the MDB . The only way to recoder from this situation is to stop both client programs . This way MDB lock is released and the db could be opended again . But i have to exit from both programs . If i only exit from one the error repeats . Why is JET or ACe provider locking the database .
One more thing .... if i dont open the connection many times (putting m_cnn.open outside the loop ) all seems to work .
Any ideas ??
Thnks .
Lorenzo .
View the full article
i have a strange problem happening with Ms Access 2007 DB .
In short , i have two instances of the same program using the same shared access DB . DB is on a network share . Users have full control to folder and access DB .
This is what happens :
1) Run test program on one client .
2) Run test program on second client .
wait .....
"Operation need and updatable query " error happens .
Im using VB .NET 4.0 .
Heres the code i use to replicate the error :
Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim CNN as string = " connection string using Access ACE 12 .0 provider or JETDB 4.0 "
Dim cla As New ProvaDB(CNN, "Colonna0")
While True
cla.Worker()
End While
End Sub
Private Class ProvaDB
Private m_cnn As OleDbConnection
Private m_column As String
Public Sub New(ByVal cnn As String, ByVal column As String)
m_cnn = New OleDbConnection(cnn)
m_column = column
End Sub
Public Sub Worker()
Dim sTransaction As OleDbTransaction = Nothing
Dim sel As OleDbCommand
Try
While True
sTransaction = Nothing
m_cnn.Open()
sTransaction = m_cnn.BeginTransaction(IsolationLevel.ReadCommitted)
Try
sel = New OleDbCommand("SELECT Colonna0 FROM Prova ", m_cnn)
Dim cmd As New OleDbCommand("UPDATE Prova SET " + m_column + " = 233 ", m_cnn)
cmd.Transaction = sTransaction
Dim result As Integer = 0
result = cmd.ExecuteNonQuery()
sTransaction.Commit()
Catch ex As Exception
sTransaction.Rollback()
End Try
m_cnn.Close()
sTransaction.Dispose()
End While
Catch ex As Exception
m_cnn.Close()
If (sTransaction IsNot Nothing) Then
sTransaction.Rollback()
sTransaction.Dispose()
End If
End Try
End Sub
End Class
All this to is enter a loop where i read and update the same record .
I would expect an exception like "Invalid Operation . Record already locked" at most , but i dont get it .
All i get is "Operation need an updatable query " .
After i get this exception , DB on net share can only be opened in Read mode . Checking open files handles on the server the share belong to , shows the Db opened by both cliens on Read mode . Trying to open it with access shows the ribbon message "The database has been opened on read only mode .... " . A Strange thing is that when this error happens , the LDB file does not exist . If i open the MDB with access LDB is created but in UNICODE , while it is normally created in ASCII . It seems like the JETDB is locking the MDB . The only way to recoder from this situation is to stop both client programs . This way MDB lock is released and the db could be opended again . But i have to exit from both programs . If i only exit from one the error repeats . Why is JET or ACe provider locking the database .
One more thing .... if i dont open the connection many times (putting m_cnn.open outside the loop ) all seems to work .
Any ideas ??
Thnks .
Lorenzo .
View the full article