I am using Access (Office 365, version 16.0), connecting to linked tables in SQL Server (ODBC DSN Type = SQL Server Native Client 11.0).
I basically have 3 tables, each using it's own bound form: ApplicantData, EnrolleeData, and PostSeparation data. The data source is simply Select * from
.
Applicant data form works just fine. I can add new records, and then go back into records and update existing records.
In EnrolleeData and PostSeparationData, I am able to add a new record, however, when I attempt to update an existing record, I get an error message ("Run-time error 3197 Microsoft Access Database Engine Stopped the process because you and another user are attempting to change the same data at the same time"). This error occurs on simple VBA code (DoCmd.RunCommand acCmdSaveRecord) on click event of "Save" button.
It's especially bizarre that this works fine on ApplicantData data, but not in the other two tables. I've tried different ODBC drivers, checked / tried different values for various properties settings in form (record locks, etc), and I've even tried removing the foreign key constraints in SQL and re-linking the tables ... this is the only difference between EnrolleeData and PostSeparationData (vs. ApplicantData) - both of these tables have a foreign key constraint to ApplicantData in SQL Server (but removing those constraints still didn't fix the issue).
I'm using bound forms, some vba to handle behavior of controls (e.g. if this value here, enable that control), but not doing anything related to recordsets in the code (other than DoCmd.RunCommand acCmdSaveRecord)
Can anyone please help me? Any ideas on what could be causing this, or what else to try? Is there any way in VBA code to remove a record lock?
I basically have 3 tables, each using it's own bound form: ApplicantData, EnrolleeData, and PostSeparation data. The data source is simply Select * from
Applicant data form works just fine. I can add new records, and then go back into records and update existing records.
In EnrolleeData and PostSeparationData, I am able to add a new record, however, when I attempt to update an existing record, I get an error message ("Run-time error 3197 Microsoft Access Database Engine Stopped the process because you and another user are attempting to change the same data at the same time"). This error occurs on simple VBA code (DoCmd.RunCommand acCmdSaveRecord) on click event of "Save" button.
It's especially bizarre that this works fine on ApplicantData data, but not in the other two tables. I've tried different ODBC drivers, checked / tried different values for various properties settings in form (record locks, etc), and I've even tried removing the foreign key constraints in SQL and re-linking the tables ... this is the only difference between EnrolleeData and PostSeparationData (vs. ApplicantData) - both of these tables have a foreign key constraint to ApplicantData in SQL Server (but removing those constraints still didn't fix the issue).
I'm using bound forms, some vba to handle behavior of controls (e.g. if this value here, enable that control), but not doing anything related to recordsets in the code (other than DoCmd.RunCommand acCmdSaveRecord)
Can anyone please help me? Any ideas on what could be causing this, or what else to try? Is there any way in VBA code to remove a record lock?
