The problem here is the connection to the database server will continue to function, and Access itself as a client will be able to function and update records. So Access doesn't care if you take a bunch of columns set up as a primary key and replace it with an auto number ID or what ever for single column as the primary key.
However saying yes to the above means absolutely nothing and does not help you in any way shape at all here because that's not the right question. The question here is does the program logic itself rely on these functionality of having set up these primary keys in such a fashion in the first place?
For example we might be booking rooms. So the primary key might be the date and the room number. So now all the program logic has to do after the date and room number is entered into the system is attempt to write out the record. If the error message returned back is a primary key violation then the program logic might pop up the message and says you can not book that room (number) for that day.
If you change that application to run with a primary key to some ID column, then when the program logic attempts to write out that record there not going to be a error message of a primary key violation anymore. Adding in some constraints or a index that says the two columns must be unique WILL NOT solve this because you'll have to find the places in the application where code is looking for a primary key violation and now modify that code to make it look for some type of index error or some type of constraint violation error.
And by the way this question is not really particular to ms access, but virtually any software and application programming environment you use would be effected by the above issue. The only way to find this out is you're going to have to look through all the lines of code and all the pieces and parts of the application to see if any of them rely on the fact of the primary key structure has any functionality that exists within the application. You might be lucky and perhaps any table error will suffice here, but you don't know till you look at the actual code itself.
There's simply no way to determine this issue except by looking ALL OF the code in the places where data is being updated.
So while most things should work and likey forms will still edit data. In other words, Access will not care much, but the code and application itself may certainly care very much about this issue.
I mean even on the SQL server side, there might be some stored procedures and triggers that work off of this very fact. If you modify what makes up a primary key, then what about the existing stored procedures and even the many relationships that are based on the assumptions of the current design ?
Now existing sql store procedures and even sql triggers may cease to function correctly based on the assumptions of the current design. As you can see once again SQL server does not care if you change your primary key from a couple columns to in some type of ID column. However the program code logic and triggers and everything else written around in the database system may very well care about this issue.
And of large number of any of the joins designed within that database will obviously have multiple columns used for joins between tables. You will have to go messing around to find all those joins and not only remake the constraints, but there's also a good possibility that other RI (referential integrity ) options such as cascade deletes etc are based on these multi column joins.
While for the most part the cascade deletes likely can be changed without problems, some cascade delete restrict logic actually may be lost by converting to a single column join. Even more problematic is the delete restrictions placed on the child tables may certainly not pass unscathed if you change the current design.
For example you can't delete customer from the system if they still have invoice data. However the current system actually might be that you can actually delete a customer that has invoices and room bookings and they have to be older than one year. Once again it might be some kind of compound key that is preventing something from being deleted in the system. You change this to single joins and again some of your program logic and even some delete restrict constraints based on 3 things on the SQL server side might very well breakdown.
So your question comes down to the fact that all the current programming constraints and even on the SQL server side are all based around a set of rules and assumptions that we're all designed around these compound columns in keys and constrains .
So once again even without looking at the ms-access client application parts, you're gonna have to look at the server side of things, and figure what's going on TWO different applications now.
Unless someone on your team is intimately familiar with the operations of this software and ALSO THE code base on BOTH systems, then there are too many risks and pitfalls that can occur by messing around with these relationships and PK structures. This becomes high risk. A small change in data structures could cause bugs that could take hours or even days to track down in an application with lots of functionality.
If this was just some tables and no application and no code had been written yet, then this would be a far easier task.
However to try and reengineer a application by fooling around with table relationships and constraints and even structures while the applications supposed to be running and functioning with that data is a very big daunting task. Your big problem here is how do you know you're not introducing bugs?
Even if you're working on a copy the application that's not in production, then you'll be make modifications and you'll be getting no feedback as to if something's even being broken.
This issue is not limited to access, but you also introducing risk on the server side code also.