List of ADO SQL syntax differences between Jet MS Access and SQL Server

I currently use SQ开发者_如何学CL via ADO 2.8 (with Delphi) with Access databases, and sometimes with SQL Server 2005. But the syntaxes are not the same for the 2 databases. Is there, somewhere, a list of the differences in order to adapt the queries for the 2 databases ? Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) but it seems not to be up to date.

Remark: I don't use existing databases; I create them with ADO.

Informations with some 'edits' help : 1/ My goal is to have only one Query for Access & MS SQL and to translate the query depending on the choosen database. For it I need to know what is the best syntax for my query and how (and if I have to) to translate when I change my database.

Example : for the datetime : Access : SELECT * FROM MYTABLE WHERE SomeDate = #1/1/2005#

T-SQL: SELECT * FROM MYTABLE WHERE SomeDate = '1/1/2005'

But I found some informations where you may use for both : SELECT * FROM MYTABLE WHERE SomeDate = #2011-02-22 00.00.00#

If your interest is DDL queries for Access (Jet/ACE) and SQL Server, see this table of data types on MSDN: Equivalent ANSI SQL Data Types

First, trying to make a magic translator between Access and SQL Server is simply not worth the time. It would be easier to encapsulate the queries into a library for Access and another for SQL Server that interact through an interface. Second, the link is current enough since you are using Jet. That said, there is one item not mentioned in the link which is notable.

Parentheses in Joins for Access.

In Access, if you Join two tables, you do not need any parentheses. However, once you go beyond two tables, you must do nonsense like:

Select
From (TableA
    Inner Join TableB
        On TableB.FK = TableA.PK)
    Inner Join TableC
        On TableC.FK = TableA.PK

SQL Server of course does not require this but will honor it if it is used. If you go beyond three tables, I have no idea what algorithm Jet (and Access' QBE grid) uses to determine the allowed syntax but in essence it is pairing the tables.

Here are some links about Jet SQL that, even though the first two reference Access 2000, they are still relevant to any Jet development:

Intermediate Microsoft Jet SQL for Access 2000

Advanced Microsoft Jet SQL for Access 2000

Jet SQL Syntax for Select Queries