MS Access/ja
│
English (en) │
español (es) │
français (fr) │
日本語 (ja) │
русский (ru) │
参照: チュートリアル/練習となる記事: 各種データベース |
このページは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月時点でこのリンクは切れている]
以下も参照のこと
- ODBCConn/ja The ODBC connector this article uses