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|
Creating from Access
By far, the easiest way to create a new JET database is to use Microsoft Access.
- Start Microsoft Access
- From the menu, select
File → Newor press
- Select "Blank database"
- 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 ADOX.Catalog Dim create_string As String 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 cat = CreateObject("ADOX.Catalog") cat.Create create_string
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.