Demonstration of methods which operate data in the table of database Microsoft Access using a component TADOTable

In the topic is described methods of component TADOTable for work with data in database Microsoft Access.

Such methods are demonstrated:

• Append – adding a new record to the end of table;
• Insert – inserting a new record in the current position of table;
• Edit – editing of current record;
• Post – fixing of changes in database.

Also, the different methods for access to the fields of current record of database are described.

The component “TADOTable” represents a single database table. This component realizes inserting, editing, deleting, viewing, searching an filtering of set of records. It is enough for single table. But it is impossible, when you process the data from several tables, by using TADOTable component. To do it you need to use component-query TADOQuery, which uses SQL-language to work with databases.

 

The task

Create an application, which realizes the main operations for database table using such methods of Delphi:

• adding a record to the end of database table;
• inserting a record to the current position of database table;
• editing the record in the database table;
• deleting the record from database table.

The demo table of database is named as “Table1”. The structure of table is following.

You can load the table here.

 

Progress

1. Run Delphi.

Save the project in the folder.

The name of main form leave by default Form1.

 

2. Building the main form of application.

Place on the form such components as shown in Figure 1.

The components of following types are placed on the form:

– component of type TLabel for displaying the messages. The object-variable Label1 is created;

– four components of type TButton. Each component calls the corresponding command of data processing in the database. Four objects with names Button1, Button2, Button3, Button4 are created;

– a component of type TDBGrid. It is used for displaying of table in database. The object with name DBGrid1 is created.

Figure 1. The main form of application after placing of component

 

3. Setting the form properties.

You can set up the main properties of form by using Object Inspector. To set up the properties of form, first, you need to select the form. After that, you need to set up the following properties:

– property Caption = “Program of demonstration of methods for operating the database”;

– property Position = “poScreenCenter”;

– property BorderStyle = “bsDialog”.

 

4. Setting of properties of components Label1, Button1, Button3, Button4.

To set up the property of component, first of all, you need to select this component. The following properties are set:

– in the component Label1 property Caption = “Table “Table1””;

– in the component Button1 property Caption = “Add record to the end of table”;

– in the component Button2 property Caption = “Insert record to the current position”;

– in the component Button3 property Caption = “Edit the record”;

– in the component Button4 property Caption = “Delete record”;

Also, you need to correct the size and position of components on the form.

As a result, the form will have the following view (Figure 2).

Figure 2. The main form of application

 

5. Connecting the application to database.

Connecting the database to the application is carried out in a standard way and is a process of clarify as a wizard. The detailed example is described here.

Database is connected to the application by using property ConnectionString of component ADOConnection1.

When connecting you need to choose:

– in the first window “Source of Connection” = “Use connection string”;

– in the first tab of the second window you need to select provider OLE DB = “Microsoft Jet OLE DB Provider”;

– in the second tab of second window you need to select path to the file of database. It is recommended save the file of database (*.mdb) in the folder of developed application.

 

6. The setting of components to organize of work with database.

Components ADOConnection1, DataSource1, ADOTable1 carried out the connection between application and data of database.

Component DBGrid1 displays data of database as a table.

You need to set the following properties of components:

• in the component ADOConnection property LoginPrompt = “false”. It disables password checking to access of database;
• in the component ADOTable1 property Connection = “ADOConnection1”;
• in the component ADOTable1 property TableName = “Table1”;
• in the component DataSource1 property DataSet = “ADOTable1”;
• in the component DBGrid1 property DataSource = DataSource1;
• in the component DBGrid1 from tab Options property dgEditing = “false”. This action disable the random editing of cells of database table through DBGrid1;
• in the component ADOTable1 property Active = “true”. After this operation, data from the table Table1 will be displayed in DBGrid1.

Now, the application form has the following view (Figure 3).

Figure 3. The application after setting of components

 

7. The setting of table view in DBGrid1.

In DBGrid1, the table is displayed not very convenient. Because, using the fields editor “Columns Editor…” of component DBGrid1, you can edit the view of columns of table.

