JET Database/Print version

JET Database

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/JET_Database

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

Introduction

What is JET?

JET is a SQL database engine distributed by Microsoft with several of its software products, most notably Microsoft Access. JET was an acronym that stood for Joint Engine Technology, when JET was originally developed as a collection of core database technologies for use within Access, and later from Visual Basic and Visual Basic for Applications via an object-based interface called Data Access Objects (DAO).

JET presents the developer with the ability to create and manipulate relational databases on the local filesystem or on shared network folders. It is a modified form of ISAM, or Indexed Sequential Access Method databases.

Reasons for not using JET

There are several good reasons for not using JET as a database technology, either when starting a new software project or when picking up maintenance on an old one.

• Microsoft have ceased support for JET, preferring instead to support newer database products based on their SQL Server product line.
• Operation of JET from a 64-bit process requires use of a different driver. The connection string for access to a JET database using a 64-bit ADO program will contain a phrase like: "Provider=Microsoft.ACE.OLEDB.12.0"
• Technical information on JET is hard to find without a lot of digging, largely due to Microsoft dropping support for JET.
• JET is not as robust as more modern server-based database products, particularly in multi-user scenarios.
• JET SQL has many limitations that other database products don't have.
• JET stored procedures can only accommodate a single statement.
• JET databases, not being server-based, use far greater network bandwidth in multiuser applications.

Why this book is still needed

There is a very large installed base of JET databases worldwide, and despite Microsoft's intentions to replace JET with SQL Server and MSDE, this base is still growing – people keep making new JET databases. Additionally, because JET is the default database sub-system used by Microsoft Access, it is very commonly used to create "simple little databases" that grow into monsters, often undocumented and unwieldy and, sadly, often built with little regard for good database design.

It is often left to the professional programmer or database administrator to maintain these databases, and this may require some technical information about JET. Microsoft used to release documentation on programming JET in its Microsoft Developer Network library, but it dropped documentation for JET SQL statements from the CD copy of the library after October 2001, along with the documentation for Access '97.

Alternatives

There are alternatives to maintaining a JET database, some of which are:

• Migrate the data from JET to Microsoft SQL Server using one of the many migration tools, and run the old application as JET linked tables in SQL Server transparently.
• Redesign the application to use Microsoft SQL Server natively, with many database statements remaining unchanged.
• Redesign the application to use another database, probably triggering a rewrite of many database statements.

These alternatives bear differing degrees of effort and risk. Depending on the associated costs and the future prospects for a database application (including its anticipated demise), it may be most viable to continue maintenance of the JET database.

Creating and connecting

Creating a JET database

There are many ways to create a JET database, a few of which are addressed below. How a database is created should not matter, except that the engine type of the database defines what JET SQL capabilities will be available.

JET version	Engine Type code
JET 1.0	1
JET 1.1	2
JET 2.0	3
JET 3.x	4
JET 4.x	5

Creating from Access

By far, the easiest way to create a new JET database is to use Microsoft Access.

1. Start Microsoft Access
2. From the menu, select File → New or press Control-N
3. Select "Blank database"
4. Navigate to where you want to save your database in the filesystem, and give it a name

You can then use the various database and security utilities to set things like the engine type, encryption, optional system database, and users / passwords.

Creating from ODBC

An empty JET database can be created from Windows by creating a new ODBC DSN (Data Source Name) and creating the database from the ODBC connection window.

Start the ODBC Data Sources applet from the Windows Control Panel, and click Add to make a new DSN. You should see a screen like the one on the right. Select the driver for Microsoft Access, and click Finish.

You should be presented with a screen for configuring a new Microsoft Access DSN, like the one on the right. Click the Create button, to get the New Database window up.

You should now see the New Database window. Navigate to where you want to put your JET database, enter a name, and choose any special options like engine type (Format), encryption, and whether to have a separate system database, then click OK to create your database. You can now cancel out of the ODBC Data Sources applet without making the new DSN if you like, as you probably want to connect to it via OLE-DB anyway!

Creating from ADOX

Visual Basic code for creating a JET 4.0 compatible database:

Dim cat As New ADOX.Catalog
Dim create_string As String
Dim conn As ADODB.Connection

create_string = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;"
create_string = create_string & "Data Source=" & path_to_database & ";"

If JET_encryption_wanted Then
    create_string = create_string & "Jet OLEDB:Encrypt Database=True;"
End If

Set conn = cat.Create(create_string)

conn.Close()

Compacting a JET database

JET databases do not reuse space released by deleting old data. This leads to databases growing in size over time, and can lead to performance problems or even failures if the database grows too large. To overcome this, a database needs to be compacted periodically, to remove the unused space and reduce the file size.

Compacting from Access

The easiest way to compact a JET database is to open it in Microsoft Access and perform an operation called Compact and Repair Database, found under the Tools menu. However, this may not be possible if the database is located remotely and is not accessible via the Windows filesystem.

Compacting with JRO

The JET runtime system is distributed with a component library called JET Replication Objects (JRO). The following Visual Basic code uses JRO to compact a JET database:

' declare Windows API functions
Declare Function MoveFileEx Lib "kernel32" Alias "MoveFileExA" _
    (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
    ByVal dwFlags As Long) As Long

' build connection strings for database and temporary file
Dim cx as string
Dim src_cx As String
Dim tgt_cx As String

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;"

If uses_database_password Then
    cx = cx & "Jet OLEDB:Database Password=" & database_password
ElseIf uses_username_password Then
    cx = cx & "User ID=" & user_id & ";Password=" & user_password & ";"
