I feel ridiculous having to ask this question but I've been struggling with it for too long now and have tried/changed so many things I feel like I'm lost in the woods.
I am working with SQL2014 for the first time. Our shop is on Access 2010. The back end databases have been set up, all boolean fields are set with defaults, rowversion/timestamp fields have been added (programmatically created as rowversion but for some reason showing as timestamp). The SQL databases are v120 (2014). I'm using ODBC Driver 11 for SQL Server. The clients are Win7 Pro or Enterprise, 64-bit. Office is 32-bit.
The SQL project uses a main utility db containing user credentials/configurations and lookup tables/configurations shared by all localities. Then there are locality DBs that are all structurally identical, with the tables storing data for the localities.
The Access database checks logins, links any utilities db objects as needed, decides which databases the user can access, and allows them to connect to localities one at a time, linking the locality tables when the data form opens and killing the links when the data form closes. The locality data form displays the records from the main table, and 8 related tables. The main form has 8 subforms plus a display-only listbox that shows the results of a union query (unique surnames for the record).
I'd thought I'd done this sort of thing before without any problems, but looking back I see that none of my projects seem to involve SQL linked tables and lots of subforms off a main record, so I haven't really come across this before. And my other projects were born in previous versions of SQL/Access and migrated forward. So, I can't tell if this is Access/SQL version-related or not.
The problem I can't resolve:
I can add, update, and delete records in the subforms, and the changes show immediately on the back end. I can delete or edit a record, and then delete or edit records in that subform and other subforms, or in the main record, without any problem. But after adding a record to a subform, I cannot edit anything in that subform, even if I scroll to a different main record! The new subrecords will load fine when changing main record but the subform is ruined for any further edits, only additions. Any attempt to edit a record in a subform ON ANY parent record once a record has been added results in the record fields showing #DELETED and the following error appears:
"The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record. "
If I then hit F5 in the subform, the data displays normally again, but I still cannot edit records. I can continue adding records, which also appear immediately on the SQL side. I am not prevented from deleting records but the records don't delete, I just get a message that the record has been deleted (which does not appear normally when deleting a record in a subform, only when it gets stuck in this loop).
Based on disconnected bits of advice found elsewhere, I've set the recordset types in all the forms to Dynaset (Inconsistent), forced a requery and refresh on both the edited subform and the parent after each update (tried it with subform first and also parent first, no difference). I've tried changing the client-level options to use row locking on open or not, and altering the Refresh and ODBC refresh intervals. NONE of it helps.
Anyone have any ideas on where to look?
Thanks,
spud
I am working with SQL2014 for the first time. Our shop is on Access 2010. The back end databases have been set up, all boolean fields are set with defaults, rowversion/timestamp fields have been added (programmatically created as rowversion but for some reason showing as timestamp). The SQL databases are v120 (2014). I'm using ODBC Driver 11 for SQL Server. The clients are Win7 Pro or Enterprise, 64-bit. Office is 32-bit.
The SQL project uses a main utility db containing user credentials/configurations and lookup tables/configurations shared by all localities. Then there are locality DBs that are all structurally identical, with the tables storing data for the localities.
The Access database checks logins, links any utilities db objects as needed, decides which databases the user can access, and allows them to connect to localities one at a time, linking the locality tables when the data form opens and killing the links when the data form closes. The locality data form displays the records from the main table, and 8 related tables. The main form has 8 subforms plus a display-only listbox that shows the results of a union query (unique surnames for the record).
I'd thought I'd done this sort of thing before without any problems, but looking back I see that none of my projects seem to involve SQL linked tables and lots of subforms off a main record, so I haven't really come across this before. And my other projects were born in previous versions of SQL/Access and migrated forward. So, I can't tell if this is Access/SQL version-related or not.
The problem I can't resolve:
I can add, update, and delete records in the subforms, and the changes show immediately on the back end. I can delete or edit a record, and then delete or edit records in that subform and other subforms, or in the main record, without any problem. But after adding a record to a subform, I cannot edit anything in that subform, even if I scroll to a different main record! The new subrecords will load fine when changing main record but the subform is ruined for any further edits, only additions. Any attempt to edit a record in a subform ON ANY parent record once a record has been added results in the record fields showing #DELETED and the following error appears:
"The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record. "
If I then hit F5 in the subform, the data displays normally again, but I still cannot edit records. I can continue adding records, which also appear immediately on the SQL side. I am not prevented from deleting records but the records don't delete, I just get a message that the record has been deleted (which does not appear normally when deleting a record in a subform, only when it gets stuck in this loop).
Based on disconnected bits of advice found elsewhere, I've set the recordset types in all the forms to Dynaset (Inconsistent), forced a requery and refresh on both the edited subform and the parent after each update (tried it with subform first and also parent first, no difference). I've tried changing the client-level options to use row locking on open or not, and altering the Refresh and ODBC refresh intervals. NONE of it helps.
Anyone have any ideas on where to look?
Thanks,
spud