Database Connectivity - The ZEOS Library

The ZEOS Library

the developers are inteneded to

copy the functions and the behaviour of the corresponding BDE components as good

as possible.The intension is to minimize the learning courve for developers who
migrate from BDE

consists of the following nine components

which shall be introduced in the following:

•

TZConnection

•

TZQuery

•

TZReadOnlyQuery

•

TZUpdateSQL

•

TZTable

•

TZStoredProc

•

TZSQLProcessor

•

TZSQLMonitor• TZSQLMetadata
Installing the ZEOS Library

Open the delphi project group

ZeosDbo.bpg from subdirectory packages\delphi7 ZeosDbo.bpg and install

the following components in given order:

•

ZCore.bpl

•

ZParseSql.bpl

•

ZPlain.bpl

•

ZDbc.bpl• ZComponent.bpl
Note:

add the subdirectory packages\delphi7\build to delphis library path. All dcu files that are created whilecompilation are located here.

Attention:

The client library of Firebird Server version 1.5.1 (not embedded!) was delivered as "gds32.dll"

and not "fbclient.dll". This causes trouble while accessing via ZEOS because the protocol "firebird1.5"

aassumes a DLL named "fbclient.dll". A workaround is to copy the "gds32.dll" and rename this copy to

"fbclient.dll".

TZConnection

The TZConnection component is a combination of a BDE TDatabase like component a component that

handles a transaction.

Features of the Firebird embedded server

Normally the server name or IP address of the server is given in property HostName. By using a Firebird

embedded server you may leave this property empty. Only property Database has to be determined. Here

you have to specify drive path and name of the database including extension.

An other feature of the embedded server is that you may specify any login name with a pasword of your

choice. It doesn't matter what you choose you will get connected.

Useful TZConnection parameters

Additional parameters for establishing connections to Firebird databases are::

CreateNewDataBase:

A new database will be created based on the specified CREATE DATABASE statements. When the database

is created the connection will be established immediately. All this happens by calling the Connect method of

TZConnection.

:

ZConnection1.Database := 'd:\db1.fdb';

ZConnection1.Protocol := 'firebird-1.5';

ZConnection1.Properties.Add ('CreateNewDatabase=CREATE DATABASE ' +

QuotedStr ('d:\db1.fdb') + ' USER ' +

QuotedStr ('sysdba') + ' PASSWORD ' + QuotedStr ('masterkey') +

' PAGE_SIZE 4096 DEFAULT CHARACTER SET ISO8859_1');

ZConnection1.Connect;

:

TZQuery

The usage of TZQuery is similar to the usage of BDE's TQuery component.

Recommandation: RequestLive and TZUpdateSQL

If an SQL dataset shall be updatable then RequestLive has to be set to true and you should generally use

according update SQL statements that will be defined in TZUpdateSQL. If this is done just assign

TZUpdateSQL to the TZQuery object. Now all changes that will be made in the result set will be done to the

database by using the defined statements of TZUpdateSQL. According to experience RequestLive mode

runs more smoothly by using TZUpdateSQL.

Usage of parameters in SQL statements

Using parameters in SELECT statments is as easy as using them with BDE's TQuery.

TZReadOnlyQuery

This is a Query component that is quite similar to the TZQuery component. There is just one difference: The

result set is read only.

TZUpdateSQL

A TZUpdateSQL object provides statements to modify the data of a result set that is retrieved by a TZQuery

object. The TZUpdateSQL component is comparable to BDE's TUpdateSQL component.

Multiple statements in TZQuery and TZUpdateSQL

The components TZQuery and TZUpdateSql provide the possibility to execute multiple statements, internally.

So it is possible to place multiple SQL statements (even with parameters) for execution in SQL property.

They only have to be separated by semicolon. Here an example:

:

With Query do Begin

Sql.Clear;

Sql.Add('DELETE FROM table1;');

Sql.Add('INSERT INTO table1 VALUES (:Val1, :Val2);');

