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

ms-access-2010 user-permissions sql-server-2008r2-express
mikimr picture mikimr · Aug 1, 2012 · Viewed 10.5k times · Source

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.

Answer

mikimr picture mikimr · Aug 2, 2012

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

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
Corrupted Access .accdb file: "Unrecognized Database Format"

I'm having a problem. One of my databases on our shared network will not open for any user. It says "Unrecognized Database Format." I've had this problem before but was still able to open the database, compact and repair, or …

Read More
Access 2010 VBA query a table and iterate through results

I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is: var q = "select * from table where some condition"; var results = db.getResults(q); …

Read More