If the relationship is many-to-many but not repeatable, the two foreign keys in the linking table can become a compound prime key in that linking table. In a normal table, a compound key makes sense only if the table is in some way a child (or "partial" child) of another table. (In other words, one or more of the candidate members of the compound PK are themselves foreign keys into another table.)
For instance, suppose that you are a distributor who makes no products of your own. You have suppliers. The supplier table has a simple PK, maybe only a WORD integer, because you don't have THAT many suppliers.
Each supplier has something they can supply. For each thing they can supply, they have a product number or code. For any single supplier, the code is unique per product. (In other words, with respect to one supplier, the code is a candidate for being a prime key.)
In this case, you could either provide an autonumber for YOUR product table and have supplier ID and supplier's code as non-key elements of your table. OR you can have the supplier's code and the supplier ID number as a composite PK. See discussion below about why this isn't recommended for everyone. This method even works if you make something on your own - but then YOU have to have a supplier ID, too!
You use autonumber PKs in preference to composite PKs when there is a huge size disparity between the resultant keys. Consider, for example, that a supplier's part number is a 10-character string and the supplier ID is at least a number from 1 to 65K (WORD integer). This makes a composite PK of 12 bytes. Whereas a LONG integer autonumber is a 4-byte key. The index that holds these keys must hold 8 more bytes for the composite key than for the autonumber key. The bigger the PK (whether simple or composite), the less you can fit into a single buffer when searching for the matching key. This has a drastic effect on the speed of your search. Also, the bigger the PK, the more space you take up in other tables for which that table's PK is the other table's FK.
OK, having said that, you can still use compound PKs if all of the component fragments are themselves Autonumbered or otherwise short fragments. Just remember, the more members of the composite key, the longer it gets and the less efficient it gets.
There are times when you can positively be sure that a composite key is the right choice ... when all of the component key parts are VERY short such that the whole key is not much longer than a LONG integer. Such times of certainty are, however, woefully rare.