Sql.Add('INSERT INTO table2 VALUES (:Val3, :Val2);');

Sql.Add('UPDATE table3 SET field1 = :Val4;');

Params.ParamByName('Val1').AsInteger := 123;

:

ExecSql;

End;

:

TZTable

TZTable acts like BDE's TTable. As a principle you only should use TZTable in a C/S application if you have

very small tables because all records of the table will be transferred from server into client's memory by

opening the TZTable.

keep the resultset that has to be

transferred from server to client as small as possible (perferably onle one record).

TZStoredProc

TZStoredProc provides the possiblity to execute stored procedures that are saved in a database. There are

two kinds of stored procedures: Procedures that return a resultset and procedures that do not return a

resultset. TZStoredProc works similar to BDE's TStoredProc.

Stored Procedures with Resultsets

If a stored procedure returns a result set then it will be activated by calling the Open method (when all

existing parameters have got their values):

:

With spSumByName do Begin

Close;

ParamByName ('Name').Value := 'DontKnowHow';

Open;

End;

:

The resultset can be worked on like a resultset of a TZQuery.

Stored Procedures without Resultsets

If a stored procedure has no resultset then it will be executed by calling the ExecProc method (when all

existing parameters have got their values). Here is an example (conConnection.AutoCommit = True):

:

With spDeleteByName do Begin

ParamByName ('Name').Value := 'DontKnowHow';

conConnection.StartTransaction

Try

// execute StoredProc

ExecProc;

Except

conConnection.Rollback;

End;

conConnection.Commit;

End;

:

TZSQLProcessor

This component provides the function to process SQL scripts that can be loaded by calling the methods

LoadFromStream() or LoadFromFile(). The loaded SQLScript

is put into an according property called Script.

Importan is that the correct delimiter for the script is set (Property is also Delimiter). By default ";" will be set

as delimiter. This is sufficient for the most scripts. But if you want to create stored procedures or triggers via

script you should set delimiter according to the setting of the script's "SET TERM >newDelimiter<

>oldDelimiter<" command (normally "^" is used for this). In addition to this the property DelimiterType has to

be set to dtSetTerm. Here some lines of code that show how to process an SQL script:

:

sqlScript.Script.Clear;

sqlScript.LoadFromFile('c:\temp\createdb.sql');

conConnection.StartTransaction;

Try

sqlScript.Execute;

Except

conConnection.Rollback;

End;

conConnection.Commit;

:

The SQL script is processed within an explicit transaktion (AutoCommit is turned on). If execution succeeds

the changes will be committed otherwise they will be rolled back.

TZSQLMonitor

Using the TZSQLMonitor component you may log certain actions or events of the ZEOS database

components. The journal may be written as file or collected in a TMemo object or something like that.

Writing the actions or events to a logfile only needs a few settings:

:

sqlMonitor.FileName := 'C:\Log\MyAppLog.log';

sqlMonitor.Active := True;

sqlMonitor.AutoSave := True;

:

Master/Detail with ZEOS Library

ZEOS DataSet components come with two kinds of master/detail connections: those with a server sided filter

and those with a client sided filter. Both kinds and one kind in additioin that is independent from ZEOS (and

thus without any comfort)

Master/Detail with server sided filters

This method is the default behaviour of the BDE's TQuery component. A master/detail connection of two

DataSets is established as follows:

•

The master's DataSource is assigned to the DataSource of the detail.

•

All primary key fields of the master have to be compared with the foreign key fields of the detail in thedetail SQL statement.

This is an example for a simple master/detail queries. Requirement: We use TZQuery or TZReadOnlyQuery

to establish the master/detail connection:

•

Master SQL:

SELECT id, feld1, feld2, feld3

FROM master

[*]

DetailSQL:

SELECT feld1, feld2, master_id

FROM detail

WHERE master_id = :id

If the cursor of the master changes its position while server sided filters are used, the SQL statement of the

detail is executed using the current key values. So the result set of the detail is automatically refreshed.

