unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleServer, ADODB_TLB, Grids, StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Connection1: TConnection;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  LRecordSet  : ADODB_TLB.Recordset;
  LRcdAffectd : OleVariant;
  LStrConnect : WideString;
  LDBFile     : WideString;
  LUserID     : WideString;
  LPassword   : WideString;
  LStrSQL     : WideString;
  LDataText   : WideString;
begin
  ListBox1.Items.Clear;


  //データベースのファイル名
  LDBFile   := ExtractFilePath(Application.ExeName) + '住所録Data03.mdb';
  LUserID   := '';
  LPassword := '';

  //接続文字列を作成してデータベース接続
  LStrConnect := 'Provider=Microsoft.Jet.OLEDB.4.0'
               + ';Data Source=' + LDBFile;
  Connection1.Open(LStrConnect, LUserID, LPassword, Integer(adConnectUnspecified));

  try
    //描画の更新を停止
    ListBox1.Items.BeginUpdate;

    //SQL文を指定して実行
    LStrSQL    := 'SELECT * FROM ta住所録';
    LRecordSet := Connection1.Execute(LStrSQL, LRcdAffectd, 0);

    //指定の項目の値だけ取得して表示
    while not LRecordSet.EOF do begin
      LDataText := LRecordSet.Fields['名前'].Value;
      ListBox1.Items.Add(LDataText);
      LRecordSet.MoveNext;

      Application.ProcessMessages;
      Sleep(1);
    end;
  finally
    //描画の更新を再開
    ListBox1.Items.EndUpdate;
    //レコードセットとデータベースを閉じる
    LRecordSet.Close;
    Connection1.Close;
  end;
end;

end.

procedure TForm1.Button1Click(Sender: TObject);
var
  LRecordSet  : Recordset;
  LStrConnect : WideString;
  LDBFile     : WideString;
  LUserID     : WideString;
  LPassword   : WideString;
begin
  ListBox1.Items.Clear;

  //データベースのファイル名
  LDBFile   := ExtractFilePath(Application.ExeName) + '住所録Data03.mdb';
  LUserID   := '';
  LPassword := '';

  //接続文字列を作成してデータベース接続
  LStrConnect := 'Provider=Microsoft.Jet.OLEDB.4.0'
               + ';Data Source=' + LDBFile;
  Connection1.Open(LStrConnect, LUserID, LPassword, Integer(adConnectUnspecified));

  try
    //描画の更新を停止
    ListBox1.Items.BeginUpdate;

    //OpenSchemaでファイル情報のレコードセットを取得
    LRecordSet := Connection1.OpenSchema(adSchemaTables);
    while not LRecordSet.EOF do begin
      ListBox1.Items.Add(LRecordSet.Fields['TABLE_NAME'].Value);
      LRecordSet.MoveNext;
    end;
  finally
    //描画の更新を再開
    ListBox1.Items.EndUpdate;
    //レコードセットとデータベースを閉じる
    LRecordSet.Close;
    Connection1.Close;
  end;
end;

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleServer, ADODB_TLB, Grids, StdCtrls;

type
  TForm1 = class(TForm)
    Connection1: TConnection;
    Recordset1: TRecordset;
    Button1: TButton;
    Button2: TButton;
    StringGrid1: TStringGrid;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure FormCreate(Sender: TObject);
  private
    { Private 宣言 }
    FStrConnect : WideString;
    FUserID     : WideString;
    FPassword   : WideString;
    procedure DisplayToStringGrid(AGrid: TStringGrid; ARecordSet: TRecordset);
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//=============================================================================
//  フォーム生成時の処理
//=============================================================================
procedure TForm1.FormCreate(Sender: TObject);
var
  LDBFile : WideString;
begin
  //データベースのファイル名
  LDBFile   := ExtractFilePath(Application.ExeName) + 'ForExport.mdb';
  FUserID   := '';
  FPassword := '';

  //接続文字列を作成
  FStrConnect := 'Provider=Microsoft.Jet.OLEDB.4.0'
               + ';Data Source=' + LDBFile;
end;

//=============================================================================
//  フォームを閉じる時の処理
//=============================================================================
procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if Connection1.State = adStateOpen then begin
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
    Connection1.Close;
  end;
end;

//=============================================================================
//  読み出し専用クエリの実行例
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
var
  LStrSQL : OleVariant;
begin
  //データベース接続
  if Connection1.State = adStateClosed then begin
    Connection1.Open(FStrConnect, FUserID, FPassword, Integer(adConnectUnspecified));
  end else begin
    //レコードセットを閉じる
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
  end;

  //SQL文を指定してレコードセットを取得
  LStrSQL := 'SELECT 番号,食品群,食品名,キロカロリ FROM 食品カロリ表';
  Recordset1.Open(LStrSQL,
                  Connection1.DefaultInterface,
                  adOpenForwardOnly,
                  adLockReadOnly,
                  adCmdText);

  //取得したデータの表示
  DisplayToStringGrid(StringGrid1, RecordSet1);
