JET Database/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.