[ 631_ADO で Access 2007 用の JET データベース ]

Delphi Programming / Object Pascal

[掲載 2010年06月03日] [更新 2011年09月11日]　Delphi サンプルプログラム集

631_ADO で Access 2007 用の JET データベース
動作確認等	Windows 7 U64(SP1) + Delphi XE(UP1) Pro + Msjet40.dll 4.00.9756.0

631_ADO_JET2007.zip　[2,445 KB]　2010年06月03日版 (EXE 同梱)

ADO で Access 2007 用の JET データベース
Microsoft Access への接続
01_Access 2007 用の JET データベースを新規作成
02_Excel 2007 からデータをインポート
03_エクセルへデータをエクスポート

ADO で Access 2007 用の JET データベース

Access 2007 用の JET データベースを扱うには，接続文字列のプロバイダの指定を次のように変更します．

  Microsoft.Jet.OLEDB.4.0 を
  Microsoft Office 12.0 Access Database Engine OLE DB Provider に変更

ただし，上のプロバイダの指定は，Microsoft Office 2007 がインストールされていないと利用できません．Microsoft Office 2007 がインストールされていない環境では，以下のリンクからファイルをダウンロードしてインストールします．

2007 Office system ドライバ: データ接続コンポーネント

このページのサンプルはこれを使用した例です．エクセルからのインポートとエクスポートのサンプルだけですが，これ以外は他の ADO 関係のサンプルコードと同じように扱えます．

[備考]
以下の記事で，Access 2010 用の JET での Excel の接続の説明とサンプルを掲載していますが，接続文字列が Access 2007 の JET と Access 2010 の JET とでは違います．Access 2010 用の JET であれば，Excel 2007 も扱えます．

[590] [04_Microsoft Access データベースエンジン 2010 を使用した接続]

図1
Access 2007 JET DB 用

以下は，サンプルプログラム集の ADO 関係の記事へのリンクです．

Microsfot Access への接続

当サイトの ADO 関係のサンプルコードでは Microsoft Access というアプリケーションへの接続や操作はできません．[Servers] タブのコンポーネント類を使用してください．
当サイトには Access (アクセス) への接続・操作のサンプルはありません．今後も掲載する予定はありません．Word (ワード) と Excel (エクセル) の接続と操作関係のサンプルはあります．

参考リンク
[Servers タブのコンポーネント類]

[Servers] にある Microsoft Access 関係コンポーネント類

01_Access 2007 用の JET データベースを新規作成

Access 2007 用の JET データベースを新規作成します．このサンプルで作成したJETデータベースは，Access 2007 よりも前の Access では扱うことはできません．
このサンプルで使用している ADOX オブジェクトについては，[630_ADO で JET DB の新規作成と最適化] の記事を参考にしてください．

参考リンク
[630_ADO で JET データベースの新規作成と最適化]

	図2 設計時画面
	図3 実行結果
	図4 作成された JET DB ファイル

リスト1
Access 2007 用の JET データベースを作成

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB, Buttons, ExtCtrls, ADOX_TLB;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    Panel1: TPanel;
    btnCreate: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnCreateClick(Sender: TObject);
  private
    { Private 宣言 }
    FDatabaseName : String;
    FTableName    : String;
    function MakeConnectString(DBName, APass: String): String;
    function CreateTable : Boolean;
    procedure OpenJETDBTable;
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

uses ComObj;

{$R *.dfm}

//=============================================================================
//  データベース切断
//=============================================================================
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOConnection1.Connected := False;
end;

//=============================================================================
//  JETデータベースが存在して表があればそれを開いてTDBGridに表示
//  なければデータベースと表を作成してから開く
//
//  オブジェクト・インスペクタの設定は以下の通り
//  ADOConnection1  デフォルトのまま
//  ADOQuery1       Connection := ADOConnection1;
//  DataSource1     DataSet    := ADOQuery1;
//  DBGrid1         DataSource := DataSource1;
//=============================================================================
procedure TForm1.btnCreateClick(Sender: TObject);
var
  LUserName   : String;
  LPassWord   : String;
  LTableList  : TStringList;
  LConnectStr : String;
  LJETCatalog : ADOX_TLB.Catalog;
