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.

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   or press
 * 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:

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