End If

src_cx = cx & "Data Source=" & src_file & ";"
tgt_cx = cx & "Data Source=" & tmp_file & ";"

' run Jet Replication Objects to compact the database to a temporary file
Dim compacter As New JRO.JetEngine
compacter.CompactDatabase src_cx, tgt_cx
Set compacter = Nothing
    
' replace the old database with the compacted database, under the same name
Dim scode As Long
scode = MoveFileEx(tmp_file, src_file, MOVEFILE_REPLACE_EXISTING + MOVEFILE_COPY_ALLOWED)

Connecting to a JET database

Connecting from ADO

When connecting to a database from ADO, one can connect using the JET OLE-DB provider or an ODBC DSN (Data Source Name). This section addresses using the OLE-DB provider, since that is the most efficient mechanism and allows greater capabilities than the ODBC DSN path.

Connections via the JET OLE-DB provider require information about the database to be passed in the OLE-DB connection string. If the database has a System database, or uses a database-level password, then this information must be provided in the connection string.

The following Visual Basic code demonstrates connecting to a JET database from ADO, using a username and password:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
Set db = CreateObject("ADODB.Connection")
db.Open cx, user_id, user_password

The following Visual Basic code demonstrates connecting to a JET database from ADO, using a database-level password:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
cx = cx & "Jet OLEDB:Database Password=" & database_password
Set db = CreateObject("ADODB.Connection")
db.Open cx

The following Visual Basic code demonstrates connecting to a JET database with System database from ADO, using a username and password:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
cx = cx & "Jet OLEDB:System Database=" & system_db_filename & ";"
Set db = CreateObject("ADODB.Connection")
db.Open cx, user_id, user_password

DAO vs SQL

JET engine versions up to JET 3.0 had limited support for database object creation using SQL statements, whilst full support was available via DAO. However, since JET 4.0, the JET SQL language allows support for most (if not all) database object attributes. Some design tools, however, have not been updated to reflect this and still use DAO (usually via ODBC) to make alterations to the database.

Data types

Quick Reference

JET type	VBA type	Size	Values
byte, tinyint, integer1	Byte	1 byte	integers 0 to 255
smallint, short, integer2	Integer	2 bytes	integers -32,768 to 32,767
integer, int, long, integer4	Long	4 bytes	integers -2,147,483,648 to 2,147,483,647
single, real, float4, IEEESingle	Single	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
double, number, double precision, float, float8, IEEEDouble	Double	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
currency, money	Currency	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
decimal, numeric		17 bytes	fixed precision decimal numbers
counter, identity, autoincrement	Long	4 bytes	auto-incrementing integer for surrogate keys
bit, yesno, logical, logical1	Boolean	1 bit	0 or 1 (may appear as 0 or -1 sometimes)
datetime, date, time	Date	8 bytes	1 January, 100 to 31 December, 9999 with optional time information
text, varchar	String	<= 255 characters	variable-length text strings, with Unicode support since JET 4.0
char	String	<= 255 characters	fixed-length text strings, with Unicode support since JET 4.0
longchar, memo	Memo	<= 2 GB	variable length text BLOB
binary, binary varying	Binary	<= 510 bytes	fixed-length binary data
longbinary, image, general, oleobject	Binary	<= 2 GB	variable length BLOB
guid	Binary	16 bytes	uniqueidentifier

Text types

There are two basic data types, plus a BLOB type, that are used to hold text values in JET databases. Prior to JET 4.0 they could only hold 8-bit text characters, but databases created with an Engine Type code of 5 or greater (i.e. JET 4.0) can also store Unicode characters.

Since JET 4.0, text values that are primarily one byte (8-bit) text characters are stored in two bytes unless otherwise specified. This increases the storage required for all text data, effectively doubling it. JET 4.0 has an additional property that can be set on text columns to compress text data to one-byte characters when it is able to. This property is set by specifying WITH COMPRESSION on each text column that requires it. When a table is created in a JET database using Microsoft Access, text columns are normally created with this setting turned on (as Unicode compression).

Fixed-length text

The char data type is used to store fixed-length text with up to 255 characters. Specifying the number of characters to store limits how big the column will be. Text values retrieved from a char column are padded with spaces, if necessary, to the size of the column.

The char data type is not available from the Access designer.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

Create Table T1 (c1 char(10) with compression, c2 char with compression)

Variable-length text

The varchar data type is used to store variable-length text with up to 255 characters.Text values retrieved from a varchar column are trimmed of any trailing spaces.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

Create Table T2 (c1 varchar(10) with compression, c2 varchar with compression)

Text BLOB

The longchar data type is used to store variable-length text with an unspecified number of characters, limited only by the maximum size of JET database files (2 GB – about 1 billion uncompressed Unicode characters).

Some software libraries are able to handle longchar columns as basic text columns, but others must use BLOB techniques for accessing their data. In particular, the ADO components so often used in Visual Basic, VBA and ASP applications can access longchar columns as basic text when using the JET 4.0 OLE-DB provider to access the database, but must use BLOB handling routines (GetChunk / AppendChunk) when using an ODBC connection.

The following statement creates a table with two variable-length text BLOB columns, both with Unicode compression:

Create Table T3 (tb1 longchar with compression, tb2 memo with compression)

Text pseudo-types

The text data type is a pseudo-type. When a size is specified, it is taken to be a varchar and is limited to 255 characters. When no size is specified, it is taken to be a longchar.

The following statement creates a table with a 10-character text column and a variable-length text BLOB column, both with Unicode compression:

Create Table T4 (c1 text(10) with compression, tb1 text with compression)

Numeric types

There are several basic numeric data types available in JET, allowing for a wide range of data storage possibilities for numeric values.

Byte

The byte data type is an 8-bit unsigned integer that can hold values between 0 and 255 inclusive. It can be referred to by the following aliases: byte, tinyint, integer1.

It is analogous with the Visual Basic type Byte.

Smallint

The smallint data type is a 16-bit (2-byte) signed integer that can hold values between -32,768 and 32,767 inclusive. It can be referred to by the following aliases: smallint, short, integer2.

It is analogous with the Visual Basic type Integer.

Integer

The integer data type is a 32-bit (4-byte) signed integer that can hold values between -2,147,483,648 and 2,147,483,647 inclusive. It can be referred to by the following aliases: integer, int, long, integer4.

It is analogous with the Visual Basic type Long.

Single

The single data type is a 32-bit (4-byte) single precision floating point number that can hold values from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. It can be referred to by the following aliases: single, real, float4, IEEESingle.

It is analogous with the Visual Basic type Single.

Double

The double data type is a 64-bit (8-byte) double precision floating point number that can hold values from -1.79769313486231E308 to -4.94065645841247E-324 for negative values, and 4.94065645841247E-324 to 1.79769313486232E308 for positive values. It can be referred to by the following aliases: double, double precision, number, float, float8, IEEEDouble.

It is analogous with the Visual Basic type Double.

Currency

The currency data type is a 64-bit (8-byte) exact precision decimal number (implemented as a scaled integer) that can hold values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 inclusive. It can be referred to by the following aliases: currency, money.

It is analogous with the Visual Basic type Currency.

Decimal

The decimal data type is a 17-byte exact precision decimal number that can hold values from -10²⁸-1 to 10²⁸-1. It can be referred to by the following aliases: decimal, dec, numeric.

Boolean (Yes/No)

JET supports a data type called bit that can hold one bit of information, used for holding simple Boolean states of true or false. It can only hold the values 0 or 1, but some interfaces into the JET database present these as 0 and -1, or No and Yes. It can be referred to by the following aliases: bit, logical, logical1, yesno.

It is analogous with the Visual Basic type Boolean.

Dates and times

The datetime data type is a 64-bit (8-byte) double precision floating point number that can hold both date and time information, in the range 1 January, 100 to 31 December, 9999 and with optional times in hours, minutes, seconds. It can be referred to by the following aliases: datetime, date, time, all capable of holding just date, just time, and both date and time information.

It is analogous with the Visual Basic type Date.

To determine whether a datetime holds time information, check to see whether the double precision number is the same as the integer truncation of that number; if they differ, then there is time information, otherwise there is no time information.

Create Table D1 (dt1 datetime)
go
Insert Into D1(dt1) Values (#2007-02-02#)
go
Insert Into D1(dt1) Values (#2007-02-02 16:26:29#)
go
Select dt1, CDbl(dt1) - CLng(dt1) As check_time From D1
go

dt1                     check_time
----------------------- -------------------------
02/02/2007 16:26:29     -0.314942129632982
02/02/2007              0

Counters

The counter data type is an auto-incrementing 32-bit (4-byte) integer, often used for creating surrogate keys. It can be referred to by the following aliases: counter, autoincrement, identity.

A counter can take optional parameters, defining the initial value to start counting at, and the increment to add each time a new value is created. The following code shows both the default, starting at one with increment of one, and with specified start of 10 and increment of 2. Each table can have, at most, one column of type counter.

Create Table A1 (id1 counter, c1 char(1))
go
Create Table A2 (id2 counter(10, 2), c2 char(1))
go
Insert Into A1(c1) Values('x')
go
Insert Into A1(c1) Values('y')
go
Insert Into A2(c2) Values('x')
go
Insert Into A2(c2) Values('y')
go
Select * From A1
go
Select * From A2
go

id1         c1 
----------- ------
1           x
2           y

id2         c2
----------- ------
10          x
12          y

BLOBs

JET can store binary large objects, or BLOB data, in the database directly as binary data. Accessing this data typically requires special BLOB handling functions, for example, ADO provides the functions GetChunk and AppendChunk for reading and writing BLOB data.

Binary

The binary data type is a variable-length binary object type that can hold up to 510 bytes of binary data. If the size is not specified, then the maximum size of 510 bytes is used. It can be referred to by the following aliases: binary, binary varying.

Longbinary

The longbinary data type is a variable-length binary object type with an unspecified capacity, limited only by the maximum size of JET database files (2 GB). It can be referred to by the following aliases: longbinary, general, image, oleobject.

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

Data integrity

JET (along with its major application wrapper, Microsoft Access) is often blamed for having poor data integrity, but this is most often because few or none of the data integrity features of the database have been properly used.

The JET database supports many of the standard data integrity functions that are expected of relational databases, including constraints, transactions, and locking. This support is something that has evolved over time, with only JET 4.0 supporting some features.

There are also some locking and buffering problems associated with using JET databases in multi-user environments, and especially so over local area networks (LAN). Such problems may be the subject of another chapter, however.

Constraints

Primary keys

Primary keys help to define a set of columns for a table, that can be used to uniquely identify each row. No two rows can have the same values in the columns that make up the primary key, a constraint that the database enforces by refusing to insert a row when there already exists a row with those values in the primary key columns.

Unique indexes and unique constraints

JET supports both unique indexes and unique constraints, subtly different concepts that achieve essentially the same functionality. A unique index is an index that cannot have duplicate values for the columns in the index, whereas a unique constraint is a data integrity rule that prevents rows being inserted with the same values in the columns listed in the constraint. Both can can used to implement the logical data model concept of an alternate key.

The JET engine implements the concept of the unique constraint by creating a unique index. A unique constraint can be added to a table in the Create Table statement, or via an Alter Table statement.

Foreign keys

JET supports the foreign key constraint, allowing entity-relationship data modelling rules to be enforced at the database level. A foreign key constraint prevents rows from being inserted when no corresponding row exists in a related table, and also prevents rows from being deleted when related tables have dependent rows referencing them.

The JET engine automatically creates an index on the columns that compose the foreign key.

A foreign key constraint must reference all columns in a primary key, unique constraint, or unique index on the referenced table. The code below shows a foreign key referencing a unique constraint (e.g. an alternate key) on another table.

Create Table F3 (
    id int identity(1, 1) not null,
    a int not null, 
    b varchar(20) not null,
    c varchar(20) not null,
    Constraint F3_pk Primary Key (id),
    Constraint F3_uc Unique (a, b)
)
go
Create Table F4 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F4_pk Primary Key (i),
    Constraint F4_fk1 Foreign Key (a, b) References F3 (a, b)
)
go

JET 4.0 introduced cascading updates and deletes to foreign keys. When a foreign key is created with Update Cascade, the foreign keys are updated if the referenced columns are changed. Delete Cascade causes the referencing rows to be deleted if the referenced row is deleted, and Delete Set Null sets the foreign keys to Null if the referenced row is deleted.

Create Table F5 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F5_pk Primary Key (i),
    Constraint F5_fk1 Foreign Key (a, b) References F3 (a, b)
        On Update Cascade On Delete Set Null
)
go

