Non uso DAO da secoli, quindi non conosco i suoi meccanismi, e potrei dire qualcosa di inesatto, ma non credo:

Tu scrivi:
la sintassi è corretta, copiando il codice SQL direttamente da una query.
La query funziona il codice no.
Ciò mi fa supporre che tu abbia creato la query in ambiente MSAccess per poi copiarne il codice in VBA.
Se è proprio così allora il fatto che 'possa' funzionare in MSAccess non significa che debba sempre funzionare in VBA (o VB6), perchè l'ambiente MSAccess fa delle cose per conto suo e spesso aggiusta quelle sbagliate (ove sia possibile, comunque).

In pratica la tua query sta tentando di copiare i dati su sé stessa, il che ovviamente potrà essere sintatticamente corretto, ma potrebbe essere sbagliato nella logica:

codice:
' duplico il contratto selezionato
dbs.Execute " INSERT INTO Contratti ( IDContratto, DataInizio, DataFine, Cliente, Confermato, NoteContratto ) SELECT Contratti.IDContratto, Contratti.DataInizio, Contratti.DataFine, Contratti.Cliente, Contratti.Confermato, Contratti.NoteContratto FROM Contratti WHERE (((Contratti.IDContratto)=" & c & "));"
Ora, io non so se IDContratto è la chiave primaria (come dovrebbe essere) ed è quindi univoco (contaotre?) ma se è così ovviamente la chiave primaria non può essere duplicata.

Non vorrei dire cavolate, ma lo strano è invece che non ti restituisca alcun errore.
Però qui mi fermo perchè non conosco niente del tuo database...

In ogni caso, hai provato ad utilizzare un alias per la parte SELECT ?