begin
  btnCreate.Enabled := False;

  //ユーザ名とパスワード
  LUserName := 'Mr.XRAY';
  LPassWord := '123';

  //データベース名と表名
  //作成するデータベース(ファイル)名はTEST2007.mdbとする
  //場所はこのプロジェクトのルート
  FDatabaseName := '..\TEST2007.mdb';
  FTableName    := '食品カロリ表';

  //既にデータベースのファイルがある場合
  if FileExists(FDatabaseName) then begin
    ADOConnection1.Connected   := False;
    ADOConnection1.Mode        := cmReadWrite;
    ADOConnection1.LoginPrompt := False;

    //接続文字列を作成して接続
    LConnectStr                     := MakeConnectString(FDatabaseName, LPassWord);
    ADOConnection1.ConnectionString := LConnectStr;
    ADOConnection1.Connected        := True;

    LTableList := TStringList.Create;
    try
      //表の一覧をTableListに取得
      ADOConnection1.GetTableNames(LTableList);
      //表がなければ作成して開く
      if LTableList.IndexOf(FTableName) >= 0 then begin
        OpenJETDBTable;
      end else begin
        if CreateTable then OpenJETDBTable;
      end;
    finally
      FreeAndNil(LTableList);
    end;

  //データベースが存在しない場合は新規作成
  end else begin
    //ADOXのCatalogオブジェクトを生成
    LJETCatalog := ADOX_TLB.CoCatalog.Create;
    try
      ADOConnection1.Connected   := False;
      ADOConnection1.Mode        := cmReadWrite;
      ADOConnection1.LoginPrompt := False;

      //接続文字列
      LConnectStr := MakeConnectString(FDatabaseName,LPassWord);

      //データベース作成
      LJETCatalog.Create(LConnectStr);

      //接続文字列を設定して接続
      ADOConnection1.ConnectionString := LConnectStr;
      ADOConnection1.Connected        := True;

      //表を作成して開く
      CreateTable;
      if CreateTable then OpenJETDBTable;
    finally
      LJETCatalog := nil;
    end;
  end;

  btnCreate.Enabled  := True;
end;

//=============================================================================
//  接続用の文字列を生成
//  パスワードなしのMDBにも使用可能(パスワードの文字列を空文字にする)
//  ユーザ名の設定は無効なので設定していない
//
//  1行目から順に
//  プロバイダ(Access2007用のJETデータベース作成の設定)
//     この指定でないと，Access2007のJETデータベースのデータとExcel2007のデー
//     タは扱えない
//  データベース名
//  OLE DBプロバイダ接続でパスワードが必要な場合の設定
//  パスワード
//=============================================================================
function TForm1.MakeConnectString(DBName, APass: String): String;
begin
  Result := 'Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider'
          + ';Data Source=' + DBName
          + ';Jet OLEDB:Database '
          + 'Password=' + APass;
end;

//=============================================================================
//  食品カロリ表という名前の表を作成
//  同名の表があれば削除して新規に作成
//=============================================================================
function TForm1.CreateTable : Boolean;
var
  TableList : TStringList;
begin
  Result := False;

  TableList := TStringList.Create;
  try
    //トランザクション開始
    ADOConnection1.BeginTrans;
    try
      ADOQuery1.Close;

      ADOConnection1.GetTableNames(TableList);
      //表は既にあるのでその表を削除
      if TableList.IndexOf(FTableName)  >= 0 then begin
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add ('DROP TABLE ' + FTableName );
        ADOQuery1.ExecSQL;
      end;

      //表作成.項目番号Primaryインデックスとする
      ADOQuery1.SQL.Clear;
      ADOQuery1.SQL.Add ('CREATE TABLE ' + FTableName );
      ADOQuery1.SQL.Add ('( ');
      ADOQuery1.SQL.Add (' 番号   INT CONSTRAINT IDX_NO PRIMARY KEY, ');
      ADOQuery1.SQL.Add (' 食品群 VARCHAR(30), ');
      ADOQuery1.SQL.Add (' 食品名 VARCHAR(30), ');
      ADOQuery1.SQL.Add (' キロカロリ INT ');
      ADOQuery1.SQL.Add (') ');
      ADOQuery1.ExecSQL;

      //項目食品名にインデックスを作成
      ADOQuery1.SQL.Clear;
      ADOQuery1.SQL.Add ('CREATE INDEX IDX_SYOKUHIN ON ' + FTableName);
      ADOQuery1.SQL.Add (' (食品名) ');
      ADOQuery1.ExecSQL;

      //コミット
      ADOConnection1.CommitTrans;
      Result := True;
    except
      //失敗したら元に戻す(ロールバック)
      ADOConnection1.RollbackTrans;
      MessageDlg('表の作成に失敗しました．', mtWarning, [mbOK], 0);
    end;
  finally
    FreeAndNil(TableList);
  end;