Check constraints

JET 4.0 introduced check constraints, which apply additional logic in the data integrity of the database. A check constraint is an expression that further constrains the allowable values in a column. The expression can be a simple value bounding validation, or it can include a sub-query that references values in other tables.

Check constraints can be useful for more than just validating input values. The following example shows how a check constraint can ensure that a table contains only one row.

Create Table Singleton (
    ID Char(1) Not Null,
    a varchar(20),
    ...
    Constraint Singleton_PK Primary Key (ID),
    Constraint Singleton_One_Row check(ID = 'A')
)
go

Transactions

From JET 4.0, JET supports transactions for multiple statements, giving developers the ability to write robust code that updates the database without compromising logical consistency by, for example, allowing half an invoice to be created, or half a client's records to be updated. Thus, statements within a declared transaction will succeed or fail together.

A transaction must be explicitly created by issuing the Begin Transaction statement. Subsequent statements will not be committed to the database until a Commit or Commit Transaction statement is issued. If a Rollback or Rollback Transaction statement is issued, all statements since the transaction began will fail, i.e. none of them will be committed to the database.

Begin Transaction
go
Insert Into U1 (a, b, c) Values (1, 'First', 'Row')
go
Rollback Transaction
go
Select * From U1
go

(0 row(s) returned)

When using Microsoft's ADO database components, transactions are typically managed through the BeginTrans / CommitTrans / RollbackTrans methods on those objects. However, they can just as easily be implemented through statement execution, as shown above.

Locking

JET supports read and write locks on the database, either through exclusive access, or shared access. Locks can be set at the row level, page level, or database level.

From JET 4.0, Row level locks will be automatically promoted to page or table level when the number of rows locked reaches a threshold. This threshold is set in the Windows registry entry PagesLockedToTableLock found under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

Locking can be configured by setting the isolation level on the database connection. For ADO, this is done with the IsolationLevel property on the Connection object.

Data definition language

Creating and dropping tables

Create Table

Tables are created by issuing the Create Table statement. The statement must specify the table name, and any columns in the table.

Create Table T (
    a integer,
    b char(10)
)

Drop Table

Tables are dropped by issuing the Drop Table statement.

Drop Table T

Alter Table

Tables can be altered by issuing one or more Alter Table statements. New columns can be added, existing columns can be dropped, and existing columns can be altered

Alter Table T Add Column c float
go
Alter Table T Drop Column c
go
Alter Table T Alter Column b varchar(20)
go

Constraints

Primary Keys

There are several ways to create primary keys in JET SQL. One can use the Primary Key directive in the Create Table statement, as shown below:

Create Table P1 (
    i1 int not null,
    c1 varchar(255),
    Primary Key(i1)
)

The same table, with the same primary key, can be created using the Constraint directive, either as part of the Create Table statement:

Create Table P2 (
    i1 int not null,
    c1 varchar(255),
    Constraint PK_P2 Primary Key(i1)
)

or afterwards, in an Alter Table statement:

Create Table P3 (
    i1 int not null,
    c1 varchar(255)
)
go
Alter Table P3 Add Constraint PK_P3 Primary Key(i1)
go

If the table has only one column in its primary key, the constraint can be added to the column specification:

Create Table P4 (
    i1 int not null Constraint PK_P4 Primary Key,
    c1 varchar(255)
)

All but the last example, above, support multiple columns in the primary key, e.g.:

Create Table P5 (
    i1 int not null,
    c1 varchar(20) not null,
    c2 varchar(255),
    Constraint PK_P5 Primary Key(i1, c1)
)

Unique Constraints

Unique constraints can be added in the same way, either in the Create Table statement (shown) or with an Alter Table statement.

