By: David Intersimone
Abstract: Scott Strool, of Inprise Corporation, shows how you can use Delphi 5's new ADO (ActiveX Data Objects) Express to access a Microsoft Excel spreadsheet.
by Scott Strool
Delphi 5 now provides choices in how you can access your corporate data.
Along with the Borland Database Engine and InterBase Express, you now can
use a third data access model named ADOExpress. With ADOExpress you can now
develop systems that access data in places where you might not have thought
possible. ADOExpress is available in the Enterprise version of Delphi 5 and
can be added to Delphi 5 Professional.
ADO is Microsoft's high-level interface to all kinds of data. This
application-level interface to Microsoft's data access technology is
called OLE DB. OLE DB provides fast access to any data source, including
relational and non-relational databases, email and file systems, text
and graphics, and custom business objects.
With Delphi's ADOExpress comes the power to access data that previously
required lots of coding or the purchase of third party components. This
article is a tutorial for using ADOExpress to provide an interface for
Microsoft Excel worksheets. Using the ADOExpress components, you can
build an application that can use an Excel file as the database. The
columns of the spreadsheet become the fields and the rows, well they
are still rows.
Using ADOExpress is similar to using the Data Access components that
have been part of Delphi since 1.0. The three primary components used
to access anything from ADO are the Connection, Command and DataSet
components. These are installed on the ADO component tab.
1. Start Delphi and create a new application.
2. Drop an ADODataSet onto the form.
3. Set the CommandType property to cmdTableDirect
An ADODataSet is similar to the BDEDataSet used to access database
tables. With ADO, you describe the data source and then use data-aware
controls to display the data in your application. The first property
to set is the CommandType. This property tells the ADO engine what
type of command you are going to be calling.
4. Set the ConnectionString property.
The ConnectionString property tells ADO how to connect to the
datasource. Click the ellipse to display the ConnectionString dialog.
We are going to build the string ourselves so select Use Connection
String and click the Build button to display the Data Link Properties
dialog. The first choice to make is what data provider are we going
to use to get the data. To access Excel we use the Microsoft Jet
OLEDB 4.0 Provider, select it and click Next. The next piece is what
are we going to connect to. In this case we want to access an Excel
file. Use the Browse button and find an Excel file on your system or
use Forecast.xls provided in the ADOExcel.zip file.
Before we can test the connection we need to enter an
Extended Property to the connection string. Click OK to go back the
ConnectionString dialog and type 'Extended Properties=Excel 8.0;' without
the quotes at the end of the connection string (remember, all ConnectionString
entries must be separated by semi-colons. This tells ADO that we
need Excel support and use the type library associated with Excel 8.0.
Go ahead and click Build again and then Test the connection. If you
get Test Connection Succeeded, you did everything right. Close the
ConnectionString dialog. The last property to set is the CommandText
5. Set the CommandText property.
This property now becomes a combobox that allows you to select the
tables defined in the datasource. Choose the name of the spreadsheet
with a $ appended to it.
6. Drop a Datasource component from the Data Access tab and a DBGrid
from the Data Controls tab.
Now we want to hook up the ADODataset to a grid for display. Connect the Grid to
the datasource by setting the DataSource property to DataSource1 and connect
the DataSource to the ADODataset by setting the DataSet property to ADODataSet1.
7. Set the Active property of the ADODataSet to True.
We can now test the query in design mode by activating the ADODataSet. The data
from the spreadsheet will be displayed in the grid.
In a future article we will investigate the use of the other ADO components and
how ADO can help you with Internet applications.
ADO technology in Delphi is very new. The best way to learn it is to just play
with it and have fun.
Try Delphi XE4 free for 30 days
New Instant Trial!
Webinars on demand!
More social media choices:
Delphi on Google+
@RADTools on Twitter
Server Response from: ETNASC02