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: Create Table `Name With Spaces` ( `Complex Column Name a/b %` single, [Text] text(20)) go Insert Into `Name With Spaces` (`Complex Column Name a/b %`, [Text]) Values (1.45, 'First attempt') go Select `Complex Column Name a/b %`, [Text] As [Output Complex Text Name] From `Name With Spaces` go Complex Column Name a/b % Output Complex Text Name ------------------------- ------------------------ 1.45 First attempt Constants 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. Select 'Eat the "food" at O''Malley''s' As T1, "Eat the ""food"" at O'Malley's" As T2 go 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 – 123 decimal numbers – 123.45 decimal numbers in scientific notation – 123.45E5 (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 (mm/dd/yyyy) or in ODBC date format (yyyy-mm-dd). Select #26 October 2007 6:43pm# As D_Long_Format, #10/26/2007 18:43:00# As D_US_Format, #2007-10-26 18:43:00# As D_ODBC_Format go 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 dd/mm/yyyy, 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. absolute action add admindb all allocate alphanumeric alter and any are as asc assertion at authorization autoincrement avg band begin between binary bit bit_length bnot bor both bxor by byte cascade cascaded case cast catalog char character char_length character_length check close coalesce collate collation column commit comp compression connect connection constraint constraints container continue convert corresponding count counter create createdb cross currency current current_date current_time current_timestamp current_user cursor database date datetime day deallocate dec decimal declare default deferrable deferred delete desc describe descriptor diagnostics disallow disconnect distinct domain double drop else end end_exec escape except exception exclusiveconnect exec execute exists external extract false fetch first float float4 float8 for foreign found from full general get global go goto grant group guid having hour identity ieeedouble ieeesingle ignore image immediate in index indicator inheritable initially inner input insensitive insert int integer integer1 integer2 integer4 intersect interval into is isolation join key language last leading left level like local logical logical1 long longbinary longchar longtext lower match max memo min minute module money month names national natural nchar next no not note null nullif number numeric object octet_length of oleobject on only open option or order outer output overlaps owneraccess pad parameters partial password percent pivot position precision prepare preserve primary prior privileges proc procedure public read real references relative restrict revoke right rollback rows schema scroll second section select selectschema selectsecurity session session_user set short single size smallint some space sql sqlcode sqlerror sqlstate string substring sum system_user table tableid temporary text then time timestamp timezone_hour timezone_minute to top trailing transaction transform translate translation trim true union unique uniqueidentifier unknown update updateidentity updateowner updatesecurity upper usage user using value values varbinary varchar varying view when whenever where with work write year yesno zone