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