JET Database/Introduction

JET Database

Introduction · Creating and connecting · Data types · Object names and constants · Data integrity · Security · Data definition language · Select · Insert · Update · Delete · Functions · Views and stored procedures · Manipulation tools · Integration and migration · Design tools

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.