将数据导出至 M$ Access

Dev Express 中的 dxDBGrid/cxGrid 均提供了将表格中 数据 导出 到 M$ Excel 等中的方法，但大多时候，却需将 数据 导出 至 M$ Access 中... 于是便有了本文。 uses ComObj, Gauges, ShellAPI; const ExportTabName_MDB = '营销 数据 '; MDBStr = 'Provider=

Dev Express 中的 dxDBGrid/cxGrid 均提供了将表格中数据导出到 M$ Excel 等中的方法，但大多时候，却需将数据导出至 M$ Access 中...
    于是便有了本文。

    uses
      ComObj, Gauges, ShellAPI;

    const
      ExportTabName_MDB = '营销数据';
      MDBStr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s';

    var
      ExportName: string;
      ExportColumnLst: TStringList; //列名;列类型(长度)
    begin
      ExportName:= '导出结果.MDB'; //use a SaveDialog to select the save name here
      ExportColumnLst:= TStringList.Create;

      //（示例）导出列列表，注意 格式
      ExportColumnLst.Add('Contact;联系人 varchar(30)');
      ExportColumnLst.Add('Gender;性别 varchar(2)');
      ExportColumnLst.Add('Address;地址 varchar(100)');
      ExportColumnLst.Add('PostCode;邮编 varchar(6)');

      try
        ExportToMDB(ExportName, ExportColumnLst);
      finally
        FreeAndNil(ExportColumnLst);
      end;
    end;

    procedure ExportToMDB(ExportMDBName: string; ExportColumnLst);
      function CreateMDB(MDBFileName: string): Boolean;
      var
        vMDB: Variant;
      begin
        Result:= False;

        vMDB:= CreateOleObject('ADOX.Catalog');
        vMDB.Create(Format(MDBStr, [MDBFileName]));
        vMDB:= UnAssigned;

        Result:= True;
      end;

      function CreateTab(MDBAndTabName: string; ExportColumnLst: TStringList;
        aqy_ExecSQL: TADOQuery): Boolean;
      var
        i: Integer;
        StrTmp: string;
        SQLTxt: string;
        MDBName: string;
        TabName: string;
      begin
        Result:= False;

        SQLTxt:= '';
        for i:= 0 to ExportColumnLst.Count - 1 do
        begin
          StrTmp:= ExportColumnLst.Strings;

          if SQLTxt = '' then
            SQLTxt:= Copy(StrTmp, Pos(';', StrTmp) + 1, Length(StrTmp));
          else
            SQLTxt:= SQLTxt + ',' +
                       Copy(StrTmp, Pos(';', StrTmp) + 1, Length(StrTmp));
        end;

        MDBName:= Copy(MDBAndTabName, 1, Pos(';', MDBAndTabName) - 1);
        TabName:= Copy(
                       MDBAndTabName,
                       Pos(';', MDBAndTabName) + 1,
                       Length(MDBAndTabName)
                      );

        with aqy_ExecSQL do
        try
          Close;

          ConnectionString:=
            'Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0;' +
            'Data Source=' + MDBName + ';Persist Security Info=false';

          SQL.Text:=
            'create table ' + TabName +
            '(' +
              SQLTxt +
            ')';

          try
            ExecSQL;
            Close;
          except
            on E: Exception do
            begin
              MessageBox(
                         Handle,
                         PChar('创建表失败！' + #13 + '失败原因：' + E.Message),
                         '错误',
                         MB_OK + MB_ICONERROR
                        );
              Close;
              Exit;
            end;  
          end;          
        finally
          //Free;  
        end;

        Result:= True;
      end;
    var
      aqy_ExecSQL: TADOQuery;
      SQLTxt: string;
      i: Integer;
      StrTmp: string;
      ExportColumn: string;
      ExportColumnParam: string;
      ExportParamLst: TStringList;
      GgTip: TGauge;
      CurrRec: Integer;
    begin
      if CreateMDB(ExportMDBName) then
      begin
        aqy_ExecSQL:= TADOQuery.Create(Self);
        try
          if CreateTab(
                       ExportMDBName + ';' + ExportTabName_MDB,
                       ExportColumnLst,
                       aqy_ExecSQL
                      ) then
          begin
            Screen.Cursor:= crHourGlass;

            ExportColumn:= '';
            ExportColumnParam:= '';
            ExportParamLst:= TStringList.Create;
            for i:= 0 to ExportColumnLst.Count - 1 do
            begin
              StrTmp:= ExportColumnLst.Strings;

              if ExportColumn = '' then
              begin
                ExportColumn:= Copy(StrTmp, 1, Pos(';', StrTmp) - 1);
                ExportColumnParam:= ':' + ExportColumn;
                ExportParamLst.Add(ExportColumn);
              end
              else
              begin
                ExportColumn:= ExportColumn + ',' +
                                 Copy(StrTmp, 1, Pos(';', StrTmp) - 1);
                ExportColumnParam:= ExportColumnParam + ',:' +
                                      Copy(StrTmp, 1, Pos(';', StrTmp) - 1);
                ExportParamLst.Add(Copy(StrTmp, 1, Pos(';', StrTmp) - 1));
              end;
            end;

            SQLTxt:=
              'select ' + ExportColumn + ' from TabName where ID=' +
              aqy_Tmp1.FieldByName('ID').AsString;  

            try
              with aqy_ExportData do //aqy_ExportData: TADOQuery;
              begin
                Close;
                SQL.Text:= SQLTxt;
                Open;

                //pnl_ExportFile: TPanel;
                GgTip:= TGauge.Create(pnl_ExportFile); //Gauge 进度提示
                with GgTip do
                begin
                  Parent:= pnl_ExportFile;
                  Left:= 0;
                  Height:= 21;
                  Width:= pnl_ExportFile.Width;
                  ForeColor:= clFuchsia;
                  MinValue:= 0;
                  MaxValue:= RecordCount;
                  Visible:= True;
                  Update;
                end;

                CurrRec:= 0;
                while not Eof do
                begin
                  Inc(CurrRec);

                  if CurrRec mod 20 = 0 then
                  begin
                    GgTip.Progress:= CurrRec;
                    Update;

                    Application.ProcessMessages;
                  end;

                  with aqy_ExecSQL do
                  begin
                    Close;

                    SQL.Text:=
                      'Insert Into ' + ExportTabName_MDB +
                      ' Values(' + ExportColumnParam + ')';

                    for i:= 0 to ExportParamLst.Count - 1 do
                      Parameters.ParamByName(ExportParamLst.Strings).Value:=
                       aqy_ExportData.FieldByName(
                                                  ExportParamLst.Strings
                                                 ).AsString;

                    try
                      ExecSQL;                  
                    except
                      on E: Exception do
                      begin
                        Close;
                        GgTip.Visible:= False;
                        Update;

                        MessageBox(
                                   Handle,
                                   PChar('导出文件失败！ ' + #13 + '失败原因：' +
                                         E.Message + ' '
                                        ),
                                   '错误',
                                   MB_OK + MB_ICONERROR
                                  );
                        Exit;
                      end;
                    end;
                  end; //End with

                  aqy_ExecSQL.Close;

                  Next;
                end; //End while

                Close; //aqy_ExportData
                GgTip.Visible:= False;

                if MessageBox(
                              Handle,
                              PChar('导出文件成功！ ' + #13 +
                                    '现在查看导出结果（' + ExportMDBName + '吗？'
                                   ),
                              '提示',
                               MB_YESNO + MB_ICONINFORMATION
                             ) = IDYES then
                begin
                  ShellExecute(0, 'Open', PChar(ExportMDBName), nil, nil, SW_SHOW);
                end;
              end;
            except
              on E: Exception do
              begin
                pnl_ExportFile.Caption:= '';
                GgTip.Visible:= False;
                Update;

                MessageBox(
                           Handle,
                           PChar('导出文件过程中发生错误！ ' + #13 +
                                 '错误描述：' + E.Message + ' '
                                ),
                           '导出失败',
                           MB_OK + MB_ICONERROR
                          );
              end;
            end;
          end;
        finally
          FreeAndNil(aqy_ExecSQL);
          FreeAndNil(ExportParamLst);
          FreeAndNil(GgTip);

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

    OK，Done！

ADelphiCoder