end;

//=============================================================================
//  表を開くクエリーを実行
//  DBGrid1にはあらかじめカラムを作成しておく
//=============================================================================
procedure TForm1.OpenJETDBTable;
begin
  ADOQuery1.DisableControls;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT * FROM ' + FTableName);
  ADOQuery1.Open;

  ADOQuery1.EnableControls;

  //DBGridへの表示を設定
  DBGrid1.Columns[0].FieldName := '番号';
  DBGrid1.Columns[1].FieldName := '食品群';
  DBGrid1.Columns[2].FieldName := '食品名';
  DBGrid1.Columns[3].FieldName := 'キロカロリ';

  DBGrid1.Columns[0].Width := 50;
  DBGrid1.Columns[1].Width := 100;
  DBGrid1.Columns[2].Width := 100;
  DBGrid1.Columns[3].Width := 60;
end;

end.

02_Excel 2007 からデータをインポート

前のサンプルで作成した JET データベースの表に Excel 2007 からデータをインポートします．

	図5 設計時画面
	図6 インポートして確認表示

リスト2
Excel 2007 からデータをインポート

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB, ExtCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    Panel1: TPanel;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//=============================================================================
//  FormCreate時に各種初期設定
//  ADOConnecton1,ADOQuery1はFormに配置しデフォルトの状態で使用
//  それ以外の設定はこのサンプルコード内で行っている
//=============================================================================
procedure TForm1.FormCreate(Sender: TObject);
var
  LPassword : String;
  LProvider : String;
begin
  LPassword := '123';
  LProvider := 'Microsoft Office 12.0 Access Database Engine OLE DB Provider';

  ADOConnection1.Connected      := False;
  ADOConnection1.KeepConnection := False;
  ADOConnection1.LoginPrompt    := False;
  ADOConnection1.Mode           := cmReadWrite;
  ADOConnection1.Provider       := LProvider;

  ADOConnection1.ConnectionString := 'Data Source=../TEST2007.mdb'
                                   + ';Jet OLEDB:Database '
                                   + 'Password=' + LPassword;

  ADOQuery1.Connection := ADOConnection1;
end;

//=============================================================================
//  フォームを閉じたら接続を切断
//=============================================================================
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOConnection1.Connected := False;
end;

//=============================================================================
//  対象となるmdbの該当テーブルのデータを全て削除
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
begin
  Screen.Cursor := crHourGlass;

  //mdbの該当テーブルのデータを全て削除
  //テーブル(表)を削除するわけではない
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('DELETE FROM 食品カロリ表 ');
  ADOQuery1.ExecSQL;

  Screen.Cursor := crDefault;
end;

//=============================================================================
//  Excel(シート)からJET DBの表にインポート
//
//  エクセルのシートのデータを，JETデータベースの表に新規追加していく
//  データベースの表には追加する項目があらかじめ用意されていなければならない
//  表のデータを全て削除して行っているのは重複データを避けるため
//
//  このサンプルではOLE DBプロバイダに
//  Microsoft Office 12.0 Access Database Engine OLE DB Provider
//  を使用しているので，Excel2007より前のブックは扱えない
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
var
  LExcelPath : String;
  LstrInto   : String;
  LstrSelect : String;
  LstrFrom   : String;