Create Table U1 (
    a int not null, 
    b varchar(20) not null, 
    c varchar(20) not null,
    Constraint U1_pk Primary Key (a),
    Constraint U1_uc Unique (b)
)
go

Foreign Key Constraint

A foreign key constraint can be added to a table in the Create Table statement, as show below, or via an Alter Table statement.

Create Table F1 (
    a int not null, 
    b varchar(20) not null,
    c varchar(20) not null,
    Constraint F1_pk Primary Key (a, b)
)
go
Create Table F2 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F2_pk Primary Key (i),
    Constraint F2_fk1 Foreign Key (a, b) References F1 (a, b)
)
go

JET 4.0 introduced cascading updates and deletes to foreign keys. When a foreign key is created with Update Cascade, the foreign keys are updated if the referenced columns are changed. Delete Cascade causes the referencing rows to be deleted if the referenced row is deleted, and Delete Set Null sets the foreign keys to Null if the referenced row is deleted.

Create Table F5 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F5_pk Primary Key (i),
    Constraint F5_fk1 Foreign Key (a, b) References F3 (a, b)
        On Update Cascade On Delete Set Null
)
go

Check Constraints

Check constraints can be added in much the same way. Note that even though a check constraint may pertain to only one specific column, the constraint is declared at the table level, not the column level:

Create Table F6 (
    i int not null,
    a char(1) not null,
    b decimal(15,2) not null,
    c decimal(15,2) not null,
    Constraint F6_pk Primary Key (i),
    Constraint F6_chk_a check (a in ('Y','N')),
    Constraint F6_chk_b check (b >= 0 And b <= 1000),
    Constraint F6_chk_c check (c <= (Select Sum(a) From F5))
)
go

Indexing

Table indexes help to improve the performance of queries made against a table, including implicit queries within other statements such as updates, deletes, and foreign key verification. Table indexes are created by issuing the Create Index statement.

Indexes can be created with values in each column either ascending (ASC) or descending (DESC), meaning least-first or greatest-first. If not specified, an index will be created with ascending values in each indexed column.

The following statements create a table with two indexes. The first index only covers column b, but the second index covers both columns c and d.

Create Table I1 (
    a int not null,
    b varchar(20),
    c varchar(20),
    d varchar(20),
    Constraint I1_pk Primary Key(a)
)
go
Create Index I1_idx1 On Table I1 (b)
go
Create Index I1_idx2 On Table I1 (c ASC, d DESC)
go

Table indexes can be dropped by issuing the Drop Index statement.

Drop Index I1_idx1 On I1

Unique indexes

Normally, an index will allow duplicate values. Where each row must have a unique value in the indexed column, or a unique combination of values in the set of columns being indexed, the index can be specified as being unique. This has a similar effect to adding a unique constraint (and is in fact how JET implements a unique constraint). NB: Nulls are not considered values, so if a column in a unique index or unique constraint is allowed to be Null, then multiple rows may have Null in that column.

Create Table UI1 (
    a int not null, 
    b varchar(20) not null, 
    c varchar(20) not null,
    Constraint UI1_pk Primary Key (a)
)
go
Create Unique Index UI1_idx_ui On UI1 (c)
go

With Disallow Null

Null handling is generally best specified on the table column. However, the Create Index statement also supports an option to disallow any Null values in the indexed columns.

Create Index T5_idx1 On T5(c2) With Disallow Null

With Ignore Null

Rows with Nulls in the indexed columns can also be completely excluded from the index, making the index physically smaller on disc and thus faster to search through.

Create Index T5_idx2 On T5(c1) With Ignore Null

With Primary

The primary key columns of a table can be specified by creating an index with the special With Primary option.

It is generally better to create the primary key with the Primary Key constraint directive, unless other options are required when creating the index on the primary key columns. One such example might be when one or more columns in the primary key should be indexed descending rather than ascending, for performance reasons.

Create Table P6 (
    i1 int not null,
    c1 varchar(20) not null,
    c2 varchar(255)
)
go
Create Index P6_idx_pk On P6(i1 Desc) With Primary
go

Dropping indexes

Dropping an index when it is no longer required is easy too. Specify the index name, and which table the index is on:

Drop Index T5_idx2 On T5
go

Security

When multiple database users, and optionally groups, have been added to the database, restrictions on what those users have access to in the database can be made by granting or revoking privileges on individual objects.

The following basic table privileges from the ANSI SQL standard are supported by JET (the basic "CRUD" privileges – Create, Read, Update, Delete):

Select	select data from the table
Delete	delete data from the table
Insert	insert new data into the table
Update	update existing data in the table

Grant Select on T1 to SalesGroup
go
Grant Select, Insert, Update on T1 to AccountsGroup
go
Revoke Update on T1 from AccountsGroup

In addition, the following table privileges are supported by JET:

All Privileges	grants or revokes all privileges in one hit
Drop	drop the table
SelectSecurity	view permissions on the table (i.e. other Grants)
UpdateSecurity	update permissions on the table
UpdateIdentity	change the values in auto-increment columns
SelectSchema	query the design of the table
Schema	update the design of the table
UpdateOwner	change the owner of the table

Select

Data can be retrieved from tables using the Select statement. The statement takes the following basic form:

Select [{limit-clause}] {column-list}
    [Into {new-tablename}]
    From {table-expression}
    [Where {where-clause}]
    [Group By {group-by-expression}]
    [Having {having-clause}]
    [Order By {order-list}]
    [With OwnerAccess Option]

