MS Access/ja

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

数の

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を設定する

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