MS Access/ja

From Free Pascal wiki
Jump to navigationJump to search

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ русский (ru) │

データベースのポータル

参照:

チュートリアル/練習となる記事:

各種データベース

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にある:

  • ドライバ名が"Microsoft Access Driver (*.mdb)"の.mdbフォーマットのみに使われる古いドライバ。Windows 2000以降(でVista?)の多くのWindowsに含まれる。MDACコンポーネントあるいはJetコンポーネントの一部としてダウンロードできる古いWindowsバージョンのためのもの。32 bitのみ。
  • ドライバの名前が"Microsoft Access Driver (*.mdb, *.accdb)"の.mdbと.accdbの双方にアクセスできる新しいドライバ。 "Microsoft Access Database Engine 2010 Redistributable"としてダウンロードできる 32 と 64 bit.普段通り、32 bit Lazarus/FPCを用いるときには32 bitODBCドライバを用いること。64 bit Lazarus/FPCを用いるときには64 bit ODBCドライバを用いること。

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文字にマッチする。
- (ハイフン) 角括弧に含まれた範囲のどの文字列にもマッチする。

以下に注意

  • AccessのWHERE 条件は丸カッコに入れなければならない。
  • 文字列はシングルクォートで囲う。
  • 日付は#yyyy-mm-dd# のようにあらわす(年-月-日の順で'#'で囲う)。
  • フィールドとテーブルの名前で通常禁止された文字は角括弧で囲うこと。

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

ファイルベースのDSN 要領

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

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

ファイルDSNの設定

  • コントロールパネルのWindowsツール、データソース [Data sources (ODBC)]に行き、
  • [File DSN] タブメニューを選び、 追加をクリック、そして、Microsoft Access Driver(*.mdb, *.accdb)を選ぶ
  • 次に今のLazarusプロジェクトバスを指し、DSNファイルに保存する。というのも、そのdnsファイルはAccessデータベースファイル(.mdb)にアクセスするのに必要な設定を含んでいるからだ。
  • 次へをクリックして終わる(これで新しい.dsnファイルができた)それはTODBCConnection [FileDSN]で用いることができる。

ファイル 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;

コード例

Lazarus: フォームにデータを埋め込む

実際に動くコード例:(Updated2_with_add_delete_update) www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip [このリンクは - 2020 11月時点では切れている] 

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
  //接続
  ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
  ODBCConnection1.Connected := True;
  ODBCConnection1.KeepConnection := True;

  //トランザクション
  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 はフィールド名 idのパラメータ
  SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
  //: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;

  //もしSQLdBで問題が生じたら、この2行を加える、あるいは除いてみる
  //これら2ラインはMS AccessデータベースのSQLdBのバグに対する手当である
  //**** bugtracker アイテムには見当たらない、bugtrackerを作ってほしい
  //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.

プログラム的にデータベースを生成する

ODBCドライバを用い、プログラム的にMicrosoft Accessデータベースを作ることができる。以下のコードはWindowsの32-bit、64-bitで実行可能である。

コード例: 

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)';
  { このドライバで
    CREATE_DBV12 は .accdbフォーマットデータベースができる
    CREATE_DBV4 は.mdbフォーマットデータベースができる
    //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
    //別のドライバを試す
    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;

文字集合問題

古いAccessデータベースはANSIキャラクタセットでエンコードされていた(新しいものに関してはわからない - おそらくより広い文字集合を使っているだろう)。そのため、非ASCIIキャラクタはDBGidや、他のデータ志向型コントロールで正しく表示されない。これはOnGetTextとOnSetTextをデータセットを開いたのちに適用すれば修正される可能性がある: 

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);  // もしWindowsのデフォルトコードページの場合
end;
 
procedure TForm1.ConvertToDB(Sender: TField; const aText: string);
begin
  if aText <> '' then 
    Sender.Value := UTF8ToWinCP(aText);
end;

作例データベース

Microsoft AccessにはNorthwindデータベースが付属する

もし、Access Runtimeがインストールされていれば、この別のデータベースをテストデータベースとして利用できる: 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] [2020年11月時点でこのリンクは切れている]

以下も参照のこと

Retrieved from "https://wiki.freepascal.org/index.php?title=MS_Access/ja&oldid=160572"