Master/Detail with client sided filters

This is the default behaviour of a BDE TTable component. Here a master/detail connection between two

DataSets is established as follows:

•

The DataSource of the master is assigned to the property MasterDataSource of the detail.

•

The primary key fields of the master are assigned to property MasterField of the detail.

•

The foreign key of the detail which references the primary key of the master is assigned to propetyIndexFieldNames.

With client sided filters both DataSets first transfer all table rows from server to client. The detail then sets a

filter (on client side) to get the details according to the current master record.

In case of creating a new detail record for a master/detail connection with a client sided filter there is a kind of

automatism: The Foreign key fields of the detail (set in property IndexFieldNames of the detail) will be filled

automatically with the according (current) primary key data of the master (set in proptery MasterField of the

detail). Note: With server sided filters you have to care about this functionality, manually in your program's

code. This can be achieved by implementing the OnNewRecord event of the detail. This event is always

triggered when an new record is to be created (see: Delphi online help for TDataSet). According to the SQL

statements, defined above you only have to implement the following:

Procedure dmMasterDetail.qryDetailNewRecord (DataSet: TDataSet);

Begin

qryDetailMASTER_ID.Value := qryMasterID.Value;

End;

Corresponding TFields were created for the fields "master_id" of the detail and "id" of the master using the

fieldeditor.

For master/detail connections in ZEOS there is an additional option that is set in property Options: It is

doAlwaysResyncDetail. If this option is set then the resultset of the detail is only refreshed when post is

called or a record changes (both within master DataSet).

Master/Detail "

by hand"

Normally you implement a master/detail connection according to the method used by server sided filters. The

SQL statements for this look exactly like that. Only the properties that are set in master and detail (see

above) will not be set here. Both TZQueris are working independently. This means: The detail DataSet does

not recognize any changes in master DataSet. Its synchronzation has to be implemented, manually. This will

be done in the OnChange event of the master. OnChange is triggered when changing to a new record or

field data has been changed (see: Delphi online help for TDataSource). Synchronization of the detail

(according to the example above) would be implemented like this:

Procedure dmMasterDetail.dsMasterDataChange (

Sender: TObject; Field: TField);

Begin

With qryDetail do Begin

Close;

ParamByName('id').Value := qryMasterID.Value;

Open;

End;

End;

Michael Seeger

ZeosLib Development Team

Database Connectivity in Borland Delphi - Using ADO

Connecting Database with ADO

what is ADO stand for

ADO is a set of COM components (DLLs) that allow you to access databases as well as e-mail and file systems.

To access any kind of database with ADO, you'll of course need to have ADO/OLE DB libraries. Everything you need to use ADO is probably already on your computer: the files are distributed by Microsoft as a part of Windows 98/2000.

you will probably need to distribute and install the ADO engine. Delphi 5's CD includes an installation of MDAC - Microsoft Data Access Components. You should always make sure to have the latest version, which is available from Microsoft. The Microsoft Data Access Components are the key technologies that enable Universal Data Access. They include ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).

ADO Objects

These objects provide the functionality to connect to data sources, query and update record sets, and report errors.

some of the Objects ADO works with:
The Connection object represents a connection to the data source with the connection strings. In BDE/Delphi a Connection object is a combination of the Database and Session components.
The Command object enables us to operate on a data source. Ir represents a command (also known as a query or statement) that can be processed to add, delete, query or update the data in a database.
The Recordset object is a result of a Query command. You can think of a Recordset as a Delphi Table or Query component. Each row that the Recordset returns consists of multiple Field objects.

In order to be able to access data in an Access database with ADO and Delphi, you must add at least three data aware components to our project. First, the DBGrid on the DataControls component page - used to browse through the records retrieved from a table or by a query. Second, the DataSource (DataAccess Page) used to provide a link between a dataset and DBGrid component on a form that enable display, navigation, and editing of the data underlying the dataset. And finally the ADOTable (ADO page) that represents a table retrieved from an ADO data store.



