Databases in Delphi Grade 11

In Grade 11 you learn how to connect a Delphi program to a Microsoft Access database, display its data on screen, and add, edit or delete records using code — without SQL (SQL comes in Grade 12).

Grade 11 vs Grade 12

In Grade 11 you use TADOTable and write code (loops, IF statements) to work with records.
In Grade 12 you switch to TADOQuery and use SQL statements to do the same things — faster and more powerful.

The Big Picture — How the Pieces Connect

Think of connecting to a database like setting up a water supply to a tap:

Understanding Each Component

TADOConnection — The Door to the Database

Imagine the database file (.accdb) is locked inside a room. The TADOConnection is the key that unlocks the door so Delphi can get in. You tell it where the file is on your computer.

Where it goes

Place TADOConnection on a Data Module (a special form-like container for database components). Go to File → New → Data Module to create one.

Key properties to set in Object Inspector

ConnectionString  → Build... → Choose "Microsoft Jet 4.0 OLE DB Provider"
                             → Browse to your .accdb file
LoginPrompt       → False   (stops the "Enter password" popup appearing)

TADOTable — One Table from the Database

A database can have many tables (Students, Subjects, Classes…). A TADOTable represents one of those tables. You need one TADOTable for each table you want to work with.

Key properties

Connection  → select your ADOConnection (dm.conDB)
TableName   → choose the table from the dropdown (e.g. "tblStudents")
Active      → True   (this "opens" the table so data loads)

TDataSource — The Go-Between

This is the part students find confusing. Why do we need it?

Think of it this way: your TADOTable is like a water tank (holds all the data). Your DBGrid is the tap (where the data comes out on screen). The TDataSource is the pipe connecting the tank to the tap. Without the pipe, no water flows.

You cannot connect a DBGrid directly to a TADOTable. Every visual display component (DBGrid, DBEdit, DBLabel) must connect through a TDataSource. These display components are called data-aware controls — controls that are aware of database data.

Key property

DataSet → select your TADOTable (dm.tblStudents)

TDBGrid — The Table You See on Screen

The DBGrid is a component you drop onto your main form (not the Data Module). It displays the database records as rows and columns — just like a spreadsheet. It updates automatically as you navigate through records.

Key property

DataSource → select your TDataSource (dm.dsStudents)

Setting up the database connection in code

Step-by-Step Setup

Create a Data Module: File → New → Data Module. Save it as DataModule_u.pas.
Drop TADOConnection onto the Data Module. Set up the connection string (browse to your .accdb file). Set LoginPrompt = False.
Drop TADOTable onto the Data Module. Set Connection to the ADOConnection. Set TableName. Set Active = True.
Drop TDataSource onto the Data Module. Set DataSet to the TADOTable.
On your main form: add the Data Module name to the uses clause at the top of your unit.
Drop TDBGrid onto your main form. Set its DataSource to the DataSource you created in the Data Module.
The grid should now show your database records!

Naming convention

Name components so you know what they are at a glance:
conStudents (TADOConnection) • tblStudents (TADOTable) • dsStudents (TDataSource) • dbgStudents (TDBGrid)

Accessing Field Values in Code

Once connected, you can read the value of any field in the current record using this syntax:

Syntax

variable := DataModuleName.TableName['FieldName'];

Delphi — examples

sName    := dm.tblStudents['Name'];        // reads the Name field
sSurname := dm.tblStudents['Surname'];    // reads the Surname field
iGrade   := dm.tblStudents['Grade'];      // reads the Grade field

// Display in a label
lblInfo.Caption := dm.tblStudents['Name'] + ' ' + dm.tblStudents['Surname'];

Navigating Records

Think of records as pages in a book. These methods let you turn pages:

Method	What it does
`tblStudents.First`	Jump to the very first record
`tblStudents.Last`	Jump to the last record
`tblStudents.Next`	Move forward one record
`tblStudents.Prior`	Move back one record
`tblStudents.Eof`	Returns True when you have gone past the last record (End Of File). Use in a WHILE loop to process all records.

Delphi — loop through ALL records

dm.tblStudents.First;                        // start at record 1
while not dm.tblStudents.Eof do           // keep going until past last
begin
  memOut.Lines.Add(dm.tblStudents['Name']); // do something with this record
  dm.tblStudents.Next;                       // move to next record
end;

Never forget .Next inside the loop!

If you forget tblStudents.Next inside the loop, the program will be stuck on the first record forever — an infinite loop that will crash Delphi.

Searching for a Record

To find a specific record, loop through all records and check each one:

Delphi — find a student by surname

dm.tblStudents.First;
while not dm.tblStudents.Eof do
begin
  if dm.tblStudents['Surname'] = edtSearch.Text then
  begin
    ShowMessage('Found: ' + dm.tblStudents['Name']);
    Break;   // stop searching once found
  end;
  dm.tblStudents.Next;
end;

Inserting a New Record

Adding a new record is a 3-step process: prepare → fill in values → save.

Delphi

dm.tblStudents.Insert;                    // Step 1: prepare a blank new record
dm.tblStudents['Name']    := edtName.Text;     // Step 2: fill in the fields
dm.tblStudents['Surname'] := edtSurname.Text;
dm.tblStudents['Grade']   := sedGrade.Value;
dm.tblStudents.Post;                      // Step 3: save to the database

Editing an Existing Record

Delphi

// First navigate to the record you want to change, then:
dm.tblStudents.Edit;                      // Step 1: put the record into edit mode
dm.tblStudents['Grade'] := 12;            // Step 2: change the value
dm.tblStudents.Post;                      // Step 3: save the change

Deleting a Record

Delphi

// Navigate to the record first, then:
dm.tblStudents.Delete;   // removes the current record permanently

Deletion is permanent

There is no undo. Always confirm with the user before deleting: if MessageDlg('Delete this record?', mtConfirmation, mbYesNo, 0) = mrYes then dm.tblStudents.Delete;

Sorting Records

Delphi

dm.tblStudents.Sort := 'Surname ASC';         // A to Z by surname
dm.tblStudents.Sort := 'Mark DESC';           // highest mark first
dm.tblStudents.Sort := 'Grade ASC, Surname ASC'; // by grade, then surname

Complete Example — Finding the Highest Mark

Delphi — find highest mark in tblStudents

var
  iHighest : Integer;
  sTopName : String;
begin
  iHighest := 0;
  sTopName := '';
  dm.tblStudents.First;
  while not dm.tblStudents.Eof do
  begin
    if dm.tblStudents['Mark'] > iHighest then
    begin
      iHighest := dm.tblStudents['Mark'];
      sTopName := dm.tblStudents['Name'];
    end;
    dm.tblStudents.Next;
  end;
  lblResult.Caption := sTopName + ' scored the highest: ' + IntToStr(iHighest);
end;

Connecting a Database Dynamically (in Code)

Everything above used components dropped onto a Data Module at design time. You can also build the whole connection at runtime with code — no components needed. This is called a dynamic connection, and it is useful when the database path is only known while the program runs (e.g. the user browses to the file).

Add these units to uses

Dynamic database objects live in two units. Add ADODB and DB to the uses clause before you can create them in code.

Declare the three objects (usually under public in the Data Module, or in the form):

Delphi — declaration

public
  conDB  : TADOConnection;
  tblStudents : TADOTable;
  dsStudents  : TDataSource;

Create and wire them together — build the connection string, point the table at the connection, then link the DataSource:

Delphi — create the connection in code

procedure TdmData.DataModuleCreate(Sender: TObject);
begin
  // 1. Create the connection object
  conDB := TADOConnection.Create(Self);
  conDB.LoginPrompt := False;        // don't ask for a password
  conDB.ConnectionString :=
    'Provider=Microsoft.ACE.OLEDB.12.0;' +
    'Data Source=' + ExtractFilePath(Application.ExeName) + 'Students.accdb;';
  conDB.Connected := True;

  // 2. Create the table and link it to the connection
  tblStudents := TADOTable.Create(Self);
  tblStudents.Connection := conDB;
  tblStudents.TableName  := 'tblStudents';
  tblStudents.Active      := True;

  // 3. Create the DataSource and link it to the table
  dsStudents := TDataSource.Create(Self);
  dsStudents.DataSet := tblStudents;
end;

Finally, link your on-screen TDBGrid to the DataSource. Do this in the main form's OnShow event:

Delphi — link the grid (in FormShow)

procedure TfrmMain.FormShow(Sender: TObject);
begin
  dbgStudents.DataSource := dmData.dsStudents;
end;

Why FormShow and not FormCreate?

The Data Module is usually created after the main form. If you try to link the grid in the main form's OnCreate event, the Data Module does not exist yet and you get an access violation error. Use OnShow, which fires later, once everything exists.

Older .mdb files

For older Access .mdb databases the provider is Microsoft Jet 4.0 OLE DB Provider (Provider=Microsoft.Jet.OLEDB.4.0;). Newer .accdb files use the ACE provider shown above.

← PreviousProcedures & Functions Next (Grade 12) →SQL