end;

//=============================================================================
//  更新可能なクエリの実行例
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
var
  LStrSQL    : OleVariant;
  LTempValue : OleVariant;
begin
  //データベース接続
  if Connection1.State = adStateClosed then begin
    Connection1.Open(FStrConnect, FUserID, FPassword, Integer(adConnectUnspecified));
  end else begin
    //レコードセットを閉じる
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
  end;

  //SQL文を指定してレコードセットを取得
  //項目の[食品群]の先頭に'野菜'があるデータを取り出す
  LStrSQL := 'SELECT * FROM 食品カロリ表 WHERE 食品群 LIKE "野菜%"';
  Recordset1.Open(LStrSQL,
                  Connection1.DefaultInterface,
                  adOpenKeyset,
                  adLockOptimistic,
                  adCmdText);

  //項目のデータを変更してデータベースも更新
  if Recordset1.RecordCount > 0 then begin
    Recordset1.MoveFirst;
    while not Recordset1.EOF do begin
      LTempValue := Recordset1.Fields['食品群'].Value;
      if LTempValue = '野菜類等' then begin
        Recordset1.Fields['食品群'].Value := '野菜';
      end else begin
        Recordset1.Fields['食品群'].Value := '野菜類等';
      end;
      //TRecordset.MoveNextで自動的にUpdateするので実際には不要
      //Recordset1.Update;
      RecordSet1.MoveNext;
    end;
  end;

  //取得したデータの表示
  DisplayToStringGrid(StringGrid1, Recordset1);
end;

//=============================================================================
//  引数のARecordSetのデータをTStringGridに表示するメソッド
//=============================================================================
procedure TForm1.DisplayToStringGrid(AGrid: TStringGrid; ARecordSet: TRecordset);
var
  LDataArray : OleVariant;
  LColsLow   : Integer;
  LColsHigh  : Integer;
  LRowsLow   : Integer;
  LRowsHigh  : Integer;
  LVarValue  : OleVariant;
  iCol       : Integer;
  iRow       : Integer;
  i          : Integer;
  j          : Integer;
begin
  //配列の初期化
  LDataArray := Unassigned;

  //描画の更新を停止
  AGrid.Rows[0].BeginUpdate;

  //データを見やすくするために列幅の変更を可能にしておく
  AGrid.Options := AGrid.Options + [goColSizing];
  //TStringGridの初期化
  AGrid.FixedCols := 1;
  AGrid.FixedRows := 1;
  AGrid.ColCount  := AGrid.FixedCols + 1;
  AGrid.RowCount  := AGrid.FixedRows + 1;

  for i := 0 to AGrid.RowCount - 1 do begin
    AGrid.Rows[i].Clear;
  end;


  try
    if Recordset1.State = adStateClosed then exit;

    //データベースの項目名だけを取得してTStringGridの固定行に表示
    AGrid.ColCount := ARecordSet.Fields.Count + 1;
    for i := 0 to ARecordSet.Fields.Count - 1 do begin
      AGrid.Cells[i + AGrid.FixedCols, 0] := ARecordSet.Fields[i].Name;
    end;
    if ARecordSet.RecordCount = 0 then exit;

    //先頭のレコードに移動しておく
    Recordset1.MoveFirst;

    //クエリーの結果のデータを取得
    //adGetRowsRestは全てのデータ
    LDataArray := ARecordSet.GetRows(Integer(adGetRowsRest));

    //取得した2次元配列の要素番号の下限と上限値を取得
    LColsLow  := VarArrayLowBound(LDataArray, 1);
    LColsHigh := VarArrayHighBound(LDataArray, 1);
    LRowsLow  := VarArrayLowBound(LDataArray, 2);
    LRowsHigh := VarArrayHighBound(LDataArray, 2);

    //TStringGridの行数を設定
    AGrid.RowCount := (LRowsHigh - LRowsLow) + 1 + AGrid.FixedRows;

    //セルに値を代入していく
    for i := LRowsLow to LRowsHigh do begin
      for j := LColsLow to LColsHigh do begin
        iCol := j - LColsLow + AGrid.FixedCols;
        iRow := i - LRowsLow + AGrid.FixedRows;
        LVarValue := LDataArray[j, i];
        if VarType(LVarValue) = VarNull then begin
          AGrid.Cells[iCol, iRow] := '';
        end else begin
          AGrid.Cells[iCol, iRow] := VarToStr(LVarValue);
        end;
      end;
    end;
  finally
    //描画の更新を再開
    AGrid.Rows[0].EndUpdate;
  end;
end;

end.

//=============================================================================
//  フォーム生成時の処理
//=============================================================================
procedure TForm1.FormCreate(Sender: TObject);
var
  LDBFile : WideString;
