To resolve the issue if you have the constraint in Access, and then upsized it to SQL Server, you will have that some constraint in SQL Server, so you would need to remove it in SQL Server and then relink the table.

But the bigger concern is the fact that you are trying to enter a record with a key that is a duplicate. Rather than using the table that has the single record, which can always go south with a failed update or other issues, we use the Max of the value in the table that actually contains the records. That way you can be almost certain that there will never be a duplicate. The only possible issue is when two or more people are adding records, and one person opens a record, then the second person opens a record and saves it. Meanwhile the first person is distracted by something and when they go to add the record they will get a failure with dup key. For that reason, we almost always use an AutoNumber field (Identity in SQL Server) as the primary key. You may not have much flexibility as it is an existing system, but I would certainly try to redesign the process so you avoid those kind of errors.