{limit-clause}	All (default) – select all matching rows Distinct – select matching rows that have unique values for the columns selected DistinctRow – select matching rows that have unique values in all columns accessed, whether selected or not Top n [Percent] – restrict the number of matching rows returned to n, or n% of all matching rows
{column-list}	Either an asterisk (*) to specify that all available columns are returned; or a comma-separated list of expressions that evaluate to output columns, where each expression can be of the form: expression [As display-name] and can be consist of a column from a table or view, a function call, a constant, or a nested expression.
{new-tablename}	The name of a non-existing table into which matching rows will be inserted.
{table-expression}	A list of tables, views, or sub-queries, either comma separated or with explicit join directives. Each table / view / sub-query can have an optional alias, of the form: expression [alias] e.g. From account_details a, contact_details c, invoices i A table may be specified as being in an external database by using the In clause, specifying a Windows path to an external database in which the target table resides. The external database can be another JET database, or any other database that the JET drivers can connect to (include xBase and Paradox databases).
{where-clause}	A set of expressions restricting the rows matched in the tables or views specified in {table-expression}. The where-clause can include multiple expressions separated by logical And, Or and Not operators, and grouped by parentheses (...)
{group-by-expression}	A comma-separated list of expressions that evaluate to the output columns on which to group, when returning aggregated data (e.g. sums, counts)
{having-clause}	A set of expressions restricting the matching rows when returning aggregated data
{order-list}	A list of columns by which to sort the matching rows

Here is a simple select statement that returns all rows in a single table, with every column in that table, and sorted by one column:

Select * From S1 Order By a

In JET SQL, much as in other SQL dialects, operations on tables, views and sub-queries are pretty much identical; for the rest of this section, table can be read as either a physical table, a view, or a sub-query utilised as a virtual table. More will be said about sub-queries and virtual tables later.

Column lists

The simplest {column-list} is the asterisk, which specifies that all available columns from the tables listed in the {table-expression} are returned:

Select * From S1

Individual columns can be selected from a table by specifying a comma-separate list of column names, and each column can be given an alias if desired:

Select a, b as TheSecondColumn From S1

Where there are multiple tables in the {table-expression}, resulting in the potential for two result columns to have the same name, the table name (or an alias for the table) can be used to specify which column to return. The following two statements achieve the same result, with the second statement using table aliases to make the statement a little more concise:

Select Invoice.InvoiceNumber, Invoice.CustomerNumber, Customer.CustomerName
    From Invoice, Customer
    Where Customer.CustomerNumber = 10 And Invoice.InvoiceNumber = 123

Select i.InvoiceNumber, i.CustomerNumber, c.CustomerName
    From Invoice i, Customer c
    Where c.CustomerNumber = 10 And i.InvoiceNumber = 123

In addition to columns from the specified tables, columns in the {column-list} can be expressions. Such an expression can be a mathematical equation, a function call, a string concatenation, a constant, or a mix of these. Here is an example showing how an extended price can be computed from an item price and a quantity, and how a constant can be introduced as a column:

Select ID, (ItemPrice * ItemQuantity) As ExtendedPrice, 'A' As ReportPart
    From OrderItem

Joining tables

Querying data often requires looking in more than one table for an answer, especially when good database design principles have been followed. SQL allows a single Select statement to do this by joining tables.

Joining two or more tables together can be achieved in JET SQL, much the same as in other SQL dialects. Here are some sample tables for examining join syntax in JET SQL:

Table J1 JobName bus driver doctor electrician painter sales clerk

Table J2 WorkerName JobName Mary bus driver Raphael electrician William painter Bruce doctor Juanita doctor

Cartesian join

A Cartesian join, sometimes called a cross join, is where each row in the first table is joined with each row in the second table. This type of join can be useful when generating a list of all possible combinations from two or three categories. The syntax for a Cartesian join is the simplest join syntax; just list the tables in the {table-expression} separated by a comma:

Select J1.JobName, J2.WorkerName From J1, J2

JobName              WorkerName
-------------------- --------------------
bus driver           Mary
doctor               Mary
electrician          Mary
painter              Mary
sales clerk          Mary
bus driver           Raphael
doctor               Raphael
electrician          Raphael
painter              Raphael
sales clerk          Raphael
bus driver           William
doctor               William
electrician          William
painter              William
sales clerk          William
bus driver           Bruce
doctor               Bruce
electrician          Bruce
painter              Bruce
sales clerk          Bruce
bus driver           Juanita
doctor               Juanita
electrician          Juanita
painter              Juanita
sales clerk          Juanita
(25 row(s) returned)

Inner join

There are two ways to achieve an inner join, where each row in the first table is joined to one or more rows in the second table by an expression. The first way is an extension of the Cartesian join, above, with a {where-clause} expression:

Select J1.JobName, J2.WorkerName From J1, J2
    Where J1.JobName = J2.JobName

Alternatively, an inner join expression can be given in the {table-expression}. This is no different in a practical sense to the previous example, but some people find that it more clearly shows the type of join and its constraints, as distinct from expressions used as selectors or filters in a {where-clause}:

Select J1.JobName, J2.WorkerName
    From J1
    Inner Join J2 On J1.JobName = J2.JobName

JobName              WorkerName
-------------------- --------------------
bus driver           Mary
electrician          Raphael
painter              William
doctor               Bruce
doctor               Juanita
(5 row(s) returned)

Outer join

