# JET Database/Print version

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

# 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.
* Start Microsoft Access
* From the menu, select File → New or press Control-N
* Select "Blank database"
* 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:

## 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:

## 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:
The following Visual Basic code demonstrates connecting to a JET database from ADO, using a database-level password:
The following Visual Basic code demonstrates connecting to a JET database with System database from ADO, using a username and 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:

### 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:

### 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:

### 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:

## 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$^{28}$-1 to 10$^{28}$-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.

## 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.

## 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:

## 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.

### 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).
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.
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.

### 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.

## 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.
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.

### Drop Table

Tables are dropped by issuing the Drop Table statement.

### 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

## 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:
The same table, with the same primary key, can be created using the Constraint directive, either as part of the Create Table statement:
or afterwards, in an Alter Table statement:
If the table has only one column in its primary key, the constraint can be added to the column specification:
All but the last example, above, support multiple columns in the primary key, e.g.:

### Unique Constraints

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

### 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.
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.

### 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:

## 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.
Table indexes can be dropped by issuing the Drop Index statement.

### 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.

### 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.

### 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.

### 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.

### 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:

## 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 |
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:
| {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:
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:
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:
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:
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:

## 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:

### 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:
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}:

### 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:
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}:

### Multiple joins

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

## 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:

### 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.
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.

# Insert

Data can be added to tables using the Insert statement. The statement takes the following basic form:
| {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:
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:

## 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

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