JET Database/Object names and constants

Naming objects in the database
As with most other SQL databases, care is recommended when naming database objects such as tables, columns, indexes, views and stored procedures. Using names outside the normal naming conventions can cause problems when writing SQL code to handle those objects, requiring such names to be qualified in SQL code. The conventions in JET are:


 * names must begin with an alphabetic character (a-z or A-Z)
 * names must contain only alpha-numeric characters or the underscore
 * names must not be reserved words

Qualifying special names
There are occasions when objects are given names that cause problems in SQL statements, either by including special characters like spaces, or by using reserved words. Sometimes this is legitimate, other times it isn't, but either way the situation needs to be handled.

In such circumstances, the object names can be qualified in one of the following ways:
 * wrapping the name in (square) brackets,
 * wrapping the name in left single quotes (grave accents),

Here are some examples:

Complex Column Name a/b % Output Complex Text Name - 1.45                     First attempt

Text constants
Text constants can either be written with single quote (apostrophe) delimiters or double quote delimiters. Any instances of the delimiters used within the text constant need to be doubled.

T1                            T2 -- -- Eat the "food" at O'Malley's  Eat the "food" at O'Malley's

Numeric constants
Numeric constants can take the form of:


 * plain integers –
 * decimal numbers –
 * decimal numbers in scientific notation –  (equates to 12,345,000)

Numeric constants cannot have commas, dollar signs or other non-numeric characters other than the decimal point or the E in scientific notation.

Date/time constants
Date and time constants are best written as hash-delimited strings, either in long textual format, US date format or in ODBC date format.

Select #26 October 2007 6:43pm# As D_Long_Format, go
 * 1) 10/26/2007 18:43:00# As D_US_Format,
 * 2) 2007-10-26 18:43:00# As D_ODBC_Format

D_Long_Format          D_US_Format             D_ODBC_Format --- --- --- 26/10/2007 18:43:00    26/10/2007 18:43:00     26/10/2007 18:43:00

NB: JET does not unambiguously accept dates in other formats! Specifying a date in, for example, will appear to work correctly when there is no possibility of interpreting that date in US date format, otherwise it will be interpreted incorrectly, no matter what regional settings are in use. To remove ambiguity, it is best to specify dates in ODBC format.

Reserved words
This is a list of the reserved words in JET. Not all of these words will cause problems when used as object names, as at JET 4.0, but may in a future version of JET.