I have an application written in MS Access 2007-2010 and a back end is an SQL database.
After building a new database, when I view the records from SQL in the Access report, trying to edit or enter new input results with a run-time error 3197.
The specific error says: This record has been changed by another user since you started editing it
Then I have Copy to Clipboard and Drop Changes options, while the Save Record is grayed out. Clicking on the Drop changes brings the error:
"Run-Time Error '3197' The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."
I then looked in the SQL and queried the database with EXEC sp_who2 'Active' and I can see that the user name I use for SQL is RUNNABLE while the user used by the Access application (coded in Access VBA) is SUSPENDED (the command it is suspended on is SELECT).
I have other databases that are constructed the same way and work with no issues. I've checked sp_who2 on the working database when it's running with Access, and the user that is SUSPENDED for the "problematic" database, is sleeping with this one.
I've checked the permissions for both databases, and checked the permissions for the users in Security--> Logins and don't see anything that I can flag as an issue (maybe someone else can?).
Not sure why this is happening and why the database is locked by the SQL user and won't let the Access user update the relevant records.
Can anyone shed some light on this issue?
Thanks.
As Remou commented, changing all bit data types to smallint and populating with 0 where Null did the trick. I didn't need to change 1 to -1 as my fields where to be in the initial state of unticked (=0).