Baskara Blog

    Lama ga posting, udah 2016 aja hehehe, kali ini saya mo copy - paste (this article was copy and paste for educational reason) artikel dari web orang lain dimana artikel yang di tulis web ini adalah kunci dari kebingungan saya tentang database delphi dengan ms access. Disini saya punya masalah ingin menghapus seluruh isi data didalam database menggunakan form delphi dengan hanya satu tombol button, udah googling sana-sini ternyata ga ada solusi, dan akhirnya nemu web dengan template sederhana ternyata artikel tentang algoritma pemrogramannya juga sederhana tetapi berguna banget dan masalah saya akhirnya terpecahkan pada artikel poin 9, saya sendiri juga masih belajar bahasa pemrograman Delphi dan mendalaminya, mungkin anda juga mencari pemecahan permasalahan yang sama, maka saya copas disini supaya makin banyak yang tahu dan semoga bermanfaat bagi orang banyak....



Alamat web sumbernya : KLIK DISINI

1. SET UP ADO TABLE
1.1 Create database file in MS Access
1.2 In Delphi: choose ADOTable under the ADO menu on the component palette
1.3 Change settings in the Object Inspector: set the Connection String settings by clicking on the ellipse
1.4 Click on Build…
1.5 Choose: Microsoft Jet 4.0 OLE DB Provider
1.6 Click Next >>
1.7 Select database file (mdb file)
1.8 Erase user name (‘Admin’)
1.9 Make sure 'Blank Password' is selected
1.10 Click OK (on 'Data Link Properties' window)
1.11 Click OK (on 'ConnectionString' window)
1.12 Click on ADOTable component: choose Table name for ADOTable
1.13 Set ADOTable Active to TRUE
1.14 Add DataSource (link to table)
1.15 Add DBGrid and DBNavigator (link to table)


2. ADD RECORD TO TABLE USING CODING
2.1 A new record can be added using set data:

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOTable1.Append;
ADOTable1['Name'] := 'John';
ADOTable1['Surname'] := 'Doe';
ADOTable1.Post;
end;

2.2 A new record can be added from an edit box:

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOTable1.Append;
ADOTable1['Name'] := Edit1.Text;
ADOTable1['Surname'] := Edit2.Text;
ADOTable1.Post;
Edit1.Clear;
Edit2.Clear;
end;


3. SEARCH FOR A RECORD IN A TABLE

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOTable1.First;
while not ADOTable1.Eof do
begin
if ADOTable1['Name'] = Edit1.Text then
begin
ShowMessage('This record is present');
Exit;
end
else
ADOTable1.Next;
end;
end;


4. SEARCH AND REPLACE FIELDS WITH SET DATA
procedure TForm1.Button2Click(Sender: TObject);
begin
ADOTable1.First;
while not ADOTable1.Eof do
begin
if ADOTable1['Name'] = Edit1.Text then
begin
ADOTable1.Edit;
ADOTable1['Name'] := Edit2.Text;
end;
ADOTable1.Next;
end;
end;


5. DO A CALCULATION IN A FIELD USING DATA FROM EXISTING FIELDS

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOTable1.First;
While not ADOTable1.Eof do
begin
ADOTable1.Edit;
ADOTable1['Total'] := ADOTable1['Unit'] * ADOTable1['Amount'];
ADOTable1.Next;
end;
end;


6. DELETE RECORD MEETING SEARCH CRITERIA

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOTable1.First;
while not ADOTable1.Eof do
begin
if ADOTable1['Name'] = Edit1.Text then
begin
ADOTable1.Delete;
Exit;
end
else
ADOTable1.Next;
end;
end;


7. DELETE RECORD CURRENTLY IN FOCUS
7.1 Click on record then click on Button1:

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.Delete;
end;


8. FILTER RESULTS
8.1 Filter according to exact search criteria (e.g. Name = 'John'):

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.Filter := 'Name = ' + '''' + Edit1.Text + '''';
ADOTable1.Filtered := True;
end;

8.2 Filter according to similar search criteria (e.g. Name LIKE 'Jo%'):

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.Filter := 'Name LIKE ' + '''' + Edit1.Text + '%''';
ADOTable1.Filtered := True;
end;

8.3 Filter according to number value:

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.Filter := 'Amount > ' + Edit1.Text;
ADOTable1.Filtered := True;
end;


9. DELETE ALL RECORDS

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.First;
while not ADOTable1.Eof do
begin
if ADOTable1['Name'] <> '' then
ADOTable1.Delete
else
ADOTable1.Next;
end;
end;


10. COUNT RECORDS

