| 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にある:
- ドライバ名が"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を設定する
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...
Here is an example how to query a table in an Access mdb file using TODBCConnection, TSQLTransaction and TSQLQuery; the mdb file exists in the same folder as the project binary:
procedure TForm1.Button1Click(Sender: TObject);
begin
//connection
ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
ODBCConnection1.Params.Add('DBQ='+Application.Location+'test.mdb'); // path to mdb file required
ODBCConnection1.Params.Add('Locale Identifier=1031');
ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
ODBCConnection1.Params.Add('CHARSET=ansi');
ODBCConnection1.Connected := True;
ODBCConnection1.KeepConnection := True;
//transaction
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
- ODBCConn The ODBC connector this article uses