Joining two tables such that the first table is joined to no rows, or one or more rows, from the second table, requires a left outer join. Another way of looking at this join is that all rows selected from the first table are returned, whether or not there are rows from the second table to join to; an inner join only returns rows from the first table that can be joined to rows in the second table. In JET SQL, the left outer join requires a LEFT JOIN statement in the {table-expression}, with the details of the join specified after the ON part of the statement:

Select J1.JobName, J2.WorkerName
    From J1
    Left Join J2 On J1.JobName = J2.JobName

JobName              WorkerName
-------------------- --------------------
bus driver           Mary
doctor               Juanita
doctor               Bruce
electrician          Raphael
painter              William
sales clerk          <NULL>
(6 row(s) returned)

The converse of a left outer join, where the second table is joined to no rows, or one or more rows, from the first table, is a right outer join. In JET SQL, the right outer join requires a RIGHT JOIN statement in the {table-expression}:

Select J1.JobName, J2.WorkerName
    From J1
    Right Join J2 On J1.JobName = J2.JobName

Multiple joins

When a Select statement joins more than two tables, each pair must be nested within parentheses to group the joining tables together:

Select *
    From ((J1 Left Join J2 On J1.JobName = J2.JobName)
    Inner Join J3 On J2.WorkerName = J3.WorkerName)
    Left Join J4 On J3.x = J4.x

Limit clause

The {limit-clause} restricts the quantity of data returned by a query, and whether or not duplicate rows are returned. If it is not specified, then the query will return everything that matches the selection criteria, duplicates and all. This is the same as saying Select All.

Table L1 ID FullName JobTitle 1 Joe Bloggs painter 2 Milly Jones doctor 3 Robert Green electrician 4 Joe Bloggs author 5 Lee Wong accountant 6 Joe Bloggs painter 7 Lee Wong plumber

Table L2 AccountNumber ID 123456789 1 987654321 2 134679258 3 976431852 4 456789123 4

Distinct

Distinct removes any rows with the same values from the result set. Without Distinct, there would be three rows for the name Joe Bloggs with an account, but with Distinct there will only be one Joe Bloggs row returned by the following statement:

Select Distinct FullName
    From L1 Inner Join L2 On L1.ID = L2.ID

FullName
--------------------
Joe Bloggs
Milly Jones
Robert Green
(3 row(s) returned)

DistinctRow

DistinctRow removes any duplicated table rows from the result set, taking into consideration all columns from tables that have columns in the {column-list} – subtly different to Distinct which only takes columns in the {column-list} into consideration.

Select DistinctRow FullName
    From L1 Inner Join L2 On L1.ID = L2.ID

FullName
--------------------
Joe Bloggs
Milly Jones
Robert Green
Joe Bloggs
(4 row(s) returned)

DistinctRow is ignored when there is only one table selected from.

Top n

Top n is used to limit the number of rows returned, to either a specific number of rows, or to a percentage of the complete result set.

Select Top 4 * From L1

ID          FullName             JobTitle
----------- -------------------- --------------------
1           Joe Bloggs           painter
2           Milly Jones          doctor
3           Robert Green         electrician
4           Joe Bloggs           author
(4 row(s) returned)

Select Top 40 Percent * From L1

ID          FullName             JobTitle
----------- -------------------- --------------------
1           Joe Bloggs           painter
2           Milly Jones          doctor
3           Robert Green         electrician
(3 row(s) returned)

Insert

Data can be added to tables using the Insert statement. The statement takes the following basic form:

Insert Into {tablename}
    [In {external-db-path}]
    [{column-list}]
    [{select-statement} | {values-list}]

{tablename}	The name of an existing table into which rows will be inserted.
{external-db-path}	A Windows path to an external database in which the target table resides. The external database can be another JET database, or any other database that the JET drivers can connect to (include dBase and Paradox databases).
{column-list}	A comma-separated list of columns within a pair of parentheses, specifying which columns in the target table will be populated by the Insert statement. If omitted, then all columns will be populated by the Insert statement. Any column not specified by the {column-list} will be set to NULL, unless the column has a default value set.
{select-statement}	A Select statement that returns the same number of columns as the {column-list}, or the number of columns in the target table if the {column-list} is omitted.
{values-list}	A comma-separated list of expressions within a pair of parentheses, providing the same number of values as there are columns in the {column-list}, or the number of columns in the target table if the {column-list} is omitted.

Manipulation tools

There is a variety of different tools available for viewing and changing JET databases. Here are some of the more commonly used tools.

Microsoft Access

Microsoft Access (now Microsoft Office Access) is a member of the Microsoft Office suite of products, and was developed by Microsoft in tandem with the JET database. Its support for JET is comprehensive, offering graphical tools for designing tables, building queries, and viewing and editing data. Beyond that, it can also be used to build complete Microsoft Access applications.

Given that Microsoft Access is so tightly bound to JET, one might wonder why anyone would use any other tool. Here are a few reasons:

It costs money.

Microsoft Access can be bought stand-alone, or as part of one of the Microsoft Office packages, but for anyone who doesn't need either of those for any other reason, it's an additional cost.

It won't run scripts.

Many software developers prefer to manipulate database through scripts, text files with batches of statements that can be executed to perform aggregate tasks. Scripts can be easily integrated into a version control / configuration management system, and applied to development, test and production databases to repeat a tested scenario exactly the same in each environment. To do this in Microsoft Access, the developer needs to convert such scripts into Visual Basic for Applications (VBA) code that separates each statement into a separate execution unit.

It's another tool to learn.

Every tool needs the developer to learn something new, but Microsoft Access doesn't allow simple database scripts to be executed, so there is more learning in line for the developer.

Some alternatives are discussed below.