procedure TForm1.Button3Click(Sender: TObject);
var
iCount : Integer;
begin
iCount := 0;
ADOTable1.First;
while not ADOTable1.Eof do
begin
if ADOTable1['Name'] <> '' then
iCount := iCount + 1;
ADOTable1.Next;
end;
Label1.Caption := IntToStr(iCount);
end;


11. QUERIES
11.1 Set up query
11.1.1 Create database file in MS Access
11.1.2 In Delphi: choose ADOQuery under the ADO menu on the component palette
11.1.3 Change settings in Object Inspector: set Connection String settings by clicking on the ellipse
11.1.4 Click on Build…
11.1.5 Choose: Microsoft Jet 4.0 OLE DB Provider
11.1.6 Click Next >>
11.1.7 Select database file (mdb file)
11.1.8 Erase user name (‘Admin’)
11.1.9 Make sure 'Blank Password' is selected
11.1.10 Click OK (on 'Data Link Properties' window)
11.1.11 Click OK (on 'ConnectionString' window)


11.2 Set Query in Object Inspector
- Click on ellipse next to (TStrings) for SQL property of the ADOQuery
- Type in SQL code (see below)
- Set Active property to True

11.2.1 Show all fields and all records
Select * from tblTableName;

11.2.2 Show certain field(s) and all records
Select Name, Surname from tblTableName;

11.2.3 Show all fields for records meeting certain exact criteria
Select * from tblTableName where Name = "John ";

11.2.4 Show all fields for records meeting similar criteria
Select * from tblTableName where Name LIKE "Jo% ";

11.2.5 Show all fields and all records sorted according to a field (ascending)
Select * from tblTableName order by Name;

11.2.6 Show all fields and all records sorted according to a field (descending)
Select * from tblTableName order by Name DESC;

11.2.7 Show all fields and all records within a set range (Unit is an Integer field)
Select * from tblTableName where Unit between 1 and 6;


11.3 Set Query with coding

procedure TForm1.Button3Click(Sender: TObject);
begin
with ADOQuery1 do
begin
Active := false;
SQL.Clear;
SQL.Add('Select * from tblTableName;');
Active := true;
end;
end;

SQL coding added to ‘SQL.Add’ statement.


11.4 Change data (Amount field becomes 10) according to a condition (Unit equals to 4)

procedure TForm1.Button3Click(Sender: TObject);
begin
with ADOQuery1 do
begin
Active := false;
SQL.Clear;
SQL.Add('Update tblTableName');
SQL.Add('set Amount = 10 where Unit = 4');
ExecSQL
end;
ADOTable1.Refresh;
end;


11.5 Filter according to input from an edit box

procedure TForm1.Button3Click(Sender: TObject);
begin
with ADOQuery1 do
begin
SQL.Clear;
SQL.Add('Select *');
SQL.Add('from tblTableName');
SQL.Add('where Name = "' + Edit1.Text + '"');
Open;
end;
end;


12. SETUP A QUICKREPORT
12.1 Activate QuickReport (QReport) on component palette (Delphi 7)
12.1.1 Choose on drop down menu: Component > Install Packages
12.1.2 Click on Add
12.1.3 Select file ‘dclqrt70.bpl’ under C:\Program Files\Borland\Delphi7\Bin
12.1.4 Click Open
12.1.5 Click OK

12.2 Create QuickReport
12.2.1 Choose on drop down menu: File > New > Other
12.2.2 Select Report and press OK
12.2.3 Press F12 (to go to unit)
12.2.4 Add unit (on which the table is located) to the Uses statement of the QuickReport unit.
E.g. uses Windows, SysUtils, Messages, Classes, Graphics, Controls,
StdCtrls, ExtCtrls, Forms, QuickRpt, QRCtrls, Unit1;
12.2.5 Press 12 (to go back to QuickReport form)
12.2.6 Click on QuickReport
12.2.7 Set DataSet property to ADOTable
12.2.8 Double click on QuickReport – select all bands:

12.2.9 Click on OK
12.2.10 Select items on the QReport tab on the component palette
12.2.11 Use QRLabel for text to be added to the report and use QRDBText to display fields from the database.
12.2.12 With the QRDBText select the ADOTable in the DataSet property.
12.2.13 Choose the relevant field in the DataField property.
12.2.14 Preview report by right clicking and selecting Preview OR use coding:

procedure TForm1.Button4Click(Sender: TObject);
begin
QuickReport2.Preview;
end;

12.2.15 Click on Yes to add reference to USES list


All Picture (except the begining) from http://www.teachitza.com/delphi/databasehowto.htm
TAG : How to erase / delete all data / records the database with Delphi 7 using ADOTable and MS Access