MS Access

This page explains how to use a Microsoft Access database using ODBC and FPC's built-in SQLDB. It is limited to Windows (or perhaps the Wine layer running on *nix).

Databases portal

References: General info Libraries Field types Controls FAQ SQL how-to Working With TSQLQuery In-memory database applications Tutorials/practical articles: Overview 0 - Database set-up 1 - Getting started 2 - Editing 3 - Queries 4 - Data modules SQLdb Programming Reference Databases Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

MS Access specifics

In the TSQLQuery' properties, you may need to set Set UsePrimaryKeyAsKey: False November 2012: to do: verify if this is needed

ODBC drivers

There are 2 different ODBC drivers:

• the old driver that can only use .mdb format files, driver name is "Microsoft Access Driver (*.mdb)". Included in many Windows versions (since Windows 2000 up to and including Vista?); downloadable for older Windows versions as part of the MDAC components or Jet components. 32 bit only.
• the new driver that can access both .mdb and .accdb files, driver name is "Microsoft Access Driver (*.mdb, *.accdb)". Downloadable as the "Microsoft Access Database Engine 2010 Redistributable"; available as 32 and 64 bit.

As usual, when using 32 bit Lazarus/FPC, use 32 bit ODBC drivers. When using 64 bit Lazarus/FPC, use 64 bit ODBC drivers.

File-based DSN Instructions

A file DSN is simply where the connection settings are written to a file. The reason for having a file DSN is if you want to distribute a data source connection to multiple users on different systems without having to configure a DSN for each system. For instance, I can create a file DSN to a reporting database on my desktop. I can then send the file to my users. My users can save the file DSN to their hard drives and then point their reporting applications at the file DSN.

If you want to use a file-based DSN with the SQLDB ODBC driver:

Set up the File DSN

• Go to your [Data sources (ODBC)] at the control panel administrative tools.
• Go to [File DSN] tab menu, Click Add, then Select <microsoft access driver>.
• Next, browse to your current Lazarus project path to save your .DSN file, because that dsn file will contain the configuration needed to access your database file (.mdb).
• Click Next, then Finish (you now have created a new .dsn file) of which will be use in your TODBCConnection [FileDSN].

File DSN contents

For reference, a file DSN that refers to an MS Access database may look something like this:

