How to use constants to handle Jet and Transact SQL conflicts

Many Access databases are upsized to SQL Server, and usually that means a lot of work because the two databases use different versions of SQL. A perfect example is wildcards, which often appear in WHERE clauses. Specifically, Jet uses the asterisk character (*) to match any string. T-SQL uses the percent sign (%).For instance, the following Jet clause would match Smith, Smyth, and Smithers:

WHERE LastName Like "Sm*"

T-SQL's version matches the same entries:

WHERE LastName Like 'Sm%'

Instead of writing statements for both versions, declare two constants, as follows:

'For Jet
Const conWildCard = "*"
'For T-SQL
'Const conWildCard = '%'

Notice that I've commented out the T-SQL version. Consequently, Access will use the Jet version. When upsizing, you just comment out the Jet version and use the T-SQL version. That simple change will update every wildcard reference in your code, as long as you've used the appropriate constant.

If you go this route, you should accommodate all the Jet and T-SQL wildcards listed below:

Jet (ANSI 89) T-SQL (ANSI 92)
* %
? _
[ ] [ ]
! ^
- -

Access 2007 lets you choose which ANSI version the current database or all new databases use. To make the switch, follow these easy steps:

  1. Choose Access Options from the Office menu.
  2. Select Object Designers.
  3. Choose This Database or Default For New Databases from the SQL Server Compatible Syntax (ANSI 92) option at the bottom of the pane.

You can do this in version 2003 and 2002, but it's a more involved process. First, you must set the database to use the 2002-2003 format:

  1. Choose Options from the Tools menu.
  2. Click the Advanced tab.
  3. From the Default File Format control, choose Access 2002 - 2003, if necessary. Remember, by default, these versions use the 2000 format.

Once the database is using the 2002-2003 format, set the ANSI 92 standards as follows:

  1. Choose Options from the Tools menu.
  2. Click the Tables/Queries tab.
  3. In the SQL Server Compatible Syntax (ANSI 92) section, check the appropriate option.

Switching to ANSI 92 will cover wildcards in query expressions and code. Keep in mind that it will affect many issues, not just wildcards. Use care when making this change. Check Comparison of Microsoft Jet SQL and ANSI SQL in Help for more information on which features respond differently to ANSI.