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.

1. Start Microsoft Access
2. From the menu, select File → New or press Control-N
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:

Dim cat As New ADOX.Catalog
Dim create_string As String
Dim conn As ADODB.Connection

create_string = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;"
create_string = create_string & "Data Source=" & path_to_database & ";"

If JET_encryption_wanted Then
    create_string = create_string & "Jet OLEDB:Encrypt Database=True;"
End If

Set conn = cat.Create(create_string)

conn.Close()

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:

' declare Windows API functions
Declare Function MoveFileEx Lib "kernel32" Alias "MoveFileExA" _
    (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
    ByVal dwFlags As Long) As Long

' build connection strings for database and temporary file
Dim cx as string
Dim src_cx As String
Dim tgt_cx As String

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;"

If uses_database_password Then
    cx = cx & "Jet OLEDB:Database Password=" & database_password
ElseIf uses_username_password Then
    cx = cx & "User ID=" & user_id & ";Password=" & user_password & ";"
End If

src_cx = cx & "Data Source=" & src_file & ";"
tgt_cx = cx & "Data Source=" & tmp_file & ";"

' run Jet Replication Objects to compact the database to a temporary file
Dim compacter As New JRO.JetEngine
compacter.CompactDatabase src_cx, tgt_cx
Set compacter = Nothing
    
' replace the old database with the compacted database, under the same name
Dim scode As Long
scode = MoveFileEx(tmp_file, src_file, MOVEFILE_REPLACE_EXISTING + MOVEFILE_COPY_ALLOWED)

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:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
Set db = CreateObject("ADODB.Connection")
db.Open cx, user_id, user_password

The following Visual Basic code demonstrates connecting to a JET database from ADO, using a database-level password:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
cx = cx & "Jet OLEDB:Database Password=" & database_password
Set db = CreateObject("ADODB.Connection")
db.Open cx

The following Visual Basic code demonstrates connecting to a JET database with System database from ADO, using a username and password:

Dim cx As String
Dim db As ADODB.Connection

cx = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database_filename & ";"
cx = cx & "Jet OLEDB:System Database=" & system_db_filename & ";"
Set db = CreateObject("ADODB.Connection")
db.Open cx, user_id, user_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.