D
doxpix
Unregistered / Unconfirmed
GUEST, unregistred user!
Importing and Exporting
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 start with an example of exporting data from the Northwind
Access database to a Paradox table. You will need an active TADOConnection, called ADOConnection1
in our example, that uses the Jet 4.0 OLE DB Provider to open the Northwind.
mdb Access database. The following code exports the Customers table to a Paradox
Customers.db file:
ADOConnection1.Execute(‘SELECT * INTO Customers ‘ +
‘IN “C:/Temp” “Paradox 7.x;” FROM CUSTOMERS’);
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.
All export statements follow these same basic clauses, but you will find that some IISAM
drivers have differing interpretations of what a database is. I’ll do
another couple of examples
to demonstrate the differences. Here, we export the same data to Excel:
ADOConnection1.Execute(‘SELECT * INTO Customers ‘ +
‘IN “Northwind.xls” “Excel 8.0;” FROM CUSTOMERS’);
A new Excel file called Northwind.xls is created in the application’s current directory. A
workbook called Customers is added, containing all of the data of the Customers table in
Northwind.mdb. You can also export data to Excel by automating Excel, but if you have ever
done this you will know that this ADO solution is simpler by far.
This next example exports the same data to HTML:
ADOConnection1.Execute(‘SELECT * INTO [Customers.htm] ‘ +
‘IN “C:/Temp” “HTML Export;” FROM CUSTOMERS’);
In this example, 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 sister to
HTML Export: HTML Import. Add a TADOTable to a form, set its ConnectionString to
use the Jet 4.0 OLE DB Provider and 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,
C:/Temp/Customers.htm. Close the connection string editors and set the TableName to
Customers. Open the table and you have just imported the HTML file! Bear in mind,
though, that the name of this IISAM driver is “HTML Import”, not just "HTML". If you
attempt to update the data in any way, 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.
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 start with an example of exporting data from the Northwind
Access database to a Paradox table. You will need an active TADOConnection, called ADOConnection1
in our example, that uses the Jet 4.0 OLE DB Provider to open the Northwind.
mdb Access database. The following code exports the Customers table to a Paradox
Customers.db file:
ADOConnection1.Execute(‘SELECT * INTO Customers ‘ +
‘IN “C:/Temp” “Paradox 7.x;” FROM CUSTOMERS’);
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.
All export statements follow these same basic clauses, but you will find that some IISAM
drivers have differing interpretations of what a database is. I’ll do
another couple of examples
to demonstrate the differences. Here, we export the same data to Excel:
ADOConnection1.Execute(‘SELECT * INTO Customers ‘ +
‘IN “Northwind.xls” “Excel 8.0;” FROM CUSTOMERS’);
A new Excel file called Northwind.xls is created in the application’s current directory. A
workbook called Customers is added, containing all of the data of the Customers table in
Northwind.mdb. You can also export data to Excel by automating Excel, but if you have ever
done this you will know that this ADO solution is simpler by far.
This next example exports the same data to HTML:
ADOConnection1.Execute(‘SELECT * INTO [Customers.htm] ‘ +
‘IN “C:/Temp” “HTML Export;” FROM CUSTOMERS’);
In this example, 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 sister to
HTML Export: HTML Import. Add a TADOTable to a form, set its ConnectionString to
use the Jet 4.0 OLE DB Provider and 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,
C:/Temp/Customers.htm. Close the connection string editors and set the TableName to
Customers. Open the table and you have just imported the HTML file! Bear in mind,
though, that the name of this IISAM driver is “HTML Import”, not just "HTML". If you
attempt to update the data in any way, 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.
]