Link between components
In order to display some data from a database we have to link all three components together. Using the Object Inspector, set the following:
DBGrid1.DataSource = DataSource1
DataSource1.DataSet = ADOTable1
to really get the data from our database we have to build a ConnectionString. This string indicates where the database is physically stored and how we are accessing it.


Press the Build button - this pops up the Data Link Properties dialog. This dialog has 4 pages. The Provider tab allows you to specify the provider - select the Microsoft Jet 4.0 OLE DB Provider. The Next button leads us to the second page: Connection. Select the ellipsis button to browse for our database (AboutDelphi.mdb). Press the Test Connection button to see if the connection is successful

the connection string is stored in the ConnectionString property of the ADTTable component. The connection string should look something like:
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\!gajba\About\aboutdelphi.mdb;
Persist Security Info=False
Every (ADO) data-aware Delphi form, in general, consist of
· several data-aware controls (Data Controls tab) that create a visual user interface (the look of the data form).
· one DataSource component (Data Access tab) that represents an interface between a dataset component and data-aware controls on a form.
· one or more dataset components (ADO tab) that provide access to the data residing in a database table or query.
· a connection component (ADO tab) that points all the dataset components to a specific data store.

Data Source
Simply put, the DataSource component provides a mechanism to hook dataset components to the visual data-aware components that display the data. You generally will need one datasource component for each dataset component to present a link to one or more data-aware controls.
Datasets
To create an ADO based application, Delphi provides us with four dataset components: TAdoDataSet, TAdoTable, TAdoQuery and TAdoStoredProc. All of the components are designed to retrieve, present and modify the data. All those components can connect directly (as like in the previous chapter's examples) to an ADO data store (such as data in an Access database) through it's ConnectionString property or they can chare a single connection. When connecting through a TAdoConnection the Connection specifies an ADO connection object to use to connect to an ADO data store.
ADO Connection
The ADOConnection component is used to establish a connection with an ADO data store. Although each ADO dataset component can directly connect to a database, we will typically want to use the ADOConnection component since the component provides methods and properties for activating the connection, accessing the ADO data store directly and for working with transactions. In order to connect to a specific database, we use the ConnectionString property.
Now, when we know the theory it's time to see some action. The next step is to build a data form. Before we move on, it'll be a good idea to open the database with Access and add some "dummy" data (3-4 records) to a database just to have some operational data.
There are two different ways of creating forms with access to a data from a database. The first way is to use the Database Form Expert. Unfortunately, the Database Form Expert works only with the BDE-aware set of dataset components. The second way is to place and connect all the data components by hand.
  Defining the User Interface
We'll build our data browsing form in three steps. First step is to define the user interface for the form. Next, the data access components are added and configured. In the third and final step, the data-aware controls are added

Data Connectivity In Dephi Database Application - Using DBExpress

Dephi Database Application - Using DBExpress

What is dbExpress?

dbExpress is a light-weight, extensible, cross-platform, high-performance mechanism for accessing data from SQL servers. dbExpress provides connectivity to databases for the Windows, .NET and Linux (using Kylix) platforms.
Initially designed to replace the BDE, dbExpress (introduced in Delphi 6), allows you to access different servers - mySQL, Interbase, Oracle, MS SQL Server, Informix.



One of the most significant features of dbExpress lies in the fact that it accesses databases using unidirectional datasets. Unidirectional datasets do not buffer data in memory - such a dataset cannot be displayed in a DBGrid. To build a user interface using dbExpress you will need to use two more components: TDataSetProvider and TClientDataSet.

Migrating Borland

®

Database Engine

Applications to

dbExpress

The dbExpress architecture

dbExpress (formerly dbExpress) was designed to meet the

following six goals.

•

Minimize size and system resource use.

•

Maximize speed.

•

Provide cross-platform support.

•

Provide easier deployment.

•

Make driver development easier.

•

Give the developer more control over memory usageand network traffic.

dbExpress drivers are small and fast because they provide very

limited functionality. Each driver is implemented as a single

DLL on the Windows

® platform and as a single shared object

library on the Linux

® platform. A dbExpress driver implements

five interfaces that support fetching metadata, executing SQL

statements and stored procedures, and returning a read only

unidirectional cursor to the result set. However, when used with

the DataSetProvider and ClientDataSet to implement the

provide/resolve data access strategy of Borland, dbExpress gives

you a full-featured, high performance, high concurrency system

for working with data in SQL databases.

architecture uses four components to provide

data access and editing. The first is the SQLConnection

component that provides a connection to the dbExpress driver for

the database you are using. Next is one of the dbExpress dataset

components that provides data by executing a

SQL SELECT

statement or calling a stored procedure. The third component is

the DataSetProvider, and the fourth is the ClientDataSet. When

you open the ClientDataSet, it requests data from the

DataSetProvider. The DataSetProvider opens the query or stored

procedure component, retrieves the records, closes the query or

stored procedure component, and supplies the records, with any

required metadata, to the ClientDataSet.

The ClientDataSet holds the records in memory while they are

viewed and modified. As records are added, deleted, or updated,

either in code or via the user interface, the ClientDataSet logs all

changes in memory. To update the database, you call the

ClientDataSet ApplyUpdates method. ApplyUpdates transmits the

change log to the DataSetProvider. The provider starts a

transaction, then creates and executes SQL statements to apply the

changes to the database. If all changes are applied successfully, the

provider commits the transaction; if not, it rolls the transaction

back. Database updates may fail if, for example, a change violates a

business rule enforced by a trigger or if another user has changed a

record you are trying to update since you read the record. If an

error occurs, the transaction is rolled back, and the ClientDataSet

OnReconcileError event is fired, giving you control of how the

error is handled.

Short transaction life

Long transactions force the database server to hold locks, which

reduces concurrency and consumes database server resources.

With provide/resolve architecture, transactions exist for a moment

when updates are applied. This dramatically reduces resource

consumption and improves concurrency on a busy database server.

Make any rows editable

Rows returned by multi-table joins, stored procedures, or readonly

views cannot be edited directly. By using the ProviderFlags

property of the field objects to identify the fields that should be

updated and the DataSetProvider OnGetTableName event to

supply the name of the table, many read only datasets can be

edited easily.

Instantaneous sorting and searching

Since the ClientDataSet holds records in memory, they can be

sorted quickly. If an in-memory sort is too slow, you can create

indexes on the ClientDataSet data at design time or runtime.

These in-memory indexes let you change the viewing order of

records or locate records virtually instantaneously without the

overhead of maintaining indexes in the database.

View subsets of data

Filter expressions using SQL WHERE syntax let you easily

display a subset of the records in a ClientDataSet without the

overhead of executing another query on the database server.

Easier deployment

An application using dbExpress requires just two DLLs to

function. The first is the dbExpress driver, for example

DBEXPINT.DLL

in the case of Borland InterBase,® and the

second is

MIDAS.DLL, the ClientDataSet support library.

Together, these two DLLs are less than half a megabyte in size.

This minimizes application size and simplifies installation. If you

prefer not to distribute these DLLs, you can compile them

directly into your EXE. Deployment on Linux is identical,

except that the DLLs are replaced by two shared object libraries.

The SQLConnection component

To create a simple dbExpress application, begin with the

following steps.

1. Create a

new application and add a data module to

it. Name the data module

MainDm.

2. Use the

Project Options dialog to make sure that the

data module is created automatically before the main

form is created.

3. Drop a

SQLConnection component from the

dbExpress page of the component palette on

the

data module

.

4. Name the SQLConnection component

EmployeeConnection

and set its DriverName

property to

InterBase.

5. Open the

property editor for the Params property and

set the

database parameter to the path to the sample

InterBase

EMPLOYEE.GDB database. On most

installations, this will be

c:\program

files\Borland\interbase\examples\dat

abase\employee.gdb.

6. Change the

UserName and Password parameters if

you require different values to connect to your

InterBase server.

7. Set the

LoginPrompt property to false so you will not

be prompted for a username and password each time

you run the program.

8. Set the

Connected property to true to test your

connection, and then set it to

false again.

The SQLConnection component provides a database

connection for any number of dataset components. You can use

multiple SQLConnection components to connect to many

databases simultaneously. There are three ways to define a

connection to a database. You can use an existing named

connection, create a new named connection or put the

connection parameters in the Params property of the

SQLConnection component. To use an existing named

connection just set the ConnectionName property.

The dbExpress Connection Editor

To create a new named connection, double-click the

SQLConnection component

to open the dbExpress

Connection Editor. The Connection Name list box, on the left,

shows any connections that have already been defined. The

Driver drop-down list lets you

filter the Connection Names to

show only the connections for the driver you select. The

Connection Settings grid on the right shows the connection

settings for the selected connection. All of the connections you

create are stored in the
dbxconnections.ini file.


A video From

http://www.youtube.com/watch?v=Zjt-xe6f6gk

How To Use Delphi with Excel - Formating MS. Excel

//a part of TExcelApplication use

var fnt: ExcelXP.Font;
    wb: ExcelWorkbook;
    ws: ExcelWorksheet;
    rng: ExcelRange;
Begin
  ExcelApplication1.Connect;
    wb := ExcelApplication1.Workbooks.Open('D:\MyTestProjects\EE\Excel\bin\test.xlsx', 0, False, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0);
    WS:= ExcelApplication1.Worksheets.Item['Blad1'] as _Worksheet;
    rng := ws.Range['A2', 'A2'];
    fnt := rng.Characters[1, 14].Font;
    fnt.Name := 'Arial';
    fnt.FontStyle := 'Bold';
    fnt.Size := 9;
    fnt.Strikethrough := False;
    fnt.Superscript := False;
    fnt.Subscript := False;
    fnt.OutlineFont := False;
    fnt.Shadow := False;
    fnt.Underline := xlUnderlineStyleNone;
    fnt.ColorIndex := xlAutomatic;
    rng := ws.Range['A2', 'A2'];
    fnt := rng.Characters[15, 24].Font;
    fnt.Name := 'Arial';
    fnt.FontStyle := 'Bold';
    fnt.Size := 9;
    fnt.Strikethrough := False;
    fnt.Superscript := False;
    fnt.Subscript := False;
    fnt.OutlineFont := False;
    fnt.Shadow := False;
    fnt.Underline := xlUnderlineStyleSingle;
    fnt.ColorIndex := xlAutomatic;
    ExcelApplication1.Visible[0] := True;
end;

Accessing and managing MS Excel sheets with Delphi - Transfer data to: MS Excel

transferring data from other sources (in your Delphi application) to an Excel workbook.

From Acees to Excel over Delphi using ADO

you can use SQL statements to insert (copy) data from any Jet compliant data source to any (compatible) data destination.
If this destination is an Excel workbook, Delphi (using ADO) enables you to transfer you "custom" data to Excel and even create a new workbook, if needed.

you can use SQL statements to insert (copy) data from any Jet compliant data source to any (compatible) data destination.
If this destination is an Excel workbook, Delphi (using ADO) enables you to transfer you "custom" data to Excel and even create a new workbook, if needed.

(TAdoQuery). We'll be accessing the sample QuickiesContest.mdb

along with the link between AdoConnection2 and AdoQuery2 (add this code to the end of the Form1 OnCreate even handling procedure):

//connect to an Access database to send the data to Excel AdoConnection2.LoginPrompt:=False; AdoConnection2.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\!Gajba\About\QuickiesContest.mdb; Persist Security Info=False'; AdoQuery2.Connection:=AdoConnection2;

Now, we'll need two more buttons: Button1 (TButton) will be used to insert data from Access to Excel, and Button2 (TButton) will be used for data copying purposes.

Insert Into ... Excel

When transferring data to an existing sheet, the column headings must already be present.

Here's an example of appending the values from three fields in the Articles table (Access database) to the Sheet2 in our test workbook:

procedure TForm1.Button2Click(Sender: TObject); var sAppend : string; begin sAppend := 'INSERT INTO [Sheet2$] IN "' + Edit1.Text + '" "Excel 8.0;" SELECT AuthorEmail, Title, Description FROM Articles'; AdoQuery2.SQL.Text:=sAppend; AdoQuery2.ExecSQL; end;

go to that Excel workbook, select Sheet2, and see for yourself: the data is transferred

Select Into ... Excel

to copy data from Access to Excel, and create a new sheet (and even a new workbook) along the way, you could try the next code:

procedure TForm1.Button1Click(Sender: TObject); var sCopy : string; begin sCopy := 'SELECT * INTO ["Excel 8.0;Database=' + Edit1.Text + '"].[SheetAuthors] FROM Authors'; AdoQuery2.SQL.Text:=sCopy; AdoQuery2.ExecSQL; end;

Accessing and managing MS Excel sheets with Delphi - Retrieve data from: MS Excel

Retrieve data from: MS Excel

Once connected, we are just one step away from retrieving the worksheet's data. First, we need to specify exactly what data we are trying to retrieve. In the most basic case this will be the data in the worksheet.

sheets are merely "datase" tables.recall that the GetTableNames method of the ADOConnection object populates a string list with the names of tables

AdoConnection1.GetTableNames(ComboBox1.Items,True);

specify the range, in the SQL statement's FROM part, you want to grab from Excel. Here's how:

• Use the sheet name followed by a dollar sign ($) and surrounded by square brackets, like "[Sheet1$]",
• Specify a named range, like "MyRange",
• Use an unnamed range by appending standard Excel row/column notation to the end of the sheet name, like "[Sheet1$A5:D20]".

fetches the data from an Excel workbook is defined as:

procedure TForm1.FetchData;
begin
  StatusBar1.SimpleText:='';

  ConnectToExcel;

  AdoQuery1.Close;
  AdoQuery1.SQL.Text:=Edit2.Text;
  try
    AdoQuery1.Open;
  except
    ShowMessage('Unable to read data from Excel, 
                 make sure the query ' + Edit1.Text + 
                 ' is meaningful!');
    raise;
  end;
end;

To actually fetch the data, we add a call to the FetchData procedure in BitBtn1's OnClick event handler:

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FetchData;
  
  //explained later
  GetFieldInfo;end;

columns

when connecting to Excel workbooks using ADO, the first row in a specified range is, by default, considered to hold the field (column) names - all subsequent rows are supposed to contain the records. When the first row does not contain headers, ADO automatically names the fields for you (where "F1" is the name of the first field, F2 represents the second field, and so on).
This is why, in our example, the unnamed column's header cell, was assigned the name "F4".

 it's time for a discussion on retrieved field (column) types...

Contrary to the traditional (relational database) approach, when using Excel as the datasource for you application, there is no direct way to specify the data types for columns in retrieved Excel ranges (sheets).

Excel as a data "provider", does not provide ADO with the information about the data it contains. The Jet must scan through the referenced range to guess the type of data it caries (the formatting of the cells does not count). The number of rows Jet scans is 8, by default. If you need more rows to be examined (before field types are determined), you can add a "MaxScanRows=X" to the connection string, where X specifies the value from 0 to sixteen 16. A value of zero tells the Jet to scan all existing rows.

Caution: Some problems may occur if you have mixed string values with numeric values "under" the same column. If this is the case, the Jet could return mixed null-string or null-number values.

Recall that the GetFieldInfo is called inside the button's "Re-fetch" OnClick event handler. This is how the GetFieldInfo procedure looks:

uses typinfo; //don't forget
...
procedure TForm1.GetFieldInfo;
var
  i      : integer;
  ft     : TFieldType;
  sft    : string;
  fname  : string;
begin
  ListBox1.Clear;
  for i := 0 to AdoQuery1.Fields.Count - 1 do
  begin
    ft := AdoQuery1.Fields[i].DataType;
    sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
    fname:= AdoQuery1.Fields[i].FieldName;
    
    ListBox1.Items.Add(Format('%d) NAME: %s TYPE: %s,
                              [1+i, fname, sft]));
  end;
end;

field in an Excel data source can be only one of the following:

• Numeric, ADO type adDouble, Delphi type ftFloat - precision 15,
• Text, ADO type adVarChar, Delphi type ftWideString - max length 255,
• Memo, ADO type adVarWChar, Delphi type ftMemo,
• Currency, ADO type adCurrency, Delphi type ftCurrency,
• Boolean, ADO type adBoolean, Delphi type ftBoolean,
• Date, ADO type adDate, Delphi type ftDateTime,

Accessing and managing MS Excel sheets with Delphi - Connect to: MS Excel

Connect to: MS Excel

Since Excel is JET OLE DB compliant, you can connect to it with Delphi using ADO (dbGO or AdoExpress) then retrieve the worksheet's data into an ADO dataset by issuing a SQL query - just like you would open a dataset against any database table.

using the ADO components, you can build an application that can use an Excel workbook as the database.

Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.

 ADO connection cannot be used for sheet formatting or implementing formulas to cells unles pre-formated.you can carry out any conditional formatting using a (pre-recorded) macro in the worksheet.

You can connect to Excel using ADO with the two OLE DB Providers that are a part of MDAC: Microsoft Jet OLE DB Provider or Microsoft OLE DB Provider for ODBC Drivers.

The ConnectionString

The ConnectionString property tells ADO how to connect to the datasource.

TADOConnection component encapsulates the ADO connection object; it can be shared by multiple ADO dataset (TADOTable, TADOQuery, ...) components

a valid connection string involves only two (additional) pieces of information: the full path to the workbook, and the Excel file version.

ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyWorkBooks\myDataBook.xls;Extended Properties=Excel 8.0;';

Caution: you must use the Jet 4.0 Provider, since Jet 3.5 does not support the ISAM drivers. If you set the Jet Provider to version 3.5 you'll receive the "Couldn't find installable ISAM." error message.

Another Jet extended property is "HDR=". "HDR=Yes" means that there is a header row in the range, so the Jet will not include the first row of the selection into the dataset. If "HDR=No" is specified, then the provider will include the first row of the range (or named range) into the dataset. The first row in a range is considered to be the header row by default ("HDR=Yes"), therefore if you have column heading you do not need to specify this value. If you do not have column headings, you need to specify "HDR=No";

First, we need to connect all the DB related components together.

This is done in the OnCreate event handler for the Form1:

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoConnection1.LoginPrompt := False;
  AdoQuery1.Connection       := AdoConnection1;
  DataSource1.DataSet        := AdoQuery1;
  DBGrid1.DataSource         := DataSource1;
  DBNavigator1.DataSource    := DataSource1;

  Application.OnException    := DisplayException;
end;

handle any possible error in one common place. The Application.OnException event is the right candidate for the job, on any exception the DisplayException gets called.

procedure TForm1.DisplayException(Sender: TObject; E: Exception);
begin
  StatusBar1.SimpleText := E.Message;
end;

the connection part.

procedure TForm1.ConnectToExcel;
var strConn :  widestring;
begin
  strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
           'Data Source=' + Edit1.Text + ';' +
           'Extended Properties=Excel 8.0;';

  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  try
    AdoConnection1.Open;
    AdoConnection1.GetTableNames(ComboBox1.Items,True);
  except
    ShowMessage('Unable to connect to Excel, make sure
                 the workbook ' + Edit1.Text + ' exist!');
    raise;
  end;
end;(*ConnectToExcel*)