The following table summarizes important differences between the SQL syntax of Microsoft Access and Microsoft SQL Server, and illustrates how the Upsizing Wizard handles these differences. You can also use this reference information when you manually convert SQL syntax from a Microsoft Access database application to a Microsoft Access project application.
| SQL syntax element | Microsoft Access |
Microsoft SQL Server |
Upsizing Wizard |
|---|---|---|---|
| Identifiers | Limit of 64 characters.
Allows keywords and special characters. Can start with any character. |
SQL Server 6.5:
SQL Server 7.0 is fully compatible with Access identifiers. |
Converts as necessary to SQL Server names. |
| Output Fields | Allows multiple output fields with the same name. | Multiple output field names not supported in views. | Adds an alias for duplicate column names. |
| Date Delimiter | Pound sign (#) | Apostrophe (') | Converts delimiter. |
| Boolean Constants | True, False; On, Off; Yes, No | Integers: 1 (true), 0 (false) | Converts constant. |
| String Concatenation | Ampersand (&) | Plus sign (+) | Converts character.
Note On SQL Server, ampersand (&) is used for bit-wise logical AND operations. |
| Wildcards | Asterisk (*) matches zero or more characters.
Question mark (?) matches a single character. Exclamation point (!) means not in list Pound sign (#) means single digit. |
Percent sign (%) matches zero or more characters.
Underscore (_) matches a single character. Up caret (^) means not in list. No equivalent to the pound sign (#). |
Converts characters.
Converts pound sign (#) to [0-9] to match a single digit. |
| TOP | Automatically includes ties, if there is an ORDER BY clause. | SQL Server 6.5 does not support.
SQL Server 7.0 requires an explicit WITH TIES clause. |
For SQL Server 6.5, ignores all TOP queries.
For SQL Server 7.0, adds a WITH TIES clause to the end of all TOP queries that contain an ORDER BY clause. |
| CREATE INDEX | Allows ascending and descending indexes.
Allows declaring primary, no nulls, and ignore nulls. |
Converts index. | |
| DROP INDEX | Syntax is:
Drop Index <index name> ON <table name> |
Syntax is:
Drop Index <table name>, <index name> |
Doesn't convert. |
| DISTINCTROW | Supported (allows selection of unique records). | Not supported. | Doesn't convert. |
| OWNERACCESS | Supported (controls permissions during execution). | Not supported. | Removes from SQL statement. |
| Table in UNION | Supported (allows tables to be specified by using the syntax:
TABLE <tablename> |
Not supported. | Doesn't convert. |
| ORDER BY in Unions | Supported. Allows multiple order by clauses in union queries. | Supported Allows only one order by clause at the end of the statement. | Doesn't convert. |
| TRANSFORM | Supported. Used for cross tab queries | Not supported | Doesn't convert. |
| PARAMETERS | Supported (Recorded in SQL). | Not supported. | Removes explicit parameters from SQL string. |