2

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.

mikimr
  • 311
  • 3
  • 8
  • 18
  • 1
    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? – Fionnuala Aug 01 '12 at 09:04
  • 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. – mikimr Aug 01 '12 at 12:28
  • 1
    Does this http://msgroups.net/microsoft.public.access.formscoding/datatype-bit-problem-with/76194 help? – Fionnuala Aug 01 '12 at 12:43
  • Thanks, I've changed all the bit datatypes to smallint and that worked. – mikimr Aug 02 '12 at 07:05

4 Answers4

4

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

mikimr
  • 311
  • 3
  • 8
  • 18
  • 1
    Thanks, I was having a similar problem - I had bit fields which were Null (not 0) and this caused the same error for me. – enderland Jan 28 '14 at 16:14
0

As suggested, I updated the data in the field that had the bit datatype from null to 0, also changed the datatype to int. That fixed the problem.

CarlMal
  • 1
0

I had the same issue,

    sql2 = "Select * from voeding where id = " & ID_Voeding_Site.Value & ""
    Set rst2 = bbase.OpenRecordset(sql2)
        rst2.Edit
        rst2.Fields("verwerkt").Value = 1
        rst2.Fields("printdatum").Value = Date
        rst2.Update
        rst2.Close

Was stuck on .update. I Changed it to:

    sql2 = "Select Verwerkt, Printdatum from voeding where id = " & ID_Voeding_Site.Value & ""
    Set rst2 = bbase.OpenRecordset(sql2)
        rst2.Edit
        rst2.Fields("verwerkt").Value = 1
        rst2.Fields("printdatum").Value = Date
        rst2.Update
        rst2.Close

No More troubles appeared. Hope this helps others.

Robin DMS
  • 1
0

I had the same problem. I solved it by adding a timestamp column to each table in the database. I found the solution in https://www.utteraccess.com/topics/1952450

Robert
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 30 '23 at 10:36