MS Access/ja

This is an old revision of MS Access/ja, edited by Ariben at . The current revision is 149401.

ja|ODBC]]で説明したとおり、DSNレスの接続はAccessデータベースに対するすべての接続パラメータを特定する記述することにより可能となる。それはこのようなものになるだろう: <syntaxhighlight lang=pascal> //注意: ここでは新しいMS Accessドライバを用いている。おそらく古いものも使えるだろう: conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)'; conn.Params.Add('DBQ=c:\somedirectory\test.mdb'); ... 望む・

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

このページはODBCとFPCのビルトイン、SQLDBを用いてどのようにして Microsoft Access データベースを用いるか解説する。

MS Access 固有事項

プライマリキーをキーとして用いる

TSQLQuery プロパティでは、Set UsePrimaryKeyAsKey: Falseと設定しなければならないだろう

オートナンバープライマリキーを挿入させる

データを挿入した後で、Access 2000とより新しいバージョンはこのクエリを用いて、適時挿入のオートナンバープライマリキーの挿入をサポートしている:

SELECT @@IDENTITY

ODBC ドライバ

Windows

2つの異なる ODBC ドライバがMicrosoft Accessにある:

Unix/Linux

The [mdbtools project] はMS Accesssに限定的なサポートを提供している。おそらく使うことのできるODBCドライバが含まれている。Mdbtools、mdbtools-dev、mdbtools-gmdbパッケージをインストールしてみること。

少なくともDebianではODBCドライバ名は"MDBTools"である。

SQLクエリにおけるワイルドカード

Microsoft Accessは他のデータベースシステムと比較して異なったワイルドカード文法を持っている。以下の情報は"Intermediate Microsoft Jet SQL for Access 2000" [MSDN]で取得したもので、これはもはやここにはない(第3者のサイトにコピーがある)。機能するようにテストされている。

ワイルドカード文字 摘要
% (パーセント) どの文字のどの数ともマッチし、パターン文字列でどこでも使える。
_ (アンダースコア) どの1文字にもマッチし、パターン文字列でどこでも使える。
[] (角括弧) 括弧の中に含まれたリストの1文字にマッチし、パターン文字列でどこでも使える。
! (エクスクラメーション) 角括弧に含まれたリストにないどの1文字にマッチする。
- (ハイフン) 角括弧に含まれた範囲のどの文字列にもマッチする。

以下に注意

例: SELECT * FROM [Birthday Table] WHERE ([Birth date] >= #2000-01-01#)

ファイルベースのDSN 要領

ファイルDSNは単に接続設定がファイルに書かれた単純なものである。ファイルDSNを持つ理由は、もしもしデータソース接続を多くの異なるシステムのユーザーに配布したいときに、ファイルDSNは各々のシステムでDSNの設定をしなくてもするためである。 例えば、デスクトップにファイルDSNをデータベースの所在を報告するために作ったとする。そうするとユーザーにそのファイルを送ることができる。ユーザーはそのファイルDSNをハードディスクに保存しファイルDSNで示された場所を特定することができる。

ファイルベースのDSNをSQLDBドライバで使いたいときは:

ファイルDSNの設定

ファイル DSNの内部

例えば、MS Accessデータベースを参照するファイル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=
DBQ=YOUR_msaccess.mdb

ファイルベースのDSNを設定する

グリッドと他の埋め込みコントロールの使用法は他のSQLDBコネクタと同じであるので、ここでは割愛する。

ODBC接続を使うときには、TODBCConnectionオブジェクトが必要である。

TOBDCConnection プロパティ:

ファイルDSN: 先に保存したDSNファイルに対するpath+ファイル名、例えばc:\mylazarus\project1\myFile.dsn Username: admin(もしくはAccessセキュリティを用いているなら何らかのユーザー名)、このパラメータはMS Accessセキュリティを用いていないならば必要ない。 他のプロパティには手を触れないこと。

システム/ユーザー DSN

ODBCで説明した通り、システム、またはユーザーDSNを用いることもでき、その場合、接続設定は保存されたファイルではなく、ODBCコントロールパネルで定義される。 TODBCConnectionは

DatabaseName: <name_of_your_DSN>

を持たなければならない。

DSNレス接続

ODBCで説明したとおり、DSNレスの接続はAccessデータベースに対するすべての接続パラメータを特定する記述することにより可能となる。それはこのようなものになるだろう:

  //注意: ここでは新しいMS Accessドライバを用いている。おそらく古いものも使えるだろう:
  conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
  ... 望む/必��記述を加�る...

これはTODBCConnectionを用いたAccess mdbのテーブルに対するクエリの仕方の例である、TSQLTransaction と TSQLQuery; mdbファイルはプロジェクトのバイナリと同じフォルダにある。

procedure TForm1.Button1Click(Sender: TObject);
begin
  //接続
  ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  ODBCConnection1.Params.Add('DBQ='+Application.Location+'test.mdb');      // 必要なmdbファイルの場所
  ODBCConnection1.Params.Add('Locale Identifier=1031');
  ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
  ODBCConnection1.Params.Add('CHARSET=ansi');
  ODBCConnection1.Connected := True;
  ODBCConnection1.KeepConnection := True;
     
  //トランザクション
  SQLTransaction1.DataBase := ODBCConnection1;
  SQLTransaction1.Action := caCommit;
  SQLTransaction1.Active := True;
     
  SQLQuery1.DataBase := ODBCConnection1;
  SQLQuery1.UsePrimaryKeyAsKey := False;
  SQLQuery1.SQL.Text := 'select * from Customers';
  SQLQuery1.Open;
end;

Example code

Lazarus: data bound controls on forms

Working Source code sample:(Updated2_with_add_delete_update) www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip [Dead link - November 2020]

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)

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

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

  // :id is the parameter for the field name id
  SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
  //:name is the parameter for the field name 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
  //THESE 2 lines apparently work around a bug in SQLdB for MS_Access dataBase'
  //**** missing reference to bugtracker item; please create a bugtracker item
  //SQLQuery1.Close;
  //SQLQuery1.Open;


  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.

