We have a Microsoft Access 2010 ADP file, which is currently working perfectly with a Microsoft SQL Server 2019 (on-premises) database in the back end. As part of a project, we are trying to migrate the backend to Amazon RDS for SQL Server.
After a test migration, we are able to get 99% of the ADP forms to function correctly. However, there is a single MS Access form (which is an Updateable Snapshot, with a recordsource that links two tables via an inner join), that is not working as hoped. It can retrieve and display records fine, and it can even allow edits on existing records. However, it produces an error when a user tries to add a new record to the bottom of the list:
Please also note that errant number "1" appended to the end of the query by Microsoft Access, which all but guarantees the query should fail.
What seems to work: If I completely remove the inner join in the SQL query in the record source, so that the record source is only retrieving data from a single database table, then we can add records correctly. However, this kills the form's original functionality, as we are missing required fields from the second table.
I understand that Microsoft Access 2010 ADP files are long since deprecated, and we should be killing it with fire post-haste. I also understand that Microsoft makes no assurances that MS Access ADPs will work with any SQL Server version beyond SQL Server 2008. However, I wanted to see if there was some obvious oversight I was making, given that it works well with SQL Server 2019 on premises.
Kind Regards.
