run-time error '3197'. MS Access 2010 and SQL Server 2008R2

Question

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.

Solution

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).

Comments

[Fionnuala]

Have you compacted & repaired and decompiled the MS Access database? Are you using any "unusual" date formats in SQL Server? You say other Access databases are okay, are they okay linked to the same tables that are causing a problem? Do you have a problem with a fresh database and linked tables?

[mikimr]

The new database has the same structure as the other databases. The only fields I changed were a couple of varchars that I made larger to fit all the text. What do you mean by "unusual date formats"? The same date formats are being used by the other databases with no problem. The error message happens (from what I tested so far) when I check a box in Access, which corresponds to a bit data type in SQL Server. At first, that bit field was NULL, but the error persists even after I populate this field with 0.

[Fionnuala]

Does this msgroups.net/microsoft.public.access.formscoding/… help?

[mikimr]

Thanks, I've changed all the bit datatypes to smallint and that worked.

[enderland]

Thanks, I was having a similar problem - I had bit fields which were Null (not 0) and this caused the same error for me.