JET Database/Manipulation tools

From Wikibooks, the open-content textbooks collection

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

[edit] 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.

[edit] 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.

[edit] 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');

[edit] 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

[edit] 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.

[edit] CUTE

Cooled Universal ASP Table Editor is a VBScript based ASP open source software intended for editing database, table structure and data online, via http/web. Currently supports table and database views, full data edition, an SQL command line, creation of JET databases in *.mdb files and preferences for accessing MDB JET databases via DSN or filesystem paths. Its roots are in older UTE, Universal Table Editor.