Using the right mouse button in the area of component DBGrid1, you can call the fields editor from the context menu.

The detailed description of work with “Columns Editor” is described here. As a result, you can edit the width of each field. In this theme, the work with the editor of component “DBGrid1” is not described.

After correcting of width of fields, the application form will have the following view (Figure 4).

Figure 4. Application form and fields editor

 

8. Creating a secondary form Form2.

The form Form2 will display the values of fields of a record, which will be added or edited using commands.

Depending on the selected command in the form Form1, will be formed the corresponding values of properties of component Form2.

Process of creating a new form in Embarcadero RAD Studio is described here in details. As a result, the new object-variable Form2 will be created.

Save the form by name “Unit2.pas”.

From the “Tool Palette” you need to place on the form Form2 the following components:

• from tab Standard, four components of type TLabel, which display the explaining messages. The four objects with names Label1, Label2, Label3, Label4 will be created;
• from the tab Standard, three components of type TEdit. Three objects with names Edit1, Edit2, Edit3 will be created;
• from tab Win32 a component of type TDateTimePicker. The object with name DateTimePicker1 will be created. Here will be displayed DateTimeField field;
• from tab Standard, two components of type TButton. As a result, the two objects named Button1 and Button2 will be created.

Figure 5. Component TDateTimePicker from tab Win32

 

Figure 6. Form “Form2” after placing of components

Set up the properties of form and components:

• in the form Form2 property BorderStyle = bsDialog;
• in the form Form2 property Position = poScreenCenter;
• in the component Button1 property Caption = “OK”;
• in the component Button1 property ModalResult = “mrOk”. It means, when you click on the button Button1, form will be closed with returning code “mrOk”;
• in the component Button2 property Caption = “Cancel”;
• in the component Button2 property ModalResult = “mrNo”. It means, when you click on the button Button2, form will be closed with returning code “mrNo”;
• in the component Label1 property Caption = “TextField = “;
• in the component Label2 property Caption = “IntegerField = “;
• in the component Label3 property Caption = “FloatField = “;
• in the component Label4 property Caption = “DateTimeField = “.

After setting of the properties and minor correcting of positions and components size, the form will look as shown in Figure 7.

Figure 7. Form “Form2”

Components Edit1, Edit2, Edit3 and Edit4 designed to display of records values of the corresponding fields of the table. It can be a new values, when you add new data or edit the existing data.

 

9. Programming the event of click on the button “Add record to the end of table”.

From the Form1 will be called Form2. The data are inputted to the Form2. These data must be inputted into the table of database.

Code listing of event handler of clicking on the button Button1 is following:

procedure TForm1.Button1Click(Sender: TObject);
var
  t_int:integer;
  t_float:real;
  t_string:string;
  t_date:TDateTime;

begin

  // 1. Forming the properties of form Form2
  Form2.Caption := 'Add record to the end of table';
  Form2.Edit1.Text := '';
  Form2.Edit2.Text := '0';
  Form2.Edit3.Text := '0,00';
  Form2.DateTimePicker1.Date := Date; // set the current date

  // 2. Calling the form with check on the pressing by OK
  if Form2.ShowModal = mrOk then
  begin
    // read data in the temporary variables
    t_string := Form2.Edit1.Text;
    t_int := StrToInt(Form2.Edit2.Text);
    t_float := StrToFloat(Form2.Edit3.Text);
    t_date := Form2.DateTimePicker1.DateTime;

    // add a new record at the end of table
    ADOTable1.Append;

    // fill the values of fields of new record
    ADOTable1.Fields[1].AsString := t_string;
    ADOTable1.Fields[2].AsInteger := t_int;
    ADOTable1.Fields[3].AsFloat := t_float;
    ADOTable1.Fields[4].AsDateTime := t_date;

    // fix the changes
    ADOTable1.Post;
  end;
end;

Adding the record at the end of table is carry out by calling of method Append(), which is realized in component TADOTable.

String like

ADOTable1.Fields[1].AsString := t_string;

sets the value of variable t_string into the field with index 1. The field “TextField” from the table of database corresponds to this field.