Create a database programmatically

Using either ODBC driver, you can programmatically create Microsoft Access databases. The following code is valid for both 32-bit and 64-bit applications on Windows.

Example code:

uses
  ..., sysutils, odbcsqldyn;

const
   ODBC_ADD_DSN=1;
   { just for reference...
   ODBC_CONFIG_DSN=2;
   ODBC_REMOVE_DSN=3;
   ODBC_ADD_SYS_DSN=4;
   ODBC_CONFIG_SYS_DSN=5;
   ODBC_REMOVE_SYS_DSN=6;
   ODBC_REMOVE_DEFAULT_DSN=7; }

function SQLConfigDataSource(hwndParent: Integer; fRequest: Integer;
  lpszDriverString: PChar; lpszAttributes: PChar): Integer; stdcall;
  external 'odbccp32.dll';

function SQLInstallerError(iError: integer; pfErrorCode: PInteger;
  lpszErrorMsg: String; cbErrorMsgMax: integer; pcbErrorMsg: PInteger): integer; stdcall;
  external 'odbccp32.dll';

procedure CreateAccessDatabase(DatabaseFile: string);
var
  cmd: String;
  driver: String;
  errCode, errMsgLength: DWord;
  errMsg: String;
  retCode: integer;
  ext: String;
begin
  ext := Lowercase(ExtractFileExt(DatabaseFile));

  driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  { With this driver,
    CREATE_DBV12 will create an .accdb format database;
    CREATE_DBV4 will create an .mdb format database
    //stackoverflow.com/questions/9205633/how-do-i-specify-the-odbc-access-driver-format-when-creating-the-database
  }
  if ext = '.mdb' then
    cmd := 'CREATE_DBV4="' + DatabaseFile + '"'
  else if ext = '.accdb' then
    cmd := 'CREATE_DBV12="' + DatabaseFile + '"'
  else
    raise Exception.Create('Invalid database file extension.');

  retCode := SQLConfigDataSource(0, ODBC_ADD_DSN, PChar(driver), PChar(cmd));
  if not (retCode in [SQL_SUCCESS, SQL_SUCCESS_WITH_INFO]) then
  begin
    //try alternate driver
    driver := 'Microsoft Access Driver (*.mdb)';
    cmd := 'CREATE_DB="' + DatabaseFile + '"';
    retCode := SQLConfigDataSource(0, ODBC_ADD_DSN, PChar(driver), PChar(cmd));
  end;

  if not (retCode in [SQL_SUCCESS, SQL_SUCCESS_WITH_INFO]) then
  begin
    errCode := 0;
    errMsgLength := 0;
    SetLength(errMsg, SQL_MAX_MESSAGE_LENGTH);
    SQLInstallerError(1, @errCode, PChar(errMsg), Length(errMsg), @errMsgLength);
    SetLength(errMsg, errMsgLength);
    raise Exception.CreateFmt('Error creating Access database: %s', [errMsg]);
  end;
end;

Character set issues

Old Access databases are encoded using an ANSI character set (I don't know about new ones - they probably use wide strings). Therefore, non-ASCII characters are not displayed correctly in a DBGrid or other data-aware controls. This can be fixed by attaching handlers to the OnGetText and OnSetText events of the dataset after opening:

uses
  lconvencoding;
 
procedure TForm1.SQLQuery1AfterOpen(DataSet: TDataSet);
var
  i: Integer;  
begin
  for i:=0 to DataSet.Fields.Count-1 do
  begin
    if DataSet.Fields[i].DataType=ftString then
    begin
      DataSet.Fields[i].OnGetText := @ConvertFromDB;
      DataSet.Fields[i].OnSetText := @ConvertToDB;
    end;
  end;
end;
 
procedure TForm1.ConvertFromDB(Sender: TField; var aText: string; DisplayText: Boolean);
begin
  if not Sender.IsNull then
    aText := WinCPToUTF8(Sender.AsString);  // if encoded in windows default code page
end;
 
procedure TForm1.ConvertToDB(Sender: TField; const aText: string);
begin
  if aText <> '' then 
    Sender.Value := UTF8ToWinCP(aText);
end;

Example database

Microsoft Access ships with the Northwind example database.

If you only have the Access Runtime installed, you can use this alternative database from the Mondial project as a test database: [databases.about.com/od/sampleaccessdatabases/a/Microsoft-Access-Sample-Database-Countries-Cities-And-Provinces.htm] [Dead link - November 2020]

See also