begin
  Screen.Cursor := crHourGlass;

  //テーブルのデータを全て削除
  Button1Click(nil);

  //対象のExcelのブック
  LExcelPath := '../ADOTestIN2007.Xlsx';

  //SQL文の作成
  LstrInto   := 'INSERT INTO 食品カロリ表 (番号, 食品群, 食品名, キロカロリ) ';
  LstrSelect := 'SELECT [Sheet1$].番号,'
              + '       [Sheet1$].食品群,'
              + '       [Sheet1$].食品名,'
              + '       [Sheet1$].キロカロリ ';
  LstrFrom := 'FROM [Sheet1$] IN ' + AnsiQuotedStr(LExcelPath, '"') + ' '
                                   + AnsiQuotedStr('Excel 12.0;', '"');
  try
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add(LstrInto);
    ADOQuery1.SQL.Add(LstrSelect);
    ADOQuery1.SQL.Add(LstrFrom);
    ADOQuery1.ExecSQL;
  except
    raise;
  end;
  Screen.Cursor := crDefault;
end;

//=============================================================================
//  JETデータベースのテーブルを開いてDBGridに表示
//  インポートする前は，現在の表のデータが表示される
//=============================================================================
procedure TForm1.Button3Click(Sender: TObject);
begin
  Screen.Cursor := crHourGlass;
  ADOQuery1.DisableControls;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT * FROM 食品カロリ表');
  ADOQuery1.Open;

  ADOQuery1.EnableControls;
  Screen.Cursor := crDefault;
end;

end.

03_エクセルへデータをエクスポート

上のサンプルで JET データベースに取込んだデータを，エクセルのブックを新規作成して，そのブックに出力します．
本サンプルでは，Excel 2007 形式のブックには出力できません．Excel 97 - 2003 までの形式となりますが，Excel 2007 でも表示できます．上のサンプルコードのエクセルの指定部分と以下のリンクの記事を参考にしてください．

参考リンク
[590] [04_Microsoft Access データベースエンジン 2010 を使用した接続]

	図7 設計時画面
	図8 作成されたエクセルブック
	図9 そのデータ

リスト3
Access 2007 の JET データベースのデータをエクセルへ出力

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB, ExtCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//=============================================================================
//  FormCreate時に各種初期設定
//  ADOConnecton1,ADOQuery1はFormに配置しデフォルトの状態で使用
//  それ以外の設定はこのサンプルコード内で行っている
//=============================================================================
procedure TForm1.FormCreate(Sender: TObject);
var
  LPassword : String;
  LProvider : String;
begin
  LPassword := '123';
  LProvider := 'Microsoft Office 12.0 Access Database Engine OLE DB Provider';

  ADOConnection1.Connected      := False;
  ADOConnection1.KeepConnection := False;
  ADOConnection1.LoginPrompt    := False;
  ADOConnection1.Mode           := cmReadWrite;
  ADOConnection1.Provider       := LProvider;

  ADOConnection1.ConnectionString := 'Data Source=../TEST2007.mdb'
                                   + ';Jet OLEDB:Database '
                                   + 'Password=' + LPassword;

  ADOQuery1.Connection := ADOConnection1;
end;

//=============================================================================
//  フォームを閉じたら接続を切断
//=============================================================================
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  ADOConnection1.Connected := False;
end;

//=============================================================================
//  JETデータベース表データをExcel(シート)へエクスポート
//
//  このサンプルではOLE DBプロバイダに
//  Microsoft Office 12.0 Access Database Engine OLE DB Provider
//  を使用しているが，エクスポートはこのコードの指定でExcel97-2003のブック形式
//  で出力しないと，Excel2007では現在のところ開けないようだ
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
var
  LExcelPath : String;
  LstrSelect : String;
  LstrFrom   : String;
begin
  Screen.Cursor := crHourGlass;

  //対象のExcelのブック
  LExcelPath :='../ADOTestOUT2007.Xls';

  //既に同名のファイルが存在する場合はエラーとなるので予め削除
  if FileExists(LExcelPath) then DeleteFile(LExcelPath);

  //SQL文の作成
  //ここではSheet1に出力
  //必要であれば*部分に取得したい項目名を並べる
  LstrSelect := 'SELECT * INTO [Sheet1] IN ' + AnsiQuotedStr(LExcelPath, '"') + ' '
                                             + AnsiQuotedStr('Excel 8.0;', '"');
  LstrFrom := 'FROM 食品カロリ表 ORDER by キロカロリ ';

  try
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add(LstrSelect);
    ADOQuery1.SQL.Add(LstrFrom);
    ADOQuery1.ExecSQL;
  except
    raise;
  end;
  Screen.Cursor := crDefault;
end;

end.