Visual Studio

Microsoft Visual Studio has had database manipulation tools since version 6. It provides tools for inspecting the various database objects including tables, views, and procedures, viewing and editing data in tables, and running scripts of SQL statements.

To use Visual Studio for working with a JET database, create a database project and point it at your .mdb file. In Visual Studio 2002 and later, database projects can be created as part of any solution (e.g. C#, VB.NET), or by themselves. In version 6, database projects are part of Visual Interdev, the web application development tool.

Visual Studio can be integrated with several popular version control / configuration management packages, and comes with a basic one called Visual SourceSafe.

Visual Studio allows scripts to execute against the JET database, with multiple SQL statements in a single file. To batch multiple JET SQL statements together, separate each with the statement go on a line by itself, like this:

Create Table B1
(
    ID integer not null,
    B1Desc varchar(50) with compression,
    Constraint B1_pk Primary Key(ID)
)
go
Insert Into B1(ID, B1Desc) Values (1, 'alpha')
go
Insert Into B1(ID, B1Desc) Values (2, 'beta')
go

NB: take care not to end the script with a line that only has spaces on it, because the script processor will return an error when it tries to execute the (blank) line.

JetSQLConsole

JetSQLConsole is a command line tool for manipulating JET databases. It is similar to mysqlclient, sqlcmd, and psql.

JetSQLConsole allows scripts of multiple SQL statements to be executed against a JET database as a single batch. Each statement must be terminated with a semicolon, ";", like this:

Create Table B2
(
    ID integer not null,
    B2Desc varchar(50) with compression,
    Constraint B2_pk Primary Key(ID)
);
Insert Into B2(ID, B2Desc) Values (1, 'alpha');
Insert Into B2(ID, B2Desc) Values (2, 'beta');

PlaneDisaster.NET

PlaneDisaster.NET is a GUI SQL editor. The curious name derives from the name of the database engine (JET → Plane) and its reputation for being unreliable (partly due to architectural problems, and partly from novice users ignoring data integrity issues).

In addition to allowing SQL scripts to be executed, PlaneDisaster.NET can:

• create a new JET database
• execute SQL statements "on the fly"
• view the contents of a table in a data grid
• generate SQL scripts for some database objects
• generate INSERT scripts for populating a table
• compact and repair a JET database
• also manipulate SQLite databases

Jet compact utility

Jet compact utility, JETCOMP.exe is a Microsoft utility that compacts databases created with Jet engine versions 3.x and 4.x.

CUTE

A screenshot of CUTE

Cooled Universal Table Editor is a free VBScript based ASP open source software application intended for editing databases (structure and data) on-line (web front-ended). Currently supports full Microsoft SQL Server and Microsoft Access databases edition, and the creation of them, using a web browser, like Mozilla Firefox or Internet Explorer. This are its features in detail:

NOTE: This tool is no longer available for download.

• Full data edition for Microsoft Access and SQL Server databases.
• Ability to create new Microsoft Access and Microsoft SQL Server databases.
• Ability to create and edit tables and table views.
• Table structure viewer and editor.
• Multi-database management workspace. You can point directly to a Microsoft Access file (full path with *.mdb extension), a folder containing Microsoft Access database files or specify multiple DSN's. All your databases (in a hosting, for example) can be easily accessed this way.
• SQL command line system. You can execute any SQL query at two different levels: database and table level. You can access SQL commands in the same session with the embedded SQL history and quickly perform the most usual SQL actions using the SQL template selector.
• Data filters: edit and display your own personalized views to navigate and edit records easily.
• CSV export: tables can be exported using Comma Separated Values, to applications that support CSV like, Microsoft Excel.
• Microsoft Word export: tables can be exported using Microsoft Word format, to applications like Microsoft Word, but too to OpenOffice, or HTML/browsers editors.
• I18N (internationalization). CUTE has corrected intensively and extensively UTE's issues with various characters in databases/tables/fields that made UTE non functional (like spaces in table names, for example).
• User login to prevent unauthorized access.

Its roots are in older UTE, Universal Table Editor.

YouAccess

YouAccess is a free (public domain license) console application intended for SQL management of Microsoft® Access™ databases from command line. Is written in VBScript and uses ADO and DAO internally for database creation and SQL execution.

MDB Tools

MDB Tools is an open source project for documenting and reading JET databases. It supports JET 3 (Access 97) and JET 4 (Access 2000/2002) engines. The tools allow a user to list tables, generate table schema statements, and extract data as CSV and SQL insert statements. Several popular Linux distributions provide MDB tools through their package managers (look for mdbtools), and source for compiling the tools can be downloaded from SourceForge. A simple GUI explorer / query tool is also provided (see screenshot (look for mdbtools-gui in Linux package managers).

JaSS

JaSS (JET and SQL Server) Database Manager is an open source Web Application intended to manage a Microsoft® Access™ JET database.

The current version will:

• List existing JET database (.mdb) files on the file system and create new JET databases.
• List tables and views on a JET database and create new and drop existing tables. There is currently no ability to manage views.
• List columns on a JET database table and create new and drop existing columns.
• List indexes on a JET database table and create new and drop existing indexes.
• List constraints on a JET database table and create new and drop existing constraints.
• Query data on a JET database table. The current version does not allow the insertion, modification or deletion data.
• Run any arbitrary SQL against a JET database. This may be used to query data or to execute DML or DDL statements.

A future release may be extended to allow SQL Server databases to be managed.

Contributors

Individuals

• User:Webaware - NSW, Australia