You can replace the above string by different ways, which will be do the same work:

1)

ADOTable1.Fields[1].Value := t_string;

2)

ADOTable1.FieldByName('TextField').Value := t_string;

3)

ADOTable1.FieldByName('TextField').AsString := t_string;

4)

ADOTable1.FindField('TextField').Value := t_string;

5)

ADOTable1.FindField('TextField').AsString := t_string;

This also applies to other types of variables.

The field with index 0 is the field with the name of «ID». However the type of this field is counter, then it is formed automatically and cannot be changed.

After, when changes are made in the table, is needed fix it by using method

ADOTable1.Post;

 

10. Programming of event of click on the button “Insert record in the current position”.

The event handler of clicking on the button Button2, is like a handler, described in p.9.

Listing of handler is following.

procedure TForm1.Button2Click(Sender: TObject);
var
  t_int:integer;
  t_float:real;
  t_string:string;
  t_date:TDateTime;

begin
  // 1. Forming the properties of Form2
  Form2.Caption := 'Insert record to the current position';
  Form2.Edit1.Text := '';
  Form2.Edit2.Text := '0';
  Form2.Edit3.Text := '0,00';
  Form2.DateTimePicker1.Date := Date;

  // 2. Calling of form Form2
  if Form2.ShowModal = mrOk then
  begin
    t_string := Form2.Edit1.Text;
    t_int := StrToInt(Form2.Edit2.Text);
    t_float := StrToFloat(Form2.Edit3.Text);
    t_date := Form2.DateTimePicker1.DateTime;

    ADOTable1.Insert;
    ADOTable1.FieldByName('TextField').Value := t_string;
    ADOTable1.FieldByName('IntegerField').Value := t_int;
    ADOTable1.FindField('FloatField').Value := t_float;
   ADOTable1.FieldByName('DateTimeField').Value := t_date;
    ADOTable1.Post;
  end;
end;

As you see from listing above, inserting a new record in the current position of database table is carry out by method

ADOTable1.Insert;

However, in this table the record will be inserted at the end of the table all the same. This is due to the fact, that these tables are sorted by primary key – field ‘ID‘, which is a counter.

 

11. Programming of event of click on the button “Edit the record”.

Code listing of event handler of clicking on the button Button3 is following.

procedure TForm1.Button3Click(Sender: TObject);
var
  t_int:integer;
  t_float:real;
  t_string:string;
  t_date:TDateTime;

begin
  // 1. Forming of properties of Form2
  Form2.Caption := 'Editing the record';
  Form2.Edit1.Text := ADOTable1.Fields[1].Value;
  Form2.Edit2.Text := ADOTable1.Fields[2].Value;
  Form2.Edit3.Text := ADOTable1.Fields[3].Value;
  Form2.DateTimePicker1.Date := ADOTable1.Fields[4].Value;

  // 2. Calling of Form2
  if Form2.ShowModal=mrOk then
  begin
    // forming the temporary variables
    t_string := Form2.Edit1.Text;
    t_int := StrToInt(Form2.Edit2.Text);
    t_float := StrToFloat(Form2.Edit3.Text);
    t_date := Form2.DateTimePicker1.DateTime;

    // put the table into editing mode
    ADOTable1.Edit;

    // set the changes in current record
    ADOTable1.Fields[1].Value := t_string;
    ADOTable1.Fields[2].Value := t_int;
    ADOTable1.Fields[3].Value := t_float;
    ADOTable1.Fields[4].Value := t_date;

    // fix the changes
    ADOTable1.Post;
  end;
end;

To put the table into editing mode is used the method

ADOTable1.Edit;

 

12. Programming the event of click on the button “Delete record”.

Listing of event handler of deleting the record is following.

procedure TForm1.Button4Click(Sender: TObject);
begin
  if ADOTable1.RecordCount<=0 then exit;
  ADOTable1.Delete;
end;

Deleting of record is realized by method

ADOTable1.Delete;

 

13. Run the application.

Now you can run the application and test it.