Perchè un database Access è nato per essere gestito dall'ambiente di lavoro MS Access.
Quindi include alcuni metodi che funzionano SOLO se utilizzate con MSAccess.

Alcune funzionano ANCHE in VB6 perchè sono proprie della libreria VBA, che in SQL SERVER ovviamente non esiste.
Anzi, oggi bisogna fare un distinguo: se si installa Microsoft Office 2010 alcune funzioni anche banali non 'funzionano' più, ad esempio Round() nelle query viene segnalata come NON TROVATA (invece c'è ancora, è lì, ma non funziona in VB6).
In pratica, se il cliente decide di passare a Office2010, e nel programma si sono utilizzate nelle query delle funzioni 'interne' di Access il programma potrebbe non funzionare più.

La tua affermazione:
Il linguaggio SQL non è compatibile con Sql Server.
non corrisponde al vero perchè stai confrontando pere con mele.
Access e SQL Server sono due cose completamente diverse.

Per cui è corretto affermare che il SQL di Access e le istruzioni/funzioni di Access non sono compatibili con SQL Server nella misura in cui queste sono proprie ed esclusive di Access, ma non vedo perchè ciò dovrebbe stupire...
Sarebbe come pretendere che SQL Server fosse compatibile con MySQL.
Per quale ragione dovrebbe esserlo?

Al contrario, se si usano istruzioni SQL del linguaggio standard SQL-ANSI 92 (ovvero NON proprietarie di Access) la stessa istruzione funziona su qualsiasi database.

Il SQL-ANSI 92 è la base del linguaggio SQL, poi ogni database può implementare (e di solito è così) un proprio dialetto (in SQL Server è il TSQL) che incrementa funzionalità non previste dal SQL-ANSI 92.
E' comunque vero che possono anche esservi dei casi in cui database di marche diverse hanno alcune limitazioni, ma sono relativamente poche e ben documentate.


Riguardo all'affermazione:
mi tocca rimettere mano alle 560 finestre
ciò significa che hai sparso le query per tutti i form dell'applicazione.
Questa è una modalità di programmazione vecchia, ma d'altronde se il programma è stato realizzato diversi anni fa è comprensibile, magari se realizzato da 'autodidatta' con poca esperienza.

Questo approccio è sbagliato perchè, come sta accadendo a te, rende necessario rivedere tutto il codice dell'applicazione.
Invece è neccessario separare l'accesso ai dati da quello che è l'UI (interfaccia utente).
Mi spiego in parole semplici: è quello che in gerco tecnico vengono definite le 'classi helper' ovvero quelle classi che 'raggruppano' tutte le istruzioni per l'accesso ai dati e si creano per due motivi principali:

1) Avere tutte le istruzioni in un unica classe, il che significa
a - codice più facile da manutenere
(ho aggiunto un campo? mi basta modificare il codice della classe, senza essere costretto ad andare 'a caccia per tutto il codice sorgente' perchè ovviamente non mi ricordo se devo aggiornare altre query).

b - Poter sfruttare le stesse funzioni, metodi e proprietà per tutto il progetto

2) Posso creare 'classi helper' diverse a seconda del database (SQL Server, Oracle, DB2, ...) con cui devo interfacciarmi;
oppure posso usare la stessa 'classe helper' creando diverse copie della stessa routine, ma predisposte per database diversi.

Concludendo, ogni migrazione è un'opportunità per crescere, perchè è ovvio che errori di progettazione commessi in passato (quando ancora non erano o non sembravano errori) oggi vengono 'al nodo'.
Ci siamo passati tutti, chi più chi meno; sta a noi scegliere se fare il 'salto' oppure no in funzione di valutazioni assolutamente soggettive che riguardano i costi, le risorse, la convenienza, ...
Ognuno deve fare i conti in casa propria.

Un po' come è accaduto quando da VB6 si è passato al .NET:
nonostante tutte le balle che ci hanno rifilato sulla facilità, compatibilità etc. etc. è stato (e sarà per chi deve ancora farlo) un 'salto nel buio'.
Ma anche questa è un'opportunità di crescita, ed alla fine la soddisfazione è impagabile.

Scusa se mi sono dilungato.