Search code examples
databasems-accessodbc

getting error 3197 when deleting from MsAcces table linked to CACHE

I have a MsAccess db (Microsoft® Access® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20186) 64-bit ) that is linked with CACHE via an ODBC-driver. It is installed on my harddrive, so no other users can be interrupting anything.

I have to delete records in a table. I am doing this via a filtered recordset.

It worked fine, but now I get an error: "The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."

  • relinked the table

  • refreshed the table

  • reconfigured the ODBC-driver

  • repaired and compacted the database

  • imported all the tables into a new database

  • in an older version same of the db (that worked) I now have the same issue

  • delete query works

  • delete via a recordset doesn't work

  • delete manually, selecting the record and pushing the delete button doesn't work

Any ideas, I'm out of options ... .

Solution

  • not quite sure about your specific problem here. Especially since you´re not able to delete it manually and a bit missing info, like

    • is older version of the same db also run via ODBC
    • is the aforementioned error occuring in both failed cases.

    But I solved the multiple users access same data problem in most cases by checking my code with regard to the following:

    • open recordsets that are not closed before the delete command. 
      Set rst = dbs.OpenRecordset(stringSQL)
...
rst.close
set rst = nothing
    • opened forms or subforms that need to be saved/closed before delete command
    • use the right type of recordset(RecordsetTypeEnum)

    surely that list is not complete, but that´s what I can think of atm.