I have a VB app that hits MS SQL Server data via DAO-JetWorkSpace-ODBC. I encountered the following error on Update statement:
Run-time error '3197':
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
The code is the following:
Dim engTest As DBEngine
Dim wksTest As Workspace
Dim dbsTest As DAO.Database
Dim rstTest As DAO.Recordset
Set engTest = New DBEngine
Set wksTest = DBEngine.CreateWorkspace("JetWorkSpace", "admin", "", dbUseJet)
Set dbsTest = wksTest.OpenDatabase(vbNullString, 0, 0, "ODBC;DSN=SQLTEST;"
Set rstTest = dbsTest.OpenRecordset("Select * from OrdPart", dbOpenDynaset)
rstTest.MoveFirst
rstTest.Edit
rstTest![Inactive] = -1
rstTest.Update
Set rstTest = Nothing
Set dbsTest = Nothing
Set wksTest = Nothing
Set engTest = Nothing
Envrionment:
Server:
Windows 2000 Advanced Server
Microsoft SQL Server 7 SP3
Client/Development Workstation:
Visual Basic 6 SP4
DAO 3.6
Microsoft Jet 4.0 SP6
Window 98 Second edition
The error only ocuur to one talbe. All the rest of tables are ok to update. I am positive that there is no concurrent user when error occured. In a hurry to finish this proejct, I simply put a in-line error handling to trap the error and issue another update statement. It works for almost two years. Couple weeks ago, this workaround was no longer working.
I first use DBCC CheckDB and DBCC CheckTable to make sure that corruption is not an issue.
I use sp_who and sp_lock to make sure that no orphaned session, orther processes, or dead lock is holding the table. I also reboot the SQL server. Nothing works.
If I modify the OpenRecordset method as the following in an effort to block other users from issuing locks:
OpenRecordset("Select * from OrdPart", dbOpenDynaset, dbDenyWrite, dbPessimistic)
This time, I got a "ODBC failed - can not lock all reocrds".
It seems that an invisible process is holding the lock of the table
Thanks for any advice
Run-time error '3197':
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
The code is the following:
Dim engTest As DBEngine
Dim wksTest As Workspace
Dim dbsTest As DAO.Database
Dim rstTest As DAO.Recordset
Set engTest = New DBEngine
Set wksTest = DBEngine.CreateWorkspace("JetWorkSpace", "admin", "", dbUseJet)
Set dbsTest = wksTest.OpenDatabase(vbNullString, 0, 0, "ODBC;DSN=SQLTEST;"
Set rstTest = dbsTest.OpenRecordset("Select * from OrdPart", dbOpenDynaset)
rstTest.MoveFirst
rstTest.Edit
rstTest![Inactive] = -1
rstTest.Update
Set rstTest = Nothing
Set dbsTest = Nothing
Set wksTest = Nothing
Set engTest = Nothing
Envrionment:
Server:
Windows 2000 Advanced Server
Microsoft SQL Server 7 SP3
Client/Development Workstation:
Visual Basic 6 SP4
DAO 3.6
Microsoft Jet 4.0 SP6
Window 98 Second edition
The error only ocuur to one talbe. All the rest of tables are ok to update. I am positive that there is no concurrent user when error occured. In a hurry to finish this proejct, I simply put a in-line error handling to trap the error and issue another update statement. It works for almost two years. Couple weeks ago, this workaround was no longer working.
I first use DBCC CheckDB and DBCC CheckTable to make sure that corruption is not an issue.
I use sp_who and sp_lock to make sure that no orphaned session, orther processes, or dead lock is holding the table. I also reboot the SQL server. Nothing works.
If I modify the OpenRecordset method as the following in an effort to block other users from issuing locks:
OpenRecordset("Select * from OrdPart", dbOpenDynaset, dbDenyWrite, dbPessimistic)
This time, I got a "ODBC failed - can not lock all reocrds".
It seems that an invisible process is holding the lock of the table
Thanks for any advice