[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=
DBQ=YOUR_msaccess.mdb

Configure your project to use file-based DSN

Usage of grids and other data bound controls is the same as for any other SQLDB connector, so that is not covered here.

As you're using an ODBC connection, you should have a TODBCConnection object.

TOBDCConnection Properties: FileDSN: the path+filename to the DSN file saved earlier, e.g. c:\mylazarus\project1\myFile.dsn Username: admin (or whatever username you need if you are using Access security) this parameter may not be needed if no MS Access security is used Do not put any other entries in the properties.

System/User DSN

As explained in the ODBC article, you can also use system or user DSNs, where connection settings are defined in the ODBC control panel instead of stored in a file.

The TODBCConnection should have DatabaseName: <name_of_your_DSN>

DSN-less connection

As explained in the ODBC article, you can also create a DSN-less connection to your Access database where you can specify all connection parameters in code, something like:

  //Notice we're using the new MS Access driver, we could probably use the old one too:
  conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
  ... add whatever parameters you want/need...

Example code

Working Source code sample:(Updated2_with_add_delete_update) http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip

unit Unit1; 

{$mode objfpc}{$H+}

interface

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

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Datasource1: TDatasource;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    DBGrid1: TDBGrid;
    ODBCConnection1: TODBCConnection;
    PrintDialog1: TPrintDialog;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
    procedure FormShow(Sender: TObject);
  private
    { private declarations }
    procedure PrintDbGrid(dbGrid:TdbGrid);
  public
    { public declarations }
  end; 

var
  Form1: TForm1; 

implementation

{ TForm1 }

procedure TForm1.FormShow(Sender: TObject);
begin
  //connection
  ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
  ODBCConnection1.Connected := True;
  ODBCConnection1.KeepConnection := True;

  //transaction
  SQLTransaction1.DataBase := ODBCConnection1;
  SQLTransaction1.Action := caCommit;
  SQLTransaction1.Active := True; //not applied to ms access(false)
  //SQLTransaction1.StartTransaction; //removed ONLY for lazarus-0.9.30.0-fcl-2.4.2

  SQLQuery1.DataBase := ODBCConnection1;
  SQLQuery1.UsePrimaryKeyAsKey := False;

  SQLQuery1.SQL.Text := 'select * from table1';



  //T.H.I.S  SQL String implementation is (ONLY) for MS_ACCESS
  //:id is the fieldname id
  SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
  //:name is the fieldname name
  SQLQuery1.updateSQL.Text := 'update table1 set name=:name where id=:id';



  DataSource1.DataSet := SQLQuery1;
  DBGrid1.DataSource := DataSource1;
  DBGrid1.ReadOnly := true;

  DBEdit1.DataField := 'id';
  DBEdit1.DataSource := DataSource1;
  DBEdit2.DataField := 'name';
  DBEdit2.DataSource := DataSource1;

  SQLQuery1.Open;
end;

function MulDiv(nNumber, nNumerator, nDenominator: Integer): Integer;
begin
  Result:=Round(int64(nNumber)*int64(nNumerator)/nDenominator);
end;
procedure TForm1.PrintDbGrid(dbGrid:TdbGrid);
const
  LeftMargin = 0.05;
  TopMargin = 0.05;
  BottomMargin = 0.05;
var
  i: integer;
  x,y: integer;
begin
  if PrintDialog1.Execute then
    begin
    Printer.BeginDoc;
    Printer.Canvas.Font.Size := 12;

    y := Round(TopMargin*Printer.PageHeight);
    dbGrid.DataSource.DataSet.First;
    while not dbGrid.DataSource.DataSet.Eof do
      begin
      x := Round(LeftMargin*Printer.PageWidth);
      for i := 0 to dbGrid.DataSource.DataSet.FieldCount-1 do
        begin
        printer.Canvas.TextOut(x,y,dbGrid.DataSource.DataSet.Fields[i].AsString);
        x := x + MulDiv(dbGrid.Columns[i].Width,72, dbGrid.Width);
        end;
      dbGrid.DataSource.DataSet.Next;
      y := y + printer.Canvas.TextHeight('A');
      if y > (1-TopMargin-BottomMargin)* Printer.PageHeight then
        begin
        y := Round(TopMargin*Printer.PageHeight);
        Printer.NewPage;
        end;
      end;
    Printer.EndDoc;
    end
    else
    Form1.caption := 'NO PRINTER INSTALLED';
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
PrintDbGrid(DBGrid1);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
if button2.Caption = 'new' then
begin
  SQLQuery1.Insert;
  button2.Caption := 'save';
  exit
  end
  else
  begin
    if (dbedit1.Text = '') or (dbedit2.Text = '')
    then
    begin
    SQLQuery1.Cancel;
    end
    else
    begin
    if SQLQuery1.State = dsInsert then
       begin
       SQLQuery1.Post;
       SQLQuery1.ApplyUpdates;
       Form1.caption := 'ADDED';
       end;
    end;
  end;
button2.Caption := 'new';
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
if SQLQuery1.RecordCount>0 then
  begin
  SQLQuery1.Delete;
  SQLQuery1.ApplyUpdates;

  '//REMOVED or ADD this 2 lines if there is a problem in your SQLdB
  '//THIS 2 lines are =**BUG**= of SQLdB for MS_Access dataBase'
  //SQLQuery1.Close;
  //SQLQuery1.Open;
  '//We don't need this 2 line's in other data models, like mySQL, sqLiTE, etc.,...'


  Form1.caption := 'DELETED';
  end;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  if SQLQuery1.RecordCount>0 then
  begin
   SQLQuery1.Edit;
   SQLQuery1.Post;
   Sqlquery1.ApplyUpdates;
   Form1.caption := 'UPDATED';
   end;
end;

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: boolean);
begin
    SQLQuery1.Close;
    CanClose := True;
end;

initialization
  {$I unit1.lrs}

end.