JET Database/Creating and connecting
Creating a JET database
editThere 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
editBy far, the easiest way to create a new JET database is to use Microsoft Access.
- Start Microsoft Access
- From the menu, select
File → New
or pressControl-N
- 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
editAn 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
editVisual 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
editJET 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
editThe 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
editThe 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
editConnecting from ADO
editWhen 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
editJET 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.