Now that you have some of the MDAC and ADO basics under your belt, let's take a moment out to look at the Jet engine. This engine is of great interest to some and of no interest to others. If you're interested in Access, Paradox, dBase, text, Excel, Lotus 1-2-3, or HTML, then this section is for you. If you have no interest in any of these formats, you can safely skip this section.
The Jet database engine is usually associated with Microsoft Access databases, and this is its forte. However, the Jet engine is also an all-purpose desktop database engine, and this lesser-known attribute is where much of its strength lies. Because using the Jet engine with Access is its default mode and is straightforward, this section mostly covers use of non-Access formats, which are not so obvious.
The Jet engine has been included in some (but not all) versions of MDAC. It is not included in version 2.6. There has been a long debate about whether programmers using a non-Microsoft development tool have the right to distribute the Jet engine. The official answer is positive, and the Jet engine is available as a free download (in addition to beingdistributed with many Microsoft software products).
There are two Jet OLE DB providers: the Jet 3.51 OLE DB provider and the Jet 4.0 OLE DB provider. The Jet 3.51 OLE DB provider uses the Jet 3.51 engine and supports Access 97 databases only. If you intend to use Access 97 and not Access 2000, then you will get better performance using this OLE DB provider in most situations than using the Jet 4.0 OLE DB provider.
The Jet 4.0 OLE DB provider supports Access 97, Access 2000, and Installable Indexed Sequential Access Method (IISAM) drivers. Installable ISAM drivers are those written specifically for the Jet engine to support access to ISAM formats such as Paradox, dBase, and text, and it is this facility that makes the Jet engine so useful and versatile. The complete list of ISAM drivers installed on your machine depends on what software you have installed. You can find this list by looking in the Registry at
However, the Jet engine includes drivers for Paradox, dBase, Excel, text, and HTML.
The Jet engine expects to be used with Access databases. To use it with any database other than Access, you need to tell it which IISAM driver to use. This is a painless process that involves setting the Extended Properties connection string argument in the connection string editor. Let's work through a quick example.
Add an ADOTable component to a form and invoke the connection string editor. Select the Jet 4.0 OLE DB Provider. Select the All page, locate the Extended Properties property, and double-click it to edit its value.
Enter Paradox 7.x in the Property Value, as illustrated in Figure 15.4, and click OK. Now go back to the Connection tab and enter the name of the directory containing the Paradox tables directly, because the Browse button won't help you (it lets you enter a filename, not a folder name). At this point you can select a table in the ADOTable's TableName and open it either at design time or at run time. You are now using Paradox through ADO, as demonstrated by the JetParadox example.
I have some bad news for Paradox users: Under certain circumstances, you will need to install the BDE in addition to the Jet engine. Jet 4.0 requires the BDE in order to be able to update Paradox tables, but it doesn't require the BDE just to read them. The same is true for most releases of the Paradox ODBC Driver. Microsoft has received justified criticism about this point and has made a new Paradox IISAM available that does not require the BDE; you can get these updated drivers from Microsoft Technical Support.
As you learn more about ADO, you will discover how much it depends on the OLE DB provider and the RDBMS (relational database management system) in question. Although you can use ADO with a local file format, as demonstrated in this and following examples, the general suggestion is to install a local SQL engine whenever possible. Access and MSDE are good choices if you have to use ADO; otherwise you might want to consider InterBase or Firebird as alternatives, as discussed in Chapter 14.
Excel is easily accessed using the Jet OLE DB provider. Once again, you set the Extended Properties property to Excel 8.0. Assume that you have an Excel spreadsheet called ABCCompany.xls with a sheet called Employees, and you want to open and read this file using Delphi. With a little knowledge of COM, you can do so by automating Excel. However, the ADO solution is considerably easier to implement and doesn't require Excel to be available on the computer.
You can also read an Excel file using the XLSReadWrite component (available from www.axolot.com). It doesn't require Excel to be available on the computer or the time to start it (like OLE Automation techniques do).
Ensure that your spreadsheet is not open in Excel, because ADO requires exclusive access to the file. Add an ADODataSet component to a form. Set its ConnectionString to use the Jet 4.0 OLE DB provider and set Extended Properties to Excel 8.0. In the Connection tab, set the database name to the full file and path specification of the Excel spreadsheet (or use a relative path if you plan to deploy the file along with the program).
The ADODataSet component works by opening or executing a value in its CommandText property. This value might be the name of a table, a SQL statement, a stored procedure, or the name of a file. You specify how this value is interpreted by setting the CommandType property. Set CommandType to cmdTableDirect to indicate that the value in CommandText is the name of a table and that all columns should be returned from this table. Select CommandText in the Object Inspector, and you will see a drop-down arrow. Drop down the arrow and a single pseudo-table will be displayed: Employees$. (Excel workbooks are suffixed with a $.)
Add a DataSource and a DBGrid and connect them altogether, and you'll obtain the output of the JetExcel example, shown in Figure 15.5 at design time. By default it would be a little difficult to view the data in the grid, because each column has a width of 255 characters. You can change the field display size either by adding columns to the grid and changing their Width properties or by adding persistent fields and changing their Size or DisplayWidth properties.
Notice that you cannot keep the dataset open at design time and run the program, because the Excel IISAM driver opens the XLS file is in exclusive mode. Close the dataset and add to the program a line of code to open it at startup. When you run the program, you will notice another limitation of this IISAM driver: You can add new rows and edit existing rows, but you cannot delete rows.
Incidentally, you could have used either an ADOTable or an ADOQuery component, instead of the ADODataSet, but you need to be aware of how ADO treats symbols in things like table names and field names. If you use an ADOTable and drop down the list of tables, you will see the Employees$ table as you expect. Unfortunately, if you attempt to open the table, you will receive an error. The same is true for SELECT * FROM Employees$ in a TADOQuery. The problem lies with the dollar sign in the table name. If you use characters such as dollar signs, dots, or, more importantly, spaces in a table name or field name, then you must enclose the name in square brackets (for example, [Employees$]).
One of the most useful IISAM drivers that comes with the Jet engine is the Text IISAM. This driver allows you to read and update text files of almost any structured format. We will begin with a simple text file and then cover the variations.
Assume you have a text file called NightShift.TXT that contains the following text:
CrewPerson ,HomeTown Neo ,Cincinnati Trinity ,London Morpheus ,Milan
Add an ADOTable component to a form, set its ConnectionString to use the Jet 4.0 OLE DB provider, and set Extended Properties to Text. The Text IISAM provider considers a directory a database, so you need to enter as the database name the directory that contains the NightShift.TXT file. In the Object Inspector and drop down the list of tables in the TableName property. You will notice that the dot in the filename has been converted to a hash, as in NightShift#TXT. Set Active to True, add a DataSource and a DBGrid and connect them altogether, and you will see the contents of the text file in a grid.
If your computer's settings are such that the decimal separator is a comma instead of a period (so that 1,000.00 is displayed as 1.000,00), then you will need to either change your Regional Settings (Start ® Settings ® Control Panel ® Regional Settings ® Numbers) or take advantage of SCHEMA.INI, described shortly.
The grid indicates that the widths of the columns are 255 characters. You can change these values just as you did in the JetExcel program by adding persistent fields or columns to the grid and then setting the relevant width property. Alternatively, you can define the structure of the text file more specifically using SCHEMA.INI.
In the JetText example, the database folder is determined at run time depending on the folder hosting the program. To modify the connection string at run time, first load it into a string list (after converting the separators) and then use the Values property to change only one of the elements of the connection string. This is the code from the example:
procedure TForm1.FormCreate(Sender: TObject); var sl: TStringList; begin sl := TStringList.Create; sl.Text := StringReplace (ADOTable1.ConnectionString, ';', sLineBreak, [rfReplaceAll]); sl.Values ['Data Source'] := ExtractFilePath (Application.ExeName); ADOTable1.ConnectionString := StringReplace (sl.Text, sLineBreak, ';', [rfReplaceAll]); ADOTable1.Open; sl.Free; end;
Text files come in all shapes and sizes. Often you do not need to worry about the format of a text file because the Text IISAM takes a peek at the first 25 rows to see whether it can determine the format for itself. It uses this information and additional information in the Registry to decide how to interpret the file and how to behave. If you have a file that doesn't match a regular format the Text IISAM can determine, then you can provide this information using a SCHEMA.INI file located in the same directory as the text files to which it refers. This file contains schema information, also called metadata, about any or all of the text files in the same directory. Each text file is given its own section, identified by the name of the text file, such as [NightShift.TXT].
Thereafter you can specify the format of the file; the names, types, and sizes of columns; any special character sets to use; and any special column formats (such as date/time or currency). Let's assume that you change your NightShift.TXT file to the following format:
Neo |Cincinnati Trinity |London Morpheus |Milan
In this example, the column names are not included in the text file, and the delimiter is a vertical bar. An associated SCHEMA.INI file might look something like the following:
[NightShift.TXT] Format=Delimited(|) ColNameHeader=False Col1=CrewPerson Char Width 10 Col2=HomeTown Char Width 30
Regardless of whether you use a SCHEMA.INI file, you will encounter two limitations of the Text IISAM: Rows cannot be deleted, and rows cannot be edited.
The Jet engine is particularly adept at importing and exporting data. The process of exporting data is the same for each export format and consists of executing a SELECT statement with a special syntax. Let's begin with an example of exporting data from the Access version of the DBDemos database back to a Paradox table. You will need an active ADOConnection, called ADOConnection1 in the JetImportExport example, which uses the Jet engine to open the database. The following code exports the Customer table to a Paradox Customer.db file:
SELECT * INTO Customer IN "C:\tmp" "Paradox 7.x;" FROM CUSTOMER
Let's look at the pieces of this SELECT statement. The INTO clause specifies the new table that will be created by the SELECT statement; this table must not already exist. The IN clause specifies the database to which the new table is added; in Paradox, this is a directory that already exists. The clause immediately following the database is the name of the IISAM driver to be used to perform the export. You must include the trailing semicolon at the end of the driver name. The FROM clause is a regular part of any SELECT statement. In the sample program, the operation is executed through the ADOConnection component and uses the program's folder instead of a fixed one:
ADOConnection1.Execute ('SELECT * INTO Customer IN "' + CurrentFolder + '" "Paradox 7.x;" FROM CUSTOMER');
All export statements follow these same basic clauses, although IISAM drivers have differing interpretations of what a database is. Here, you export the same data to Excel:
ADOConnection1.Execute ('SELECT * INTO Customer IN "' + CurrentFolder + 'dbdemos.xls" "Excel 8.0;" FROM CUSTOMER');
A new Excel file called dbdemos.xls is created in the application's current directory. A workbook called Customer is added, containing all the data from the Customer table in dbdemos.mdb.
This last example exports the same data to an HTML file:
ADOConnection1.Execute ('SELECT * INTO [Customer.htm] IN "' + CurrentFolder + '" "HTML Export;" FROM CUSTOMER');
In this case, the database is the directory, as it was for Paradox but not for Excel. The table name must include the .htm extension and, therefore, it must be enclosed in square brackets. Notice that the name of the IISAM driver is HTML Export, not just HTML, because this driver can only be used for exporting to HTML.
The last IISAM driver we'll look at in this investigation of the Jet engine is the partner to HTML Export: HTML Import. Add an ADOTable to a form, set its ConnectionString to use the Jet 4.0 OLE DB provider, and set Extended Properties to HTML Import. Set the database name to the name of the HTML file created by the export a few moments ago—that is, Customer.htm. Now set the TableName property to Customer. Open the table—you have just imported the HTML file. Bear in mind, though, that if you attempt to update the data, you'll receive an error because this driver is intended for import only. Finally, if you create your own HTML files containing tables and want to open these tables using this driver, then remember that the name of the table is the value of the caption tag of the HTML table.