begin
  //データベースのファイル名
  LDBFile   := ExtractFilePath(Application.ExeName) + 'ForExport.mdb';
  FUserID   := '';
  FPassword := '';

  //接続文字列を作成
  FStrConnect := 'Provider=Microsoft.Jet.OLEDB.4.0'
               + ';Data Source=' + LDBFile
               + ';User ID=' + FUserID
               + ';Password=' + FPassword;
end;

//=============================================================================
//  フォームを閉じる時の処理
//=============================================================================
procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if Recordset1.State = adStateOpen then begin
    Recordset1.Close;
  end;
end;

//=============================================================================
//  読み出し専用クエリの実行例
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
var
  LStrSQL : OleVariant;
begin
  //レコードセットを閉じる
  if Recordset1.State = adStateOpen then begin
    RecordSet1.Close;
  end;

  //SQL文を指定してレコードセットを取得
  LStrSQL := 'SELECT 番号,食品群,食品名,キロカロリ FROM 食品カロリ表';
  Recordset1.Open(LStrSQL, FStrConnect, adOpenForwardOnly, adLockReadOnly, adCmdText);

  //取得したデータの表示
  DisplayToStringGrid(StringGrid1, RecordSet1);
end;

//=============================================================================
//  フォーム生成時の処理
//=============================================================================
procedure TForm1.FormCreate(Sender: TObject);
var
  LDBFile : WideString;
begin
  //データベースのファイル名
  LDBFile   := ExtractFilePath(Application.ExeName) + 'ADOTestBook.xls';
  FUserID   := '';
  FPassword := '';

  //接続文字列を作成
  FStrConnect := 'Provider=Microsoft.Jet.OLEDB.4.0'
               + ';Data Source=' + LDBFile
               + ';Extended Properties="Excel 8.0"';
end;

//=============================================================================
//  フォームを閉じる時の処理
//=============================================================================
procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if Connection1.State = adStateOpen then begin
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
    Connection1.Close;
  end;
end;

//=============================================================================
//  更新可能なクエリの実行例
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
var
  LStrSQL : OleVariant;
  LText   : String;
begin
  //データベース接続
  if Connection1.State = adStateClosed then begin
    try
      Connection1.Open(FStrConnect, FUserID, FPassword, Integer(adConnectUnspecified));
    except
      LText := '他のユーザが編集中です' + sLineBreak + '開くことは出来ません';
      MessageBox(Handle, PChar(LText), '情報', MB_ICONINFORMATION);
      exit;
    end;
  end else begin
    //レコードセットを閉じる
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
  end;


  //SQL文を指定してレコードセットを取得
  //項目の[食品群]の先頭に'野菜'があるデータを取り出す
  //'SELECT * FROM [Sheet2$]'とすれば全てのデータの取得
  LStrSQL := 'SELECT * FROM [Sheet2$] WHERE 食品群 LIKE "野菜%" ORDER BY キロカロリ';
  Recordset1.Open(LStrSQL,
                  Connection1.DefaultInterface,
                  adOpenKeyset,
                  adLockOptimistic,
                  adCmdText);

  //取得したデータの表示
  DisplayToStringGrid(StringGrid1, RecordSet1);
end;

  if Connection1.State = adStateClosed then begin
    Connection1.Mode := adModeRead;
    Connection1.Open(FStrConnect, FUserID, FPassword, Integer(adConnectUnspecified));
  end else begin
    //レコードセットを閉じる
    if Recordset1.State = adStateOpen then begin
      RecordSet1.Close;
    end;
  end;

//=============================================================================
//  指定レコード位置に相当するTStringGridの行番号を取得する
//  TRecordset.AbsolutePositionプロパティによるレコード位置の移動
//  TRecordset.AbsolutePositionの最小値は1
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
begin
  if Recordset1.State = adStateClosed then exit;
  if Recordset1.RecordCount = 0 then exit;

  //テスト用にカレントレコードを移動
  Recordset1.AbsolutePosition := 5;

  StringGrid1.Row := Recordset1.AbsolutePosition + StringGrid1.FixedRows - 1;
end;

//=============================================================================
//  TStringGridの行番号に相当する位置にカレントレコードを変更する
//=============================================================================
procedure TForm1.Button3Click(Sender: TObject);
var
  LRecPos : Integer;
begin
  if Recordset1.State = adStateClosed then exit;
  if Recordset1.RecordCount = 0 then exit;

  LRecPos := StringGrid1.Row + StringGrid1.FixedRows - 1;
  if Recordset1.RecordCount >= LRecPos then begin
    Recordset1.AbsolutePosition := LRecPos;
    //確認用の表示
    //項目番号でなく項目名でFields['食品名']でも可
    ShowMessage(Recordset1.Fields[2].Value);
  end;
end;