Updating a Microsoft Access Database using Lazarus

In this version of our DBGrid demonstration we show how you can display some fields in a DBGrid, and a field of the selected record in a DBEdit or a DBMemo. We downloaded the zip file from the Microsoft Access page in the Lazarus Wiki. We used some of this code for our buttons to edit a Microsoft Access table and to delete and insert a record. This builds on the previous section, where we displayed the contents of the table in components.mdb. These components are data-aware and so you just need to set the DataSource property to DataSource1 (the only data source in the drop down list in the Object Inspector). The New and Update buttons update the database with data edited in any of the three data-aware components. You need to be careful to write the SQL strings correctly otherwise you can edit whole columns in the table inadvertently. Always keep a backup of a valid database!

We copied the whole folder of our DatabaseDemo project, opened the project and saved it as DatabaseEdit.lpr. We saved uDatabaseDemo.pas as uDatabaseEdit.pas and chose the option to delete the original file. In the process, uDatabaseDemo.lfm was renamed as uDatabaseEdit.lfm. We then deleted the last two columns of the DBGrid by right clicking on the column name in the Object Inspector then clicking on the Delete option:

Deleting a column

Deleting a column

We added to the form the three buttons (from the Standard tab) and the DBEdit and the DBMemo (from the Data Controls tab). You can see the code for the buttons in uDatabaseEdit.pas and the properties of the components in uDatabaseEdit.lfm. We have emboldened important properties.

Program DatabaseEdit in action

Program DatabaseEdit in action

The essential code of the files needed for program DatabaseEdit follows. You can download the three code files, the database and our components.dsn file in form_database_edit.zip. You will probably need to change the path of the database. The Microsoft Access page in the Lazarus Wiki explains how to create your own .dsn file if ours does not work for you.

uDatabaseEdit.pas

unit uDatabaseEdit;

interface

uses
  Classes, SysUtils, db,  odbcconn, sqldb, FileUtil, Forms, Controls,
  Graphics, Dialogs, DBGrids, DbCtrls, StdCtrls;

type

  TfrmComponents = class(TForm)
    btnUpdate: TButton;
    btnNew: TButton;
    btnDelete: TButton;
    Datasource1: TDatasource;
    DBEdit1: TDBEdit;
    DBGrid1: TDBGrid;
    DBMemo1: TDBMemo;
    DBNavigator1: TDBNavigator;
    lblCode: TLabel;
    lblProps: TLabel;
    ODBCConnection1: TODBCConnection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure btnDeleteClick(Sender: TObject);
    procedure btnUpdateClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
  end;
var
  frmComponents: TfrmComponents;

implementation

{$R *.lfm}

procedure TfrmComponents.btnUpdateClick(Sender: TObject);
begin
  SQLQuery1.Edit;
  SQLQuery1.Post;
  Sqlquery1.ApplyUpdates;
end;

procedure TfrmComponents.btnDeleteClick(Sender: TObject);
begin
  if SQLQuery1.RecordCount > 0 then
    begin
      SQLQuery1.Delete;
      SQLQuery1.ApplyUpdates;
      SQLQuery1.Close;
      SQLQuery1.Open;
    end;
end;

procedure TfrmComponents.btnNewClick(Sender: TObject);
begin
  if btnNew.Caption = 'New' then
    begin
      SQLQuery1.Insert;
      btnNew.Caption := 'Save';
      exit;
    end
  else
    begin
      if (dbedit1.Text = '') or (dbmemo1.Text = '') or
         (SQLQuery1.FieldByName('Name').Text = '') or
         (SQLQuery1.FieldByName('Abbreviation').Text = '') or
         (SQLQuery1.FieldByName('Palette').Text = '') then
        begin
          SQLQuery1.Cancel;
          ShowMessage('Cancelled because of blank field');
        end
      else
        begin
          if SQLQuery1.State = dsInsert then
            begin
              SQLQuery1.Post;
              SQLQuery1.ApplyUpdates;
            end;
        end;
    end;
  btnNew.Caption := 'New';
end;

end.

uDatabaseEdit.lfm

object frmComponents: TfrmComponents
  Left = 493
  Height = 335
  Top = 275
  Width = 697
  Caption = 'Delphi Components'
  ClientHeight = 335
  ClientWidth = 697
  LCLVersion = '0.9.30'
  object DBGrid1: TDBGrid
    Left = 3
    Height = 256
    Top = 8
    Width = 352
    Color = clWindow
    Columns = <    
      item
        Title.Caption = 'Type'
        Title.Font.Style = [fsBold]
        Width = 100
        FieldName = 'Name'
      end    
      item
        Title.Caption = 'Prefix'
        Title.Font.Style = [fsBold]
        Width = 100
        FieldName = 'Abbreviation'
      end    
      item
        Title.Caption = 'Palette'
        Title.Font.Style = [fsBold]
        Width = 100
        FieldName = 'Palette'
      end>
    DataSource = Datasource1
    TabOrder = 0
  end
  object DBNavigator1: TDBNavigator
    Left = 56
    Height = 25
    Top = 272
    Width = 241
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 241
    DataSource = Datasource1
    TabOrder = 1
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbRefresh]
  end
  object btnUpdate: TButton
    Left = 8
    Height = 25
    Top = 304
    Width = 75
    Caption = 'Update'
    OnClick = btnUpdateClick
    TabOrder = 2
  end
  object DBMemo1: TDBMemo
    Left = 404
    Height = 64
    Top = 80
    Width = 276
    DataField = 'Codefragments'
    DataSource = Datasource1
    TabOrder = 3
  end
  object lblProps: TLabel
    Left = 398
    Height = 21
    Top = 6
    Width = 286
    Caption = 'Useful Properties of selected component'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object lblCode: TLabel
    Left = 398
    Height = 21
    Top = 56
    Width = 276
    Caption = 'Code fragment for selected component'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object DBEdit1: TDBEdit
    Left = 401
    Height = 28
    Top = 31
    Width = 280
    DataField = 'UsefulProperties'
    DataSource = Datasource1
    CharCase = ecNormal
    MaxLength = 256
    TabOrder = 4
  end
  object btnNew: TButton
    Left = 128
    Height = 22
    Top = 307
    Width = 64
    Caption = 'New'
    OnClick = btnNewClick
    TabOrder = 5
  end
  object btnDelete: TButton
    Left = 232
    Height = 25
    Top = 304
    Width = 75
    Caption = 'Delete'
    OnClick = btnDeleteClick
    TabOrder = 6
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 416
    top = 160
  end
  object ODBCConnection1: TODBCConnection
    Connected = True
    LoginPrompt = False
    KeepConnection = False
    Transaction = SQLTransaction1
    UserName = 'admin'
    FileDSN = 'E:\Database\components.dsn'
    left = 448
    top = 224
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caCommit
    Database = ODBCConnection1
    left = 584
    top = 176
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = ODBCConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
        'SELECT * FROM ComponentDetails'
    )
    UpdateSQL.Strings = (
      'Update ComponentDetails '
      'Set Abbreviation=:Abbreviation, '
      'Palette=:Palette, UsefulProperties=:UsefulProperties, Codefragments=:Codefragments '
      'where Name=:Name'
    )
    Params = <>
    UsePrimaryKeyAsKey = False
    left = 520
    top = 224
  end
end

DatabaseEdit.lpr

program DatabaseEdit;

uses
  Interfaces, Forms, uDatabaseEdit;

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TfrmComponents, frmComponents);
  Application.Run;
end.

components.dsn

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=E:\
DBQ=E:\components.mdb

Programming - a skill for life!

How to use a